Oracle 存储过程与函数

d5rr1d52cg

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;