Here for the below program i need to print the amt_running_bal from the previous value. but it is not working and showing error. what is the error in the below program.Please provide any solution for this.

DECLARE
   total Number := 1000000;
   c_cod_acct_no Char;
   c_amt_txn Number;
   c_cod_drcr Char;
   c_amt_running_bal Number;
   amt_running_bal Number;
   CURSOR c_chnos1 is
   SELECT cod_drcr, amt_txn,amt_running_bal FROM chnos1;
   BEGIN
   OPEN c_chnos1;
   FOR k IN 1..2 LOOP
   FETCH c_chnos1 into c_cod_drcr,c_amt_txn,c_amt_running_bal;
   if  c_cod_drcr = 'C' then
   total := total   c_amt_txn;
   Update chnos1 SET amt_running_bal = total where cod_drcr='C' ;
   elsif
   c_cod_drcr = 'D' then
   total := total - c_amt_txn;
   Update chnos1 SET amt_running_bal = total where cod_drcr='D';
   else
   total := total   c_amt_txn;
   Update chnos1 SET amt_running_bal = total where cod_drcr='C';
   end if;
   END LOOP;
   CLOSE c_chnos1;
   END;
   /

CodePudding user response：

Your query does not work as you limit the loop to k IN 1..2 so it will only read two rows from the cursor and there is no correlation between the row you are reading from the cursor and what you are updating; in fact, you are updating all the rows WHERE cod_drcr = 'C' or WHERE cod_drcr = 'D' and not just the current row. You could fix it by correlating the updates to the current row using the ROWID pseudo-column but it is an inefficient solution to use cursors as it will be slow and generate redo/undo log entries for each iteration of the cursor loop.

Instead, do it all in a single MERGE statement using an analytic SUM and a CASE expression:

MERGE INTO chnos1 dst
USING (
  SELECT rowid AS rid,
         1000000
           SUM(
             CASE cod_drcr
             WHEN 'C' THEN  amt_txn
             WHEN 'D' THEN -amt_txn
             ELSE 0
             END
           )
           OVER (
             -- Use something like this to update each account
             -- PARTITION BY cod_acct_no ORDER BY payment_date
             -- However, you haven't said how to partition or order the rows so use this
             ORDER BY ROWNUM
           ) AS total
  FROM   chnos1
) src
ON (dst.ROWID = src.rid)
WHEN MATCHED THEN
  UPDATE SET amt_running_bal = src.total;

fiddle

CodePudding user response：

it is not working and showing error.

Only if you told us which error and what "not working" means ... because, for my sample table, it kind of "works" (I don't know whether that's what you actually wanted, but - there's no Oracle error).

SQL> SELECT * FROM chnos1;

COD_DRCR      AMT_TXN AMT_RUNNING_BAL
---------- ---------- ---------------
C                 100            1000

Your code:

SQL> DECLARE
  2     total              NUMBER := 1000000;
  3     c_cod_acct_no      CHAR;
  4     c_amt_txn          NUMBER;
  5     c_cod_drcr         CHAR;
  6     c_amt_running_bal  NUMBER;
  7     amt_running_bal    NUMBER;
  8
  9     CURSOR c_chnos1 IS SELECT cod_drcr, amt_txn, amt_running_bal FROM chnos1;
 10  BEGIN
 11     OPEN c_chnos1;
 12
 13     FOR k IN 1 .. 2
 14     LOOP
 15        FETCH c_chnos1 INTO c_cod_drcr, c_amt_txn, c_amt_running_bal;
 16
 17        IF c_cod_drcr = 'C'
 18        THEN
 19           total := total   c_amt_txn;
 20
 21           UPDATE chnos1
 22              SET amt_running_bal = total
 23            WHERE cod_drcr = 'C';
 24        ELSIF c_cod_drcr = 'D'
 25        THEN
 26           total := total - c_amt_txn;
 27
 28           UPDATE chnos1
 29              SET amt_running_bal = total
 30            WHERE cod_drcr = 'D';
 31        ELSE
 32           total := total   c_amt_txn;
 33
 34           UPDATE chnos1
 35              SET amt_running_bal = total
 36            WHERE cod_drcr = 'C';
 37        END IF;
 38     END LOOP;
 39
 40     CLOSE c_chnos1;
 41  END;
 42  /

PL/SQL procedure successfully completed.

Result:

SQL> SELECT * FROM chnos1;

COD_DRCR      AMT_TXN AMT_RUNNING_BAL
---------- ---------- ---------------
C                 100         1000200

SQL>