You can simplify the code for an explicit cursor by using FOR loop instead of using the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches records of values from the result set
into fields within the record, and closes the cursor once all records have been processed.
Within the FOR loop, you need not open and close a cursor explicitly by using the OPEN and CLOSE statements that are used normally within other loops. The FOR loop takes care of opening and closing the cursor automatically. Also, note that each record returned into the variable list is not declared explicitly within the DECLARE section of the cursor block.
Advantages of using the FOR loop
Let us say you need to update the data of five columns for 10 records within the PRODUCT table.
Each update is different based on the PRODUCT_NAME column value.
If you used an
OPEN … FETCH… CLOSE …
block, you would need to write 10 FETCH statements and define 50 variables.
A better and more efficient way to do the same is by using a cursor FOR loop.
The following MouseOver explains the FOR loop with an example.
For Loop Cursor Block
Note that when the cursor FOR loop is entered, the cursor name cannot belong to a cursor that was already opened by an OPEN statement or by an enclosing cursor FOR loop. Before every iteration of the FOR loop, PL/SQL fetches into the implicitly declared record, which is equivalent to a record explicitly declared as follows:
The next lesson concludes this module.
Using For Loop
Click the link below to build a cursor block that uses a FOR loop by using an evaluative simulation. Using For Loop