The for statement has two forms. It can be use with:
You break the for loop with an
EXIT
statement.
for index in [reverse] lower_bound .. upperd_bound loop
statement
end loop [label]
for record in ([select statement|explicit cursor]) loop
statement with record.column
end loop [label]
where the in condition contains
SET SERVEROUTPUT ON
BEGIN
FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE (i);
end loop;
END;
/
1
2
3
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;
/
On Cursor FOR Loops.
Recommendations:
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
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;
/