How to Fetch Data from Cursor in Oracle Using For Loop

In Oracle, For Loop is the easiest way to work with Cursor. It will open the cursor, fetch and closes by itself. Below are the examples to how to fetch data from Cursor in Oracle using For Loop.

For Loop Cursor Examples

1. Cursor without Parameter Example

SET SERVEROUTPUT ON;

DECLARE
CURSOR c_emp
IS
SELECT * FROM emp;
BEGIN
FOR cur IN c_emp
LOOP
DBMS_OUTPUT.put_line (
'Employee Name: ' || cur.ename || ' Job: ' || cur.job);
END LOOP;
END;
/

Output

Employee Name: SMITH Job: CLERK
Employee Name: ALLEN Job: SALESMAN
Employee Name: WARD Job: SALESMAN
Employee Name: JONES Job: MANAGER
Employee Name: MARTIN Job: SALESMAN
Employee Name: BLAKE Job: MANAGER
Employee Name: CLARK Job: MANAGER
Employee Name: SCOTT Job: ANALYST
Employee Name: KING Job: PRESIDENT
Employee Name: TURNER Job: SALESMAN
Employee Name: ADAMS Job: CLERK
Employee Name: JAMES Job: CLERK
Employee Name: FORD Job: ANALYST
Employee Name: MILLER Job: CLERK
PL/SQL procedure successfully completed.

2. Parameterize Cursor For Loop Example

SET SERVEROUTPUT ON;

DECLARE
CURSOR c_emp (p_job emp.job%type)
IS
SELECT * FROM emp where job = p_job;
BEGIN
FOR cur IN c_emp ('MANAGER')
LOOP
DBMS_OUTPUT.put_line (
'Employee Name: ' || cur.ename || ' Job: ' || cur.job);
END LOOP;
END;
/

Output

Employee Name: JONES Job: MANAGER
Employee Name: BLAKE Job: MANAGER
Employee Name: CLARK Job: MANAGER
PL/SQL procedure successfully completed.

See also:

1. Oracle Bulk Collect Example Using Cursor
2. Keep looping in Cursor Even if an Error Occurred
3. Export Data into CSV in Oracle