Simplest Form - SELECT inside the loop
BEGIN
FOR rec IN (SELECT * FROM hr.employees)
LOOP
DBMS_OUTPUT.put_line (rec.last_name);
END LOOP;
END;
Cursor FOR Loop with Explicit Cursor
DECLARE
CURSOR emps_cur
IS
SELECT * FROM hr.employees;
BEGIN
FOR rec IN emps_cur
LOOP
DBMS_OUTPUT.put_line (rec.last_name);
END LOOP;
FOR rec IN emps_cur
LOOP
DBMS_OUTPUT.put_line (rec.salary);
END LOOP;
END;
Parameterized Cursor FOR Loop
DECLARE
CURSOR emps_cur (department_id_in IN INTEGER)
IS
SELECT * FROM hr.employees
WHERE department_id = department_id_in;
BEGIN
FOR rec IN emps_cur (1700)
LOOP
DBMS_OUTPUT.put_line (rec.last_name);
END LOOP;
FOR rec IN emps_cur (50)
LOOP
DBMS_OUTPUT.put_line (rec.salary);
END LOOP;
END;
Package Based Cursor
CREATE OR REPLACE PACKAGE emps_pkg
IS
CURSOR emps_cur
IS
SELECT * FROM hr.employees;
END;
Using a Package-based Cursor
BEGIN
FOR rec IN emps_pkg.emps_cur
LOOP
DBMS_OUTPUT.put_line (rec.last_name);
END LOOP;
END;
Hide SELECT in Package-based Cursor
CREATE OR REPLACE PACKAGE emps_pkg
IS
CURSOR emps_cur
RETURN hr.employees%ROWTYPE;
END;
CREATE OR REPLACE PACKAGE BODY emps_pkg
IS
CURSOR emps_cur RETURN hr.employees%ROWTYPE
IS
SELECT * FROM hr.employees;
END;
BEGIN
FOR rec IN emps_pkg.emps_cur
LOOP
DBMS_OUTPUT.put_line (rec.last_name);
END LOOP;
END;