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;
/