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

2-6. Looping Through Rows from a Query

Problem

A query that you are issuing to the database will return many rows. You want to loop through those rows and process them accordingly.

Solution #1

There are a couple of different solutions for looping through rows from a query. One is to work directly with a SELECT statement and use a FOR loop along with it. In the following example, you will see this technique in action:

SET SERVEROUTPUT ON; BEGIN   FOR emp IN   (     SELECT first_name, last_name, email     FROM employees     WHERE commission_pct is not NULL   )   LOOP     DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name || ' - ' || emp.email);   END LOOP; END;

Solution #2

Similarly, you can choose to use a FOR loop along with a cursor. ...