Using Explicit Cursors  «Prev  Next»

Using the FOR loop in PL/SQL

Course project: Using the FOR loop

Your solution should have followed these steps:

  1. 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.
  2. 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';
    
  3. 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
  4. 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.');
  5. 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;
  6. You compiled your PL/SQL block by typing /at the 9 prompt and pressing Enter.
  7. 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.