Oracle，存储过程，不直接使用insert，而使用游标的原因

在写存储过程的时候，有时不直接使用insert into 表名 values select * from XXX where XXX

而是使用游标进行数据的一条条插入，主要原因是数据库资源的问题。

 

使用insert直接插入整个查询的表的数据，数据库会等到都执行完成之后才能释放资源，若查询的过程比较慢，那么资源就一直处于使用状态。比较费资源。

 

若使用游标进行插入的话，可以设置游标多少条数据进行插入一次，然后释放一下资源。

 

 

 

 

 

游标的形式：

create or replace procedure PRC_RPN_REVISIT_REPORT is

cursor cur_rpn_revisit_report is
select A.*,
case when A.STATUS is not null and A.STATUS = '3'
         then '回访不成功'
         when A.STATUS is null and CONCAT(A.SERVICE_RESULT,' ') <> CONCAT( '不回访通过',' ')
           then '回访成功'
         when A.STATUS is null and A.SERVICE_RESULT ='不回访通过'
           then '不回访通过'
       end RINGBACK_STATUS--回访处理状态
 from (
SELECT  S.ROW_ID,
 S.BRANCH_ID,
       S.CUSTOMER_NAME,
       S.MID_PRODUCT_ID,
       S.MODEL_NAME,
     
       P.PROD_SERIAL_NO,
       F.SERVICE_TYPE_ID,
       F.SERVICE_MODE_ID,
       F.AUTO_AUDIT_RESULT,
       F.IF_WARRANT,
       S.AREA_CODE1,
       F.AUDIT_BILL,
      C.RINGBACK_TIME,
       S.TELEPHONE2,
       S.TELEPHONE1,
       S.TELEPHONE3,
       case when S.AREA_CODE1 is not null and S.TELEPHONE1 is not null
         then
           '家庭电话:' || S.AREA_CODE1 || '-' || S.TELEPHONE1
         end HOMEPHONE,
        case when S.TELEPHONE2 is not null
          then
              '移动电话:' || S.TELEPHONE2
        end MOBILEPHONE,
        case when S.TELEPHONE3 is not null
          then
             '其他电话:' || S.TELEPHONE3
          end OFFICEPHONE,
     
       C.DISSATIS_CODE,
       C.RINGBACK_DESC,
       F.WITHDRAW_REASON,
       E.PRICE_DIFFERENCE,
       F.SERVICE_FINISH_TIME,
       C.SERVICE_RESULT,
       S.REQUIRE_SERVICE_DATE,
       C.VISIT_TIMES,
       C.TARGET_CALLCENTER_ID,
       C.RINGBACKER,
       C.CALLCENTER_ID,
       C.RINGBACK_RESULT,
       C.STATUS,   
       F.SERVICE_CORP_NAME,
       P.BUY_DATE,
       I.REMARK,
       A.APPEAL_DESC,
       F.SERVICE_CORP_ID,
       C.SATIS_ID,
       C.SATIS_DESC,
       C.SATIS_DEDUCT,
       F.RETURN_TIMES,
       S.BELONG_CENTER,
       F.FIRST_BRANCH_ID,
       S.product_id,
       ( select t.codename from v_codelist t where t.kindvalue='RINGBACKER' and t.codevalue =C.RINGBACKER) RINGBACKER_NAME ,
       ( select t.codename from v_codelist t where t.kindvalue='PROD_MID' and t.codevalue =S.MID_PRODUCT_ID) MID_PRODUCT_NAME ,
       ( select t.codename from v_codelist t where t.kindvalue='RINGBACK_RESULT' and t.codevalue =C.RINGBACK_RESULT) RINGBACK_RESULT_NAME ,
       ( select t.codename from v_codelist t where t.kindvalue='ORG_DEPT_DV' and t.codevalue =S.BRANCH_ID) BRANCH_NAME ,
       ( select t.codename from v_codelist t where t.kindvalue='SERVICE_TYPE' and t.codevalue =F.SERVICE_TYPE_ID) SERVICE_TYPE_NAME ,
       ( select distinct t.codename from v_codelist t where t.kindvalue='SERVICE_MODE' and t.codevalue =F.SERVICE_MODE_ID) SERVICE_MODE_NAME ,
       ( select distinct t.codename from v_codelist t where t.kindvalue='DISSATIS_CODE' and t.codevalue =C.DISSATIS_CODE) DISSATIS_CODE_NAME ,
       ( select distinct t.codename from v_codelist t where t.kindvalue='IF_WARRANT' and t.codevalue =F.IF_WARRANT) IF_WARRANT_NAME ,
       ( select distinct t.codename from v_codelist t where t.kindvalue='SATISFACTION' and t.codevalue =C.SATIS_ID) SATIS_NAME ,
       ( select distinct t.codename from v_codelist t where t.kindvalue='CALL_CENTER' and t.codevalue =C.TARGET_CALLCENTER_ID) TARGET_CALLCENTER_NAME ,
       ( select distinct t.codename from v_codelist t where t.kindvalue='CALL_CENTER' and t.codevalue =C.CALLCENTER_ID) CALLCENTER_NAME
  FROM WB_SERVICE_INFO          S,
       WB_CALL_ON_TASK          C,
       WB_SERVICE_FEEDBACK      F,
       WB_CUSTOMER_PRODUCT      P,
       EM_RETURN_REQ_BILL       E,
       WB_SERVICE_FEEDBACK_ITEM I,
       WB_SERVICE_CORP_APPEAL   A
 WHERE 1 = 1
   AND C.ACTIVE_FLAG = '1'
   AND C.WB_ID = F.ROW_ID
   AND S.ROW_ID = F.ROW_ID
   AND S.ROW_ID = A.WB_ID(+)
   AND P.ROW_ID = S.CUSTOMER_PROD_ID
   AND S.ROW_ID = E.RETURN_WB_NO(+)
   AND (C.STATUS = '3' OR C.STATUS IS NULL)
   AND F.ROW_ID = I.WB_ID(+)
   AND I.PRIMARY_FLAG(+) = '1'
   AND F.WB_STATUS != '20'
  
   and C.RINGBACK_TIME >= trunc(sysdate-90)
   and C.RINGBACK_TIME < trunc(sysdate)
 
 order by a.row_id asc
) A;

   lv_rpn_revisit_report RPN_REVISIT_REPORT%rowtype;

