3-6. Iterating Cursor Results Until All Rows Have Been Returned
Problem
You have created a cursor and retrieved a number of rows from the database. As a result, you want to loop through the results and do some processing on them.
Solution
Use a standard FOR loop to iterate through the records. Within each iteration of the loop, process the current record. The following code shows the use of a FOR loop to iterate through the records retrieved from the cursor and display each employee name and e-mail. Each iteration of the loop returns an employee with the job_id of 'ST_MAN', and the loop will continue to execute until the cursor has been exhausted.
DECLARE
CURSOR emp_cur IS
SELECT *
FROM employees
WHERE job_id = 'ST_MAN';
emp_rec employees%ROWTYPE;
BEGIN
FOR emp_rec IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name ||
' - ' || emp_rec.email);
END LOOP;
END;
Here are the results:
Matthew Weiss - MWEISS
Adam Fripp - AFRIPP
Payam Kaufling - PKAUFLIN
Shanta Vollman - SVOLLMAN
Kevin Mourgos - KMOURGOS
PL/SQL procedure successfully completed.
As you can see, the employee records that meet the specified criteria are displayed.
How It Works
The FOR...IN loop works by iterating over a collection of data such as a cursor. The loop begins with the FOR keyword followed by a variable that will be used to contain the current value or values from the collection of data you are iterating. In this case, the variable is a record that will contain the current row. Next, the IN collection clause is used to denote the collection of data being iterated. The loop is terminated just like all other PL/SQL loops, using the END LOOP keywords. There is no need to evaluate a condition in a FOR loop because the collection or range that is used to define the loop determines its scope. However, it is possible to use the EXIT keyword to escape from a loop prematurely. For more information regarding the use of EXIT, please see Recipe 3-5.
The benefit of using a FOR loop is decreased lines of code and better readability. Rather than opening the cursor prior to the loop, fetching a row into a record with each iteration, and then closing the cursor after the loop, you simply fetch the row into the record within the LOOP definition itself.