Oracle游标的性能优化

Question

请问这个该怎么实现性能优化？

Answer 1

如果可以，改成关联批量update的方式，不要游标，游标是逐条处理，相比批量update效率低些

Answer 2

实在不行，把游标取得的数据塞到一个临时表里， 下面的update语句关联临时表

Answer 3

怎么去弄  求SQL

Answer 4

insert into tmp_table select * 
然后
update DATA_POLICYEN
set col= (select b.col from tmp_table b
就这个意思啊，建一张表tmp_table，把需要的临时数据集放进去，update DATA_POLICYEN的时候关联这张表，就不需要用游标了，明白了吗

Answer 5

 游标优化，关键是优化游标的查询sql，你查询sql都没发，怎么优化啊。
 另外你是根据rowid更新的，如果查询时候配合了order by应该效率很高的，关键还是看你的查询sql

Answer 6

INSERT INTO DATA_POLICYENDORKINDBASEINFO(
        POLICYNO,
        ENDORSEQNO,
        PLANCODE,
        RISKCODE,
        ITEMNO,
        ITEMKINDNO,
        KINDCODE,
        KINDNAME,
        ITEMDETAILCODE,
        ITEMDETAILLIST,
        MANUALPOLICYNO,
        -- ENDORNO,
        -- ENDORTYPE,
        -- ENDORTYPENAME,
        BUSINESSTYPE,
        CLASSCODE,
        INTERMEDIARYCODE,
        INTERMEDIARYCNNAME,
        AGREEMENTNO,
        SOLUTIONCODE,
        POLICYTYPE,
        APPLINAME,
        INSUREDNAME,
        STARTDATE,
        ENDDATE,
        UWYEAR,
        SUMGROSSPREMIUMA,
        SUMNETPREMIUMA,
        COMMISSIONA,
        SURCHARGEAMOUNTA,
        DISCOUNTRATE,
        COMMISIONRATE,
        CLIENTDISCOUNTA,
        UNDERWRITEENDDATE,
        UNDERWRITECODE,
        SALESMANCODE,
        OPERATORCODE,
        COMPANYCODE,
        ACCOUNTDATE,
        INPUTDATE,
        ALLOWRENEWIND,
        ISSUECOMPANY,
        ENDORTYPE,
        BUSINESSSOURCE,
        SUMINSUREDA,
        LIMITA,
        ORIGALCOMPANYCODE,
        UPDATEDATE,
        INSUREDCODE,
        PROMOTIONCODE,
        APPLICODE,
        OUTREINSCODE,
        OUTPOLICYNO,
        BROKERCODE,
        DEBITACCEPTER,
        COINSISSUEEXPENSE,
        DUESTARTDATE,
        TRCODE,
        TRNAME,
        SPECIALACCEPTANCE,
        CURRENCY,
        EXCHGRATE,
        OCCUPATION,
        OCCUPATIONNAME,
        TICCODE,
        TICCODENAME,
        --SUMGROSSPREMIUMANEW,
        --SUMNETPREMIUMANEW,
        --COMMISSIONANEW,
        --SURCHARGEAMOUNTANEW,
        --SUMINSUREDANEW,
        --LIMITANEW,
        -- KINDITEMRATE,
        KINDRISKRATE,
        Insuredsubjectmattercode,
        Insuredsubjectmatterdesc,
        Covercode,
        Covername,
        EmployeesClasscode,
        EmployeesOccupations,
        FACPLANCODE
      )SELECT  A.POLICYNO ,
               '000' ENDORSEQNO,
               ETL_FUNC_PACKAGE.GET_GUPOLICYCOPYRISK1(B.POLICYNO,B.ENDORSEQNO,B.RISKCODE,'PLANCODE') PLANCODE,
               B.RISKCODE,
               C.ITEMNO,
               C.ITEMKINDNO,
               C.KINDCODE,
               C.KINDNAME,
               C.ITEMDETAILCODE,
               C.ITEMDETAILLIST,
               A.MANUALPOLICYNO,
               -- ENDORNO,
               -- ENDORTYPE,
               -- ENDORTYPENAME,
               A.BUSINESSTYPE,
               ETL_FUNC_PACKAGE.GET_GUPOLICYCOPYRISK1(P_POLICYNO,'000',P_RISKCODE,'RISKCLASS'),
               A.INTERMEDIARYCODE,
               ETL_FUNC_PACKAGE.GET_AGENTNAME(INTERMEDIARYCODE) INTERMEDIARYCNNAME,
               A.AGREEMENTNO,
               A.SOLUTIONCODE,
               CASE WHEN A.RENEWIND = '0' AND A.ENDORSEQNO = '000' THEN 'N' --新单
                 WHEN A.RENEWIND = '1' AND A.ENDORSEQNO = '000'  THEN 'R' --续保单
                 WHEN A.ENDORSEQNO != '000' THEN 'E' --批单
               END POLICYTYPE,
               A.APPLINAME,
               A.INSUREDNAME,
               TRUNC(B.STARTDATE),
               TRUNC(B.ENDDATE),
               A.UWYEAR,
               NVL(C.GROSSPREMIUM,0),
               NVL(C.NETPREMIUM,0),
               0 COMMISSIONA,
               0 SURCHARGEAMOUNTA,
               0 DISCOUNTRATE,
               0 COMMISIONRATE,
               0 CLIENTDISCOUNTA,
               A.UNDERWRITEENDDATE,A.UNDERWRITECODE,
               A.SALESMANCODE,A.OPERATORCODE,
               ETL_FUNC_PACKAGE.GET_COMPANYCODE(A.COMPANYCODE) COMPANYCODE,
               TRUNC(GREATEST(B.STARTDATE,A.UNDERWRITEENDDATE)) ACCOUNTDATE,
               TRUNC(A.INPUTDATE),
               A.ALLOWRENEWIND,
               ETL_FUNC_PACKAGE.GET_COMPANYCODE(A.ISSUECOMPANY) ISSUECOMPANY,
               '00' ENDORTYPE,
               A.BUSINESSSOURCE,
               NVL(C.SUMINSURED,0)*DECODE(C.CALCULATEIND,'1',1,0), -- 不计保额取保额为0
               0 LIMITA,
               A.COMPANYCODE,
               SYSDATE,
               a.insuredcode,
               A.PROMOTIONCODE,
               A.APPLICODE,
               ETL_FUNC_PACKAGE.GET_POLICYCOPYREINSCEDEDINFO(P_POLICYNO, '000', 'OUTREINSCODE'),
               ETL_FUNC_PACKAGE.GET_POLICYCOPYREINSCEDEDINFO(P_POLICYNO, '000', 'OUTPOLICYNO'),
               ETL_FUNC_PACKAGE.GET_POLICYCOPYREINSCEDEDINFO(P_POLICYNO, '000', 'BROKERCODE'),
               ETL_FUNC_PACKAGE.GET_POLICYCOPYREINSCEDEDINFO(P_POLICYNO, '000', 'DEBITACCEPTER'),
               0, -- A.COINSISSUEEXPENSE,-- TODO
               TRUNC(B.STARTDATE) DUESTARTDATE,
               A.TRCODE,
               ETL_FUNC_PACKAGE.GET_TRNAME(A.TRCODE),
               A.SPECIALACCEPTANCE,
               A.CURRENCY,
               V_EXCHRATE,
               GET_GUPOLICYCOPYEMPOLYINFO('OCCUPATION',P_POLICYNO,P_ENDORSEQNO,C.ITEMNO),
               GET_GUPOLICYCOPYEMPOLYINFO('OCCUPATIONNAME',P_POLICYNO,P_ENDORSEQNO,C.ITEMNO),
               GET_GUPOLICYCOPYEMPOLYINFO('TICCODE',P_POLICYNO,P_ENDORSEQNO,C.ITEMNO),
               GET_GUPOLICYCOPYEMPOLYINFO('TICCODENAME',P_POLICYNO,P_ENDORSEQNO,C.ITEMNO),
               --SUMGROSSPREMIUMANEW,
               --SUMNETPREMIUMANEW,
               --COMMISSIONANEW,
               --SURCHARGEAMOUNTANEW,
               --SUMINSUREDANEW,
               --LIMITANEW,
               -- 0 KINDITEMRATE,
               0 KINDRISKRATE,
               GET_GUPOLICYCOPYITEMKINDINFO('INSUREDSUBJECTMATTERCODE',subStr(B.RISKCODE,0,2),P_POLICYNO,P_ENDORSEQNO,B.PLANCODE,B.RISKCODE,C.ITEMKINDNO) Insuredsubjectmattercode,
               GET_GUPOLICYCOPYITEMKINDINFO('INSUREDSUBJECTMATTERDESC',subStr(B.RISKCODE,0,2),P_POLICYNO,P_ENDORSEQNO,B.PLANCODE,B.RISKCODE,C.ITEMKINDNO) Insuredsubjectmatterdesc,
               GET_GUPOLICYCOPYITEMKINDINFO('COVERCODE',subStr(B.RISKCODE,0,2),P_POLICYNO,P_ENDORSEQNO,B.PLANCODE,B.RISKCODE,C.ITEMKINDNO) Covercode,
               GET_GUPOLICYCOPYITEMKINDINFO('COVERNAME',subStr(B.RISKCODE,0,2),P_POLICYNO,P_ENDORSEQNO,B.PLANCODE,B.RISKCODE,C.ITEMKINDNO) Covername,
               GET_GUPOLICYCOPYITEMKINDINFO('CLASSCODE',subStr(B.RISKCODE,0,2),P_POLICYNO,P_ENDORSEQNO,B.PLANCODE,B.RISKCODE,C.ITEMKINDNO) EmployeesClasscode,
               GET_GUPOLICYCOPYITEMKINDINFO('OCCUPATIONS',subStr(B.RISKCODE,0,2),P_POLICYNO,P_ENDORSEQNO,B.PLANCODE,B.RISKCODE,C.ITEMKINDNO) EmployeesOccupations,
               D.PLANCODE
        FROM GUPOLICYCOPYMAIN A,GUPOLICYCOPYRISK B,GUPOLICYCOPYITEMKIND C,GRFACPOLICY  D
        WHERE A.POLICYNO = B.POLICYNO
          AND A.ENDORSEQNO = B.ENDORSEQNO
          AND A.POLICYNO = C.POLICYNO
          AND A.ENDORSEQNO = C.ENDORSEQNO
          AND A.POLICYNO = P_POLICYNO
          AND B.RISKCODE = C.RISKCODE
          AND A.ENDORSEQNO = P_ENDORSEQNO
          AND B.RISKCODE = P_RISKCODE
           AND C.POLICYNO = D.POLICYNO(+)
          AND C.ENDORSEQNO = D.ENDORSEQNO(+)
          AND C.ITEMNO=D.DANGERUNITNO(+);
      -- 设置险别保额/保费/佣金/征费
      FOR REC_KIND IN CUR_KIND LOOP
        GET_GUPOLICYCOPYKINDINFO(P_POLICYNO,P_ENDORSEQNO,P_RISKCODE,REC_KIND.ITEMNO,REC_KIND.ITEMKINDNO,V_KINDINFO);
        UPDATE DATA_POLICYENDORKINDBASEINFO T
           SET T.SUMGROSSPREMIUMA = V_KINDINFO.GROSSPREMIUMKIND * V_EXCHRATE,
               T.SUMNETPREMIUMA   = V_KINDINFO.NETPREMIUMKIND * V_EXCHRATE,
               T.SUMINSUREDA      = T.SUMINSUREDA * V_EXCHRATE,
               T.COMMISSIONA      = V_KINDINFO.COMMISSIONKIND * V_EXCHRATE,
               T.CLIENTDISCOUNTA  = V_KINDINFO.DISCOUNTKIND * V_EXCHRATE,
               T.COMMISIONRATE    = V_KINDINFO.COMMISSIONRATE ,
               T.DISCOUNTRATE     = V_KINDINFO.DISCOUNTRATE,
               T.SURCHARGEAMOUNTA = V_KINDINFO.SURCHARGEAMOUNTKIND* V_EXCHRATE ,
               T.LIMITA = V_KINDINFO.LIMITKIND * V_EXCHRATE,
               T.KINDITEMRATE = V_KINDINFO.KINDITEMRATE,
               T.KINDRISKRATE = V_KINDINFO.KINDRISKRATE
         WHERE T.ROWID = REC_KIND.ROW_ID;
         V_TOTALKINDCOMMISION := V_TOTALKINDCOMMISION + V_KINDINFO.COMMISSIONKIND;
         V_TOTALKINDDISCOUNT := V_TOTALKINDDISCOUNT + V_KINDINFO.DISCOUNTKIND;
         V_TOTALKINDLIMIT := V_TOTALKINDLIMIT + V_KINDINFO.LIMITKIND;
         IF V_RISKCOMMISION = 0 OR V_RISKDISCOUNT = 0 OR V_RISKLIMIT = 0 THEN
           V_RISKCOMMISION := V_KINDINFO.COMMISSIONRISK;
           V_RISKDISCOUNT := V_KINDINFO.DISCOUNTRISK;
           V_RISKLIMIT := V_KINDINFO.LIMITRISK;
         END IF;
      END LOOP;这是游标内容