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

34fw

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;