Update Statement in SQL

Question

I have one table named as temp_stock_reco. I have created one procedure for update two columns in that table.

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;

i want to reduce this one a single by using decode function.Kindly help me to do this one

Answer 1

You need to avoid catching and reraising errors with a user defined error number. That will only make it harder to see where the error is happening. You should also avoid using explicit cursors and try using PL/SQL collections. As long as you are using 9i or above PL/SQL collections are the better way to go. You should probably have an index on transdetail as follows:

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

This will greatly reduce the number of rows that Oracle needs to look at. It will do a range scan of the transdate column for each of the transtype values that you are searching for, plus the index has all of the columns that you want. Hence Oracle won't need to look at the index, then look at the table.

-- 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;
/