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

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;