SQL中的Update语句

Question

我有一张名为temp_stock_reco的桌子。我已经为更新该表中的两列创建了一个过程。

CREATE OR REPLACE PROCEDURE TEST_RECPT_0705
is
CURSOR RECEIPT IS
select sr_cd,to_char(trunc(transdate,'mm'),'YYYYMM')Month,
sum(quantity)Receipt from transdetail
where transtype IN('STKREC','RP','RECADJ','SRET') and trunc(transdate,'mm')
between '01-April-2013' and '31-March-2014'
group by trunc(transdate,'mm'),sr_cd;
CURSOR ISSUE IS
select sr_cd,to_char(trunc(transdate,'mm'),'YYYYMM') Month,sum(quantity) Issue
from transdetail
where transtype IN('SINV','IP','STA','ISSADJ','PURRET')
and trunc(transdate,'mm') between '01-April-2013' and '31-March-2014'
group by trunc(transdate,'mm'),sr_cd;
BEGIN
OPEN RECEIPT;
LOOP
UPDATE temp_stock_reco SET RECEIPT = I.RECEIPT
WHERE SR_CD = I.SR_CD
AND YYYYMM = I.MONTH;
OPEN ISSUE;
LOOP
UPDATE temp_stock_reco SET ISSUE = J.ISSUE
WHERE SR_CD = J.SR_CD
AND YYYYMM = J.MONTH;
END LOOP;
CLOSE ISSUE;
END LOOP;
CLOSE RECEIPT;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' 
-ERROR- '||SQLERRM);
END;

我想通过使用解码function.Kindly把这个减少一个，帮我做这个

Answer 1

您需要避免使用用户定义的错误号捕获和重命名错误。这只会使我们更难看到错误发生在哪里。您还应该避免使用显式游标，并尝试使用PL/SQL集合。只要您使用的是9i或以上，PL/SQL集合是更好的方法。您可能应该在transdetail上有如下索引：

CREATE UNIQUE INDEX transdetail_idx1 
  ON transdetail ( transtype, transdate, sr_cd, quantity);

这将大大减少Oracle需要查看的行数。它将对正在搜索的每个transdate值进行transtype列的范围扫描，而且索引包含所需的所有列。因此，Oracle不需要查看索引，然后查看表。

-- Let unhandled exceptions remain unhandled
-- EXCEPTION
-- This is a terrible example of error handling, don't use it
-- WHEN OTHERS THEN
-- raise_application_error(-20001,'An error was encountered - '||SQLCODE||' 
-- -ERROR- '||SQLERRM);
-- END;
-- /


CREATE OR REPLACE PROCEDURE TEST_RECPT_0705
AS
   TYPE transdetail_rec IS RECORD (
      sr_cd     transdetail.sr_cd%TYPE,
      transdate transdetail.transdate%TYPE,
      quantity  transdetail.quantity%TYPE );

   TYPE transdetail_type IS TABLE OF transdetail_rec
      INDEX BY PLS_INTEGER;

   transdetail_tab transdetail_type;
BEGIN
   SELECT sr_cd, to_char(trunc(transdate,'mm'),'YYYYMM') Month, sum(quantity)Receipt 
     BULK COLLECT INTO transdetail_tab
     FROM transdetail
    WHERE transtype IN('STKREC','RP','RECADJ','SRET') 
      AND trunc(transdate,'mm') BETWEEN '01-April-2013' AND '31-March-2014'
    GROUP BY trunc(transdate,'mm'),sr_cd;

   FORALL i IN transdetail_tab.FIRST..transdetail_tab.LAST
   UPDATE transdetail
      SET receipt = transdetail_tab(i).RECEIPT
    WHERE sr_cd   = transdetail_tab(i).SR_CD
      AND yyyymm  = transdetail_tab(i).MONTH;

    SELECT sr_cd, to_char(trunc(transdate,'mm'),'YYYYMM') Month,  sum(quantity) Issue
      FROM transdetail
     WHERE transtype IN('SINV','IP','STA','ISSADJ','PURRET')
       AND trunc(transdate,'mm') BETWEEN '01-April-2013' AND '31-March-2014'
     GROUP BY trunc(transdate,'mm'), sr_cd;

   FORALL i IN transdetail_tab.FIRST..transdetail_tab.LAST
   UPDATE transdetail
      SET receipt = transdetail_tab(i).RECEIPT
    WHERE sr_cd   = transdetail_tab(i).SR_CD
      AND yyyymm  = transdetail_tab(i).MONTH;

   COMMIT;
END;
/