Oracle and PL/SQL Recipes: A Problem-Solution Approach

3-5. Looping Until a Specified Condition Is Met

Problem

You want to loop through a set of statements until a specified condition evaluates to true.

Solution

Use a simple LOOP statement along with an EXIT clause to define a condition that will end the iteration. The following example shows a simple LOOP that will print out each employee with a department_id equal to 90:

DECLARE   CURSOR emp_cur IS   SELECT *   FROM employees   WHERE department_id = 90;   emp_rec employees%ROWTYPE; BEGIN   OPEN emp_cur;   LOOP   FETCH emp_cur into emp_rec;   IF emp_cur%FOUND THEN     DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name ||         ' - ' || emp_rec.email);   ELSE     EXIT;    END IF;   END LOOP;   CLOSE emp_cur; END;

As you can ...