begin

delete from RPN_REVISIT_REPORT t where t.RINGBACK_TIME < add_months(sysdate,-3);
--commit;

for  sendRec in cur_rpn_revisit_report loop

 begin
       lv_rpn_revisit_report.ROW_ID := sendRec.ROW_ID;
       lv_rpn_revisit_report.BRANCH_ID := sendRec.BRANCH_ID;
       lv_rpn_revisit_report.CUSTOMER_NAME := sendRec.CUSTOMER_NAME;
       lv_rpn_revisit_report.MID_PRODUCT_ID := sendRec.MID_PRODUCT_ID;
       lv_rpn_revisit_report.MODEL_NAME := sendRec.MODEL_NAME;
       lv_rpn_revisit_report.PROD_SERIAL_NO := sendRec.PROD_SERIAL_NO;
       lv_rpn_revisit_report.SERVICE_TYPE_ID := sendRec.SERVICE_TYPE_ID;
       lv_rpn_revisit_report.SERVICE_MODE_ID := sendRec.SERVICE_MODE_ID;
       lv_rpn_revisit_report.AUTO_AUDIT_RESULT := sendRec.AUTO_AUDIT_RESULT;
       lv_rpn_revisit_report.IF_WARRANT := sendRec.IF_WARRANT;
       lv_rpn_revisit_report.AREA_CODE1 := sendRec.AREA_CODE1;
       lv_rpn_revisit_report.AUDIT_BILL := sendRec.AUDIT_BILL;
       lv_rpn_revisit_report.RINGBACK_TIME := sendRec.RINGBACK_TIME;
       lv_rpn_revisit_report.TELEPHONE2 := sendRec.TELEPHONE2;
       lv_rpn_revisit_report.TELEPHONE1 := sendRec.TELEPHONE1;
       lv_rpn_revisit_report.TELEPHONE3 := sendRec.TELEPHONE3;
       lv_rpn_revisit_report.HOMEPHONE := sendRec.HOMEPHONE;
       lv_rpn_revisit_report.MOBILEPHONE := sendRec.MOBILEPHONE;
       lv_rpn_revisit_report.OFFICEPHONE := sendRec.OFFICEPHONE;
       lv_rpn_revisit_report.DISSATIS_CODE := sendRec.DISSATIS_CODE;
       lv_rpn_revisit_report.RINGBACK_DESC := sendRec.RINGBACK_DESC;
       lv_rpn_revisit_report.WITHDRAW_REASON := sendRec.WITHDRAW_REASON;
       lv_rpn_revisit_report.PRICE_DIFFERENCE := sendRec.PRICE_DIFFERENCE;
       lv_rpn_revisit_report.SERVICE_FINISH_TIME := sendRec.SERVICE_FINISH_TIME;
       lv_rpn_revisit_report.SERVICE_RESULT := sendRec.SERVICE_RESULT;
       lv_rpn_revisit_report.REQUIRE_SERVICE_DATE := sendRec.REQUIRE_SERVICE_DATE;
       lv_rpn_revisit_report.VISIT_TIMES := sendRec.VISIT_TIMES;
       lv_rpn_revisit_report.TARGET_CALLCENTER_ID := sendRec.TARGET_CALLCENTER_ID;
       lv_rpn_revisit_report.RINGBACKER := sendRec.RINGBACKER;
       lv_rpn_revisit_report.CALLCENTER_ID := sendRec.CALLCENTER_ID;
       lv_rpn_revisit_report.RINGBACK_RESULT := sendRec.RINGBACK_RESULT;
       lv_rpn_revisit_report.STATUS := sendRec.STATUS;
       lv_rpn_revisit_report.RINGBACK_STATUS := sendRec.RINGBACK_STATUS;
       lv_rpn_revisit_report.SERVICE_CORP_NAME := sendRec.SERVICE_CORP_NAME;
       lv_rpn_revisit_report.BUY_DATE := sendRec.BUY_DATE;
       lv_rpn_revisit_report.REMARK := sendRec.REMARK;
      
       lv_rpn_revisit_report.APPEAL_DESC := sendRec.APPEAL_DESC;
       lv_rpn_revisit_report.SERVICE_CORP_ID := sendRec.SERVICE_CORP_ID;
       lv_rpn_revisit_report.SATIS_ID := sendRec.SATIS_ID;
       lv_rpn_revisit_report.SATIS_DESC := sendRec.SATIS_DESC;
       lv_rpn_revisit_report.SATIS_DEDUCT := sendRec.SATIS_DEDUCT;
       lv_rpn_revisit_report.RETURN_TIMES := sendRec.RETURN_TIMES;
       lv_rpn_revisit_report.BELONG_CENTER := sendRec.BELONG_CENTER;
       lv_rpn_revisit_report.FIRST_BRANCH_ID := sendRec.FIRST_BRANCH_ID;
       lv_rpn_revisit_report.product_id := sendRec.product_id;
       lv_rpn_revisit_report.RINGBACKER_NAME := sendRec.RINGBACKER_NAME;
       lv_rpn_revisit_report.RINGBACK_RESULT_NAME := sendRec.RINGBACK_RESULT_NAME;
       lv_rpn_revisit_report.BRANCH_NAME := sendRec.BRANCH_NAME;
       lv_rpn_revisit_report.SERVICE_TYPE_NAME := sendRec.SERVICE_TYPE_NAME;
       lv_rpn_revisit_report.SERVICE_MODE_NAME := sendRec.SERVICE_MODE_NAME;
       lv_rpn_revisit_report.DISSATIS_CODE_NAME := sendRec.DISSATIS_CODE_NAME;
       lv_rpn_revisit_report.IF_WARRANT_NAME := sendRec.IF_WARRANT_NAME;
       lv_rpn_revisit_report.SATIS_NAME := sendRec.SATIS_NAME;
       lv_rpn_revisit_report.TARGET_CALLCENTER_NAME := sendRec.TARGET_CALLCENTER_NAME;
       lv_rpn_revisit_report.CALLCENTER_NAME := sendRec.CALLCENTER_NAME;
 
       exception  when others then
             null;
        end;

        insert into RPN_REVISIT_REPORT values lv_rpn_revisit_report;

