Oracle 存储过程与函数

CREATE OR REPLACE PROCEDURE pro_scmcbs_i6bill_filldata(group_code    org_group.code%type,
                                                       stockorg_code org_stockorg.code%type) AS
  cursor cur is
    select pk_cbsnode, pk_projectclass, pk_costprojectid
      from temp_scmcbs_mapping;
BEGIN
  for v_temp in cur loop
    dbms_output.put_line(v_temp.pk_cbsnode);
    update ia_i6bill_b
       set ccbsnodeid = v_temp.pk_cbsnode
     where dr = 0
       and vbdef1 = v_temp.pk_costprojectid
       and cprojectid in
           (select pk_project
              from bd_project
             where dr = 0
               and pk_projectclass = v_temp.pk_projectclass)
       and cbillid in
           (select cbillid
              from ia_i6bill ia_i6bill
              join org_group org_group
                on ia_i6bill.pk_group = org_group.pk_group
              join org_stockorg org_stockorg
                on org_stockorg.pk_stockorg = ia_i6bill.cstockorgid
             where ia_i6bill.dr = 0
               and org_group.code = group_code
               and org_stockorg.code = stockorg_code);
  end loop;
  commit;
END;
------------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE pro_scmcbs_mapping_fill_new AS

  cursor cur_costproject is
    SELECT code, name, PK_DEFDOC
      FROM bd_defdoc
     WHERE PK_DEFDOCLIST = '1002A310000000004QPT';

  cursor cur_projectclass is
    select pk_projectclass, type_code, type_name
      from bd_projectclass
     where type_code in ('01', '02', '03', '04', '05', '90');

  cursor cur_cbs is
    select pk_cbsnode, code, name from bd_cbsnode;

  v_cbs_code   temp_scmcbs_mapping.cbs_code%type;
  v_pk_cbsnode temp_scmcbs_mapping.pk_cbsnode%type;
  v_name       temp_scmcbs_mapping.cbs_name%type;

BEGIN

  for cur in cur_costproject loop
    for cur_ in cur_projectclass loop
      v_cbs_code := f_compute_contrast(cur.code, cur_.type_code);
      insert into temp_scmcbs_mapping
        (pk_costprojectid,
         costproject_code,
         costproject_name,
         pk_projectclass,
         projectclass_code,
         projectclass_name,
         pk_cbsnode,
         cbs_code,
         cbs_name)
      values
        (cur.pk_defdoc,
         cur.code,
         cur.name,
         cur_.pk_projectclass,
         cur_.type_code,
         cur_.type_name,
         v_pk_cbsnode,
         v_cbs_code,
         v_name);
    end loop;
  end loop;
  commit;
  for cur in cur_cbs loop
    update temp_scmcbs_mapping
       set pk_cbsnode = cur.pk_cbsnode, cbs_name = cur.name
     where cbs_code = cur.code;
  end loop;
  commit;
exception
  WHEN NO_DATA_FOUND THEN
    raise_application_error(-20009, 'NO_DATA_FOUND');
END;

[img]http://dl.iteye.com/upload/picture/pic/123859/1a57558a-3749-3bba-ac9d-0f74ce20c4fe.jpg[/img]

[img]http://dl.iteye.com/upload/picture/pic/123861/024e3f1f-ad25-38b0-9441-b70437b7038d.jpg[/img]

[img]http://dl.iteye.com/upload/picture/pic/123863/d36a2ed7-93f2-3c98-a51f-13eec57ff69e.jpg[/img]

[img]http://dl.iteye.com/upload/picture/pic/123865/4e41e692-0a09-3cb8-8f6c-34ecadd8374c.jpg[/img]