您好,登錄后才能下訂單哦!
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);
--查詢包含有要刪除的子產(chǎn)品的產(chǎn)品組合
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表中已經(jīng)存在的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';
--刪除市場產(chǎn)品對應的產(chǎn)品組合
OPEN delete_combind_cur(tmpid_marketproduct_info); --打開游標;
loop
FETCH delete_combind_cur INTO delCombindInfo; --從游標中取值要刪除的產(chǎn)品組合的id
--首先刪除套餐下所有的子產(chǎn)品
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;
--刪除產(chǎn)品組合下的子產(chǎn)品
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';
--刪除市場產(chǎn)品對應的產(chǎn)品組合
OPEN delete_combind_cur(tmpid_marketproduct_info); --打開游標;
loop
FETCH delete_combind_cur INTO delCombindInfo; --從游標中取值
--首先刪除套餐下所有的子產(chǎn)品
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;
--刪除產(chǎn)品組合下的子產(chǎn)品
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產(chǎn)品時,出現(xiàn)異常');
end;
--需要處理的表:將表product_combind_info中的數(shù)據(jù),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已經(jīng)存在的:'|| 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中的數(shù)據(jù),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已經(jīng)存在的:'|| 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;
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權內(nèi)容。