Using Explicit Cursors  «Prev  Next»

Lesson 9Cursor FOR Loop
ObjectiveUse a cursor FOR loop to process Records

PL/SQL Cursor FOR Loop

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.

Cursor FOR loop 
DECLARE
  CURSOR order_product IS 
  SELECT product_name,
  current_inventory_count
  FROM PRODUCT
  WHERE current_inventory_count < 10;
BEGIN
  FOR get_products IN order_products
  LOOP
   DBMS_OUTPUT.PUT_LINE 
    (get_products.product_name ||
     'has to be reordered');
  END LOOP;
END;
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.

PL/SQL For Loop
  1. The FOR loop eliminates the use of OPEN, FETCH, and CLOSE statements. It also eliminates the declaration of the variables for fetching the data. The FOR loop index get_products is implicitly declared as a record.
  2. Dot notation is used to reference the product_name column within the cursor.
The FOR loop eliminates the use of OPEN, FETCH, and CLOSE statements.

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: 
order_products%ROWTYPE;

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