PL/SQL - FOR LOOP Statement (On Cursor, Sequence, …)

About

The for statement has two forms. It can be use with:

• a sequence to loop though a sequence of number or through a collection
• a cursor

You break the for loop with an

EXIT

statement.

Articles Related

Syntax

• Sequence Syntax

for index in [reverse] lower_bound .. upperd_bound loop
statement
end loop [label]

• Cursor Syntax

for record in ([select statement|explicit cursor]) loop
statement with record.column 
end loop [label]

where the in condition contains

• a select statement that defines a apps/search/search.jsp
• or an apps/search/search.jsp

Sequence

Basic

SET SERVEROUTPUT ON
BEGIN
FOR i IN 1..3 LOOP
  DBMS_OUTPUT.PUT_LINE (i);
end loop;
END;
/

1
2
3

Collection

with a collection

DECLARE
   TYPE at_most_twelve_t IS VARRAY (12) OF VARCHAR2 (100);
   l_months   at_most_twelve_t;
BEGIN
   
   l_months = at_most_twelve_t('January','February','March');

   FOR indx IN 1 .. l_months.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_months (indx));
   END LOOP;
   
END;
/

Cursor

On Cursor FOR Loops.

Recommendations:

• If you expect to retrieve just one row, use an implicit SELECT INTO query
• If you expect to retrieve multiple rows of data and you know the upper limit (as in, “I will never get more than 100 rows in this query”), use BULK COLLECT into a collection of type varray whose upper limit matches what you know about the query.
• If you expect to retrieve multiple rows of data and you do not know the upper limit, use BULK COLLECT with a FETCH statement that relies on a LIMIT clause to ensure that you do not consume too much per-session memory.

Implicit

apps/search/search.jsp FOR LOOP statement: See apps/search/search.jsp

SET SERVEROUTPUT ON
BEGIN
FOR i IN (select 'Nico' as name  from dual union all select 'Nicol' from dual) LOOP
  DBMS_OUTPUT.PUT_LINE (i.name);
end loop;
END;
/

Nico
Nicol

Explicit

DECLARE
  CURSOR c1 IS
    SELECT last_name, job_id FROM employees
    WHERE job_id LIKE '%CLERK%' AND manager_id > 120
    ORDER BY last_name;
BEGIN
  FOR item IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE
      ('Name = ' || item.last_name || ', Job = ' || item.job_id);
  END LOOP;
END;
/

With parameters:

DECLARE
  CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
    SELECT * FROM employees
    WHERE job_id = job
    AND salary > max_wage;
BEGIN
  FOR person IN c1('ST_CLERK', 3000)
  LOOP
     -- process data record
    DBMS_OUTPUT.PUT_LINE (
      'Name = ' || person.last_name || ', salary = ' ||
      person.salary || ', Job Id = ' || person.job_id
    );
  END LOOP;
END;
/

Reference

• Oracle Magazine - On Cursor FOR Loops
• For loop statement reference