Oracle PL SQL cursor For loops

The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns and opens a cursor. With each iteration the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or if an exception is raised.

The following procedure is followed in most of the situations in PL/SQL:

1.    Open a cursor

2.    Start a loop

3.    Fetch the cursor

4.    Check whether rows are returned

5.    Process

6.    Close the loop

7.    Close the cursor

Cursor For Loop allows us to simplify this procedure by letting PL/SQL do most of the things for us.

 You can simplify coding by using a Cursor For Loop instead of the OPEN, FETCH, and CLOSE statements.

 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, and then closes the cursor when all rows have been processed.

The cursor FOR LOOP statement lets you issue a SELECT statement and then immediately loop through the rows of the result set. This statement can use either an implicit or explicit cursor. If you use the SELECT statement only in the cursor FOR LOOP statement, then specify the SELECT statement inside the cursor FOR LOOP statement, as in. This form of the cursor FOR LOOP statement uses an implicit cursor, and is called an implicit cursor FOR LOOP statement. Because the implicit cursor is internal to the statement, you cannot reference it with the name SQL. If you use the SELECT statement more than once in the same PL/SQL unit, then define an explicit cursor for it and specify that cursor in the cursor FOR LOOP statement. This form of the cursor FOR LOOP statement is called an explicit cursor FOR LOOP statement. You can use the same explicit cursor elsewhere in the same PL/SQL unit. The cursor FOR LOOP statement implicitly declares its loop index as a %ROWTYPE record variable of the type that its cursor returns. This record is local to the loop and exists only during loop execution. Statements inside the loop can reference the record and its fields. They can reference calculated columns only by aliases. After declaring the loop index record variable, the FOR LOOP statement opens the specified cursor. With each iteration of the loop, the FOR LOOP statement fetches a row from the result set and stores it in the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or if an exception is raised.

Cursor For Loop Example

DECLARE CURSOR c1 IS

 SELECT ename, sal, deptno

 FROM emp;

 ...

 BEGIN

 FOR emp_rec IN c1 LOOP

 ...

 salary_total := salary_total +

 emp_rec.sal;

 END LOOP;