Cursor for loop

Cursor for loop

What is Cursor for loop?

In most of the explicit cursors, the programmer tends to open a cursor, fetch it and loop through it and finally close the cursor. A cursor FOR loop could be used instead of these operations. A cursor FOR Loop implicitly declares its loop index as a record that represents a row fetched from the database. Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, then closes the cursor when all rows have been processed.

An example script is given below:

CREATE OR REPLACE PROCEDURE UPDATE_OPEN_AMOUNT IS
/* This stored procedure uses cursor for loop to update the OPEN_AMT in the T_LOANGRANT table. */
CURSOR cur_loanGrant IS
SELECT * FROM T_LOANGRANT WHERE status = 'O'; -- 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

FOR rec_LoanGrant IN cur_loanGrant LOOP
/* Need not declare rec_LoanGrant explicitly as it would be done automatically.
No Open and Fetch statements. Just the loop will do the work.
*/

-- 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 LOAN_NBR = rec_loanGrant.loan_nbr;

-- 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 LOAN_NBR = rec_loanGrant.loan_nbr;
END IF;

END LOOP;

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

END UPDATE_OPEN_AMOUNT;
/