Cursor for update

Cursor for update

What is Cursor for update?

In most of the cases, the row fetched by the cursor query is updated inside the cursor loop. When an update occurs, the updated row is locked by Oracle. When the ‘FOR UPDATE’ clause is given in the cursor, oracle locks the rows that satisfy the where condition during opening of the cursor. Instead of the where clause in the update statement, the ‘CURRENT OF’ clause can be used. The following example illustrates that.

See the example below:

CREATE OR REPLACE PROCEDURE UPDATE_OPEN_AMOUNT IS
CURSOR cur_loanGrant IS
SELECT * FROM T_LOANGRANT WHERE status = 'O' FOR UPDATE; -- Declaring the cursor and associating the query that returns more than one row.
rec_LoanGrant t_loangrant%rowtype;
v_total_repay t_loangrant.disb_amount%type;
v_open_amt t_loangrant.open_amt%Type

BEGIN
OPEN cur_loanGrant; -- Open the cursor
Loop
Fetch cur_loanGrant INTO rec_loangrant; -- Get the cursor in the record variable.
EXIT WHEN cur_loanGrant%NOTFOUND; -- Exit the loop when no more rows are found.

-- Get the sum of repaid amount for each loan.
SELECT SUM(REPAY_AMOUNT) INTO v_total_repay WHERE loan_nbr = rec_loanGrant.Loan_nbr;

-- Calculate Open amount:
v_open_amt := rec_loangrant.disb_amount - v_total_repay;

-- update the t_loangrant.open_Amt
UPDATE T_LOANGRANT SET OPEN_AMT = v_open_amt WHERE CURRENT OF cur_loanGrant;

-- If The loan is fully closed then set the Status to 'C'
IF v_open_amt = 0 THEN
UPDATE T_LOANGRANT SET STATUS = 'C' WHERE CURRENT OF cur_loanGrant;
END IF;

END LOOP;

IF cur_loanGrant%ISOPEN THEN
CLOSE cur_loanGrant;
END IF; -- Close the cursor.

END UPDATE_OPEN_AMOUNT;
/