oracle中创建存储过程，以及存储过程中调用游标
CREATE OR REPLACE PROCEDURE aa
成都创新互联公司长期为上1000家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为衡山企业提供专业的成都网站建设、网站设计，衡山网站改版等技术服务。拥有10年丰富建站经验和众多成功案例,为您定制开发。
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;
当前题目：oracle中创建存储过程，以及存储过程中调用游标
文章起源：http://scyanting.com/article/jcjcis.html