end loop;

commit;

EXCEPTION   WHEN OTHERS THEN

   rollback;

 
end PRC_RPN_REVISIT_REPORT;

 

 

直接插入的形式：

create or replace procedure PRC_RPN_VISITER_COUNT is

begin

 DELETE FROM  RPN_VISITER_COUNT_CALLCENTER T where T.RINGBACK_TIME <add_months(sysdate,-3);
-- commit;
   
 INSERT INTO RPN_VISITER_COUNT_CALLCENTER
 (ROW_ID,
   PRODUCT_ID,
   RINGBACK_TIME,
   SERVICE_RESULT,
   PROJECT_CODE,
   STATUS,
   PSN_NAME,
   org_id)
SELECT f_get_report_rowid('rpn_visiter_count_callcenter'),
 t1.product_id,
           T1.RINGBACK_TIME,
           T1.SERVICE_RESULT,
           T1.PROJECT_CODE,
           T1.STATUS,
           T2.PSN_NAME,
           t2.org_id
      FROM WB_CALL_ON_TASK T1, V_PERSON_DEPT_MAP T2
     WHERE T1.RINGBACKER = T2.PSN_ACCOUNT(+)
     AND T1.RINGBACK_TIME >= trunc(sysdate-1)
     AND T1.RINGBACK_TIME < trunc(sysdate);
 
 commit;

EXCEPTION   WHEN OTHERS THEN

   rollback;
    
 
end PRC_RPN_VISITER_COUNT;