oracle中存储过程的调用存储过程,oracle中创建存储过程，以及存储过程中调用游标...

oracle中创建存储过程，以及存储过程中调用游标

发布时间：2020-08-06 23:27:14

来源：51CTO

阅读：630

作者：lxg290

CREATE OR REPLACE PROCEDURE aa

is

CURSOR prodCombind_cur IS SELECT * FROM proddata.product_combind_info pci;

prodCombindInfo prodCombind_cur%ROWTYPE;

id_marketproduct_info varchar2(32);

CURSOR combindPackage_cur IS SELECT * FROM proddata.combind_package_info;

combindPackageInfo combindPackage_cur%ROWTYPE;

id_package_info varchar2(32);

tmpid_marketproduct_info varchar2(32);

--查询包含有要删除的子产品的产品组合

CURSOR delete_combind_cur(id_sub_product VARCHAR2) IS

select * from proddata.product_combind_rel pcr where pcr.id_product=id_sub_product;

delCombindInfo delete_combind_cur%ROWTYPE;

begin

begin

--marketproduct_info表中已经存在的a57,和a72先删除

select m.id_marketproduct_info into tmpid_marketproduct_info from  proddata.marketproduct_info m where m.marketproduct_code='MP02000057' and m.is_combined='1';

--删除市场产品对应的产品组合

OPEN delete_combind_cur(tmpid_marketproduct_info); --打开游标;

loop

FETCH delete_combind_cur INTO delCombindInfo; --从游标中取值要删除的产品组合的id

--首先删除套餐下所有的子产品

delete from proddata.combind_package_rel cpr where cpr.id_combind_package_info in (

select cpi.id_combind_package_info from proddata.combind_package_info cpi

where cpi.id_product_combind_info=delCombindInfo.id_product_combind_info);

--在删除套餐

delete from proddata.combind_package_info cpi

where cpi.id_product_combind_info=delCombindInfo.id_product_combind_info;

--删除产品组合下的子产品

delete from proddata.product_combind_rel pcr where pcr.id_product_combind_info=delCombindInfo.id_product_combind_info;

delete from proddata.marketproduct_rule_param mrp where mrp.id_marketproduct_info= delCombindInfo.id_product_combind_info;

delete from proddata.marketproduct_department md where md.id_marketproduct_info=delCombindInfo.id_product_combind_info;

delete from proddata.product_combind_info pci where pci.id_product_combind_info=delCombindInfo.id_product_combind_info;

exit when delete_combind_cur%notfound;

end loop;

close delete_combind_cur; --关闭游标

delete from proddata.marketproduct_rule_param mr where mr.id_marketproduct_info= tmpid_marketproduct_info;

delete from proddata.marketproduct_department md where md.id_marketproduct_info=tmpid_marketproduct_info;

delete from proddata.marketproduct_plan_duty_rel mpdr where mpdr.id_marketproduct_info=tmpid_marketproduct_info;

delete from proddata.marketproduct_info m where m.id_marketproduct_info=tmpid_marketproduct_info;

--

select m.id_marketproduct_info into tmpid_marketproduct_info from  proddata.marketproduct_info m  where m.marketproduct_code='MP02000072' and m.is_combined='1';

--删除市场产品对应的产品组合

OPEN delete_combind_cur(tmpid_marketproduct_info); --打开游标;

loop

FETCH delete_combind_cur INTO delCombindInfo; --从游标中取值

--首先删除套餐下所有的子产品

delete from proddata.combind_package_rel cpr where cpr.id_combind_package_info in (

select cpi.id_combind_package_info from proddata.combind_package_info cpi

where cpi.id_product_combind_info=delCombindInfo.id_product_combind_info);

--在删除套餐

delete from proddata.combind_package_info cpi

where cpi.id_product_combind_info=delCombindInfo.id_product_combind_info;

--删除产品组合下的子产品

delete from proddata.product_combind_rel pcr where pcr.id_product_combind_info=delCombindInfo.id_product_combind_info;

delete from proddata.marketproduct_rule_param mrp where mrp.id_marketproduct_info= delCombindInfo.id_product_combind_info;

