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.
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.
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.
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
DECLARE
CURSOR order_products 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;
FOR loop cursor block
FOR get_products IN order_products LOOP
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.
get_products.product_name
Dot notation is used to reference the product_name column within the cursor.
Simple FOR loops iterate over a specified range of integers (lower_bound .. upper_bound).
The number of iterations is known before the loop is entered. The range is evaluated when the FOR loop is first entered and is never re-evaluated.
If lower_ bound equals upper_bound, the loop body is executed once.
As Example 6 shows, the sequence of statements is executed once for each integer in the range 1 to 500. After each iteration, the loop counter is incremented.
Example 6: Simple FOR-LOOP Statement
SQL> BEGIN
2 FOR i IN 1..3 LOOP
3 DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
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;
Using For Loop
Click the link below to build a cursor block that uses a FOR loop by using an evaluative simulation.
Course project: Using the FOR loop
Your solution should have followed these steps:
You connected to the database through SQL*Plus by typing PETSTORE as the user name, GREATPETS as the password, and MYDB as the host string.
You started building the PL/SQL block by declaring a cursor that selects the product ID from the product table for the product Rabbit. You named the cursor get_product by typing DECLARE at the SQL prompt and typing the following text at the 2 prompt:
CURSOR GET_PRODUCT IS
SELECT PRODUCT_ID FROM PRODUCT
WHERE PRODUCT_NAME = 'Rabbit';
Retrieve data in the FOR loop. The FOR loop index is product_value by typing the following text at the following prompts and pressing Enter:
3> BEGIN
4> FOR product_value IN get_product LOOP
Insert a record into PET_CARE_LOG, with the product ID set to the value in the variable LOG_TEXT set to "Rabbits eat fresh grass only." and log_datetime set to the system date by typing the following text at the 5 prompt and pressing Enter:
5> INSERT INTO PET_CARE_LOG (PRODUCT_ID, LOG_DATETIME, LOG_TEXT) VALUES (product_value.product_id, to_date('2-SEP-99 05:30PM','dd-mon-yy hh:miPM'), 'Rabbits eat fresh grass only.');
You closed the loop, committed the changes, and ended the PL/SQL block by typing the following text at the following prompts and pressing Enter:
6> END LOOP;
7> COMMIT;
8> END;
You compiled your PL/SQL block by typing /at the 9 prompt and pressing Enter.
SQL*Plus then displayed the result of the compilation of your block.
Cursor FOR Loop
A cursor FOR loop is a loop that is associated with (and actually defined by) an explicit cursor or a SELECT statement incorporated directly within the loop boundary. Use the cursor FOR loop only if you need to fetch and process each and every record from a cursor, which is often the case with cursors. The cursor FOR loop leverages the tight and effective integration of the procedural constructs with the power of the SQL database language. It reduces the volume of code you need to write to fetch data from a cursor. It decreases the chance of introducing loop errors in your programming and loops are one of the more error-prone parts of a program.
Here is the basic syntax of a cursor FOR loop:
FOR record IN { cursor_name | (explicit SELECT statement) }
LOOP
executable statement(s)
END LOOP;
where record is a record declared implicitly by PL/SQL with the %ROWTYPE attribute against the cursor specified by cursor_name.
The next lesson concludes this module.
