這個過程怎麼優化才好了？為什麼執行這麼慢！高手請進來看看

Question

create or replace procedure redo_spjxcritem_rkd(p_STARTRQ IN DATE,
                                                p_ENDRQ   IN DATE) as
  CURSOR C_RKD IS
    SELECT A.RKD16  RKRQ, 
           A.BM01   BM,
           A.WLDW01 WLDW,
           A.RKD19  TJXYH,           
           A.RKD04  HZFS,
           B.SPXX01 SPXX,
           SUM(B.RKDI05) RKSL,
           sum(B.RKDI07) JHJE
      FROM RKDITEM B, RKD A
     WHERE B.RKD01 = A.RKD01
       AND A.RKD16 >= p_STARTRQ
       AND A.RKD16 <= p_ENDRQ
     group by A.RKD16,A.BM01,A.WLDW01,A.RKD19,A.RKD04,B.SPXX01;  
      V_RKD      C_RKD%rowtype;
  MAXSPJXC03 NUMBER(16);
  RQ         date;
 -- icount      number;begin  OPEN C_RKD;
  FETCH C_RKD
    INTO V_RKD;
    --icount:=0;
  WHILE C_RKD%found LOOP
  
    IF V_RKD.RKRQ <= TO_Date('2003-10-27', 'yyyy-mm-dd') THEN
      RQ := TO_Date('2003-10-27', 'yyyy-mm-dd');
    ELSE
      RQ := V_RKD.RKRQ;
    END IF;
  
    UPDATE SPJXCRITEM A
       SET A.SPJXCI95 = NVL(SPJXCI95, 0) + NVL(V_RKD.Jhje, 0),
           A.SPJXCI94 = NVL(SPJXCI94, 0) + NVL(V_RKD.Rksl, 0),
           A.SPJXCI96 = V_RKD.Tjxyh,
           A.SPJXCI97 = 4
     where A.SPJXC08 = V_RKD.Tjxyh
       AND A.SPJXC02 = V_RKD.HZFS          
       AND A.Spjxci01 = 1
       AND A.WLDW01 = V_RKD.Wldw
       AND A.BM01 = V_RKD.Bm          
       AND A.Spjxc01 = RQ
       AND A.SPXX01 = V_RKD.Spxx;
  
    IF SQL%ROWCOUNT = 0 THEN
     
      SELECT MAX(SPJXC03) INTO MAXSPJXC03 FROM SPJXCRITEM;
      INSERT INTO SPJXCRITEM
        (SPJXC03,
         SPJXCI01,
         SPJXC01,
         BM01,
         WLDW01,
         SPXX01,
         SPJXC02,
         SPJXC08,
         SPJXCI94,
         SPJXCI95,
         SPJXCI96,
         SPJXCI97)
      VALUES
        (MAXSPJXC03 + 1,
         1,
         RQ,
         V_RKD.Bm,
         V_RKD.WLDW,
         V_RKD.SPXX,
         V_RKD.HZFS,
         V_RKD.TJXYH,
         V_RKD.Rksl,
         V_RKD.Jhje,
         V_RKD.Tjxyh,
         5);
    END IF;
    --icount:=icount+1;
   -- dbms_output.put_line('hehe');
    FETCH C_RKD
      INTO V_RKD;
  END LOOP;
  CLOSE C_RKD;
end;

Answer 1

用游标,而且还要用UPDATE,相当于游标有N行就要扫描SPJXCRITEM这个表N次,直接用UPDATE不行???
UPDATE /*+BYPASS_UJVC*/ (SELECT T1.SPJXCI95,
             NVL(T1.SPJXCI95, 0) + NVL(J1.JHJE, 0) AS NEW_SPJXCI95,
             T1.SPJXCI94,
             NVL(T1.SPJXCI94, 0) + NVL(J1.RKSL, 0) AS NEW_SPJXCI94,
             T1.SPJXCI96,
             J1.TJXYH,
             T1.SPJXCI97,
        FROM (SELECT A.RKD16 RKRQ,
                     A.BM01 BM,
                     A.WLDW01 WLDW,
                     A.RKD19 TJXYH,
                     A.RKD04 HZFS,
                     B.SPXX01 SPXX,
                     SUM(B.RKDI05) RKSL,
                     SUM(B.RKDI07) JHJE
                FROM RKDITEM B,
                     RKD     A
               WHERE B.RKD01 = A.RKD01
                 AND A.RKD16 >= P_STARTRQ
                 AND A.RKD16 <= P_ENDRQ
               GROUP BY A.RKD16,
                        A.BM01,
                        A.WLDW01,
                        A.RKD19,
                        A.RKD04,
                        B.SPXX01) J1,
             SPJXCRITEM T1
       WHERE A.SPJXC08 = J1.TJXYH
         AND T1.SPJXC02 = J1.HZFS
         AND T1.SPJXCI01 = 1
         AND T1.WLDW01 = J1.WLDW
         AND T1.BM01 = J1.BM
         AND T1.SPJXC01 = RQ
         AND T1.SPXX01 = J1.SPXX)
   SET SPJXCI95 = NEW_SPJXCI95,
       SPJXCI94 = NEW_SPJXCI94,
       SPJXCI96 = TJXYH,
       SPJXCI97 = 4
一次UPDATE后再判断是否成功UPDATE然后再做下面的,估计会比你用游标这样快....