delete from proddata.marketproduct_department md where md.id_marketproduct_info=delCombindInfo.id_product_combind_info;

delete from proddata.product_combind_info pci where pci.id_product_combind_info=delCombindInfo.id_product_combind_info;

exit when delete_combind_cur%notfound;

end loop;

close delete_combind_cur; --关闭游标

delete from proddata.marketproduct_rule_param mr where mr.id_marketproduct_info= tmpid_marketproduct_info;

delete from proddata.marketproduct_department md where md.id_marketproduct_info=tmpid_marketproduct_info;

delete from proddata.marketproduct_plan_duty_rel mpdr where mpdr.id_marketproduct_info=tmpid_marketproduct_info;

delete from proddata.marketproduct_info m where m.id_marketproduct_info=tmpid_marketproduct_info;

--

commit;

exception

when others then

dbms_output.put_line('删除a57和a72产品时，出现异常');

end;

--需要处理的表：将表product_combind_info中的数据，copy到表marketproduct_info中；

for prodCombindInfo in prodCombind_cur loop

begin

select m.id_marketproduct_info into id_marketproduct_info from proddata.marketproduct_info m

where m.id_marketproduct_info = prodCombindInfo.id_product_combind_info;

dbms_output.put_line('product已经存在的：'|| prodCombindInfo.marketproduct_code ||'-'|| prodCombindInfo.MARKETPRODUCT_NAME);

exception

--这里抛出异常，是表示上面的select没有查询到记录引起的

when others then

dbms_output.put_line('product不存在的：'|| prodCombindInfo.marketproduct_code||'-'||prodCombindInfo.marketproduct_name);

insert into proddata.marketproduct_info

(CREATED_BY,

CREATED_DATE,

UPDATED_BY,

UPDATED_DATE,

marketproduct_code,

marketproduct_name,

product_class,

status,

is_combined,

id_marketproduct_info,

department_code,

version,

policy_type)

values

(prodCombindInfo.CREATED_BY,

prodCombindInfo.CREATED_DATE,

prodCombindInfo.UPDATED_BY,

prodCombindInfo.UPDATED_DATE,

prodCombindInfo.marketproduct_code,

prodCombindInfo.marketproduct_name,

prodCombindInfo.MAIN_PRODUCT_CLASS,

prodCombindInfo.status,

'1',

prodCombindInfo.ID_PRODUCT_COMBIND_INFO,

prodCombindInfo.DEPARTMENT_CODE,

prodCombindInfo.version,

prodCombindInfo.POLICY_TYPE);

end;

end loop;

commit;

--需要处理的表:将表combind_package_info中的数据，copy到表package_info中

for combindPackageInfo in combindPackage_cur loop

begin

select p.id_package_info into id_package_info from  proddata.package_info p where p.id_package_info=combindPackageInfo.ID_COMBIND_PACKAGE_INFO;

dbms_output.put_line('package已经存在的：'|| combindPackageInfo.PACKAGE_CODE||'-'||combindPackageInfo.PACKAGE_NAME);

exception

--这里抛出异常，是表示上面的select没有查询到记录引起的

when others then

insert into  proddata.package_info

(ID_PACKAGE_INFO,

ID_MARKETPRODUCT_INFO,

PACKAGE_CODE,

PACKAGE_NAME,

CREATED_BY,

CREATED_DATE,

UPDATED_BY,

UPDATED_DATE,

status)

values

(combindPackageInfo.ID_COMBIND_PACKAGE_INFO,

combindPackageInfo.ID_PRODUCT_COMBIND_INFO,

combindPackageInfo.PACKAGE_CODE,

combindPackageInfo.PACKAGE_NAME,

combindPackageInfo.CREATED_BY,

combindPackageInfo.CREATED_DATE,

combindPackageInfo.UPDATED_BY,

combindPackageInfo.UPDATED_DATE,

'1');

dbms_output.put_line('package不存在的：'|| combindPackageInfo.PACKAGE_CODE||'-'||combindPackageInfo.PACKAGE_NAME);

end;

end loop;

commit;

end;