How to Fetch Data from Cursor in Oracle Using For Loop • Vinish.Dev

In Oracle PL/SQL, the For Loop offers one of the simplest ways to work with cursors. The key benefit when using a cursor in this format is that Oracle automatically manages the lifecycle of the cursor—that is, opening, fetching records, and closing the cursor—all handled by the PL/SQL engine behind the scenes. This means you can focus on processing each record without worrying about manually managing these cursor steps.

Let’s explore this further by examining examples that show how to fetch data from Oracle database cursors using For Loops. We'll look at two scenarios: using a cursor without any parameters, and a parameterized cursor where we control the result set.

On This Page
Show More

For Loop Cursor Examples

1. Example: For Loop Cursor Without Parameters

This example retrieves all employee records from the emp table and displays each employee's name and job title. Since the cursor here isn't parameterized, it fetches every row from the table.

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.

First, SET SERVEROUTPUT ON; enables viewing results in the console.
A cursor named c_emp is declared to select all columns from the emp table.
By looping with FOR cur IN c_emp LOOP ... END LOOP;, each row is made available in the cur variable (typed based on c_emp's record structure).
DBMS_OUTPUT.put_line is used to concatenate and display the employee's name (cur.ename) and job (cur.job).

The main advantage here is code simplicity. Even if it is your first time handling database loops, the For Loop Cursor is user-friendly and requires minimal coding.

2. Example: For Loop Cursor With Parameter

Parameterized cursors allow you to customize which data the cursor will fetch according to the parameters you provide at run time. This next example filters the employees and shows only those who have the role "MANAGER" in the organization.

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.

Here, c_emp cursor is defined with a parameter—p_job, based on the job column’s data type from the emp table.
When entering the loop, 'MANAGER' is used as an argument. Thus, only employees whose job is "MANAGER" are selected.
The rest of the logic iterates over the filtered result set, displaying just their names and positions.

This is more flexible compared to a simple, non-parameterized cursor as you can provide different values each time, dynamically changing the rows fetched by the cursor.

Key Points to Remember

When using the For Loop Cursor in Oracle PL/SQL, it's the most straightforward main mechanism for iterating recently selected datasets. You'll rarely need to write extra lines of code for opening or closing the cursor.
With parameterized cursors, you get dynamic and reusable logic which can handle various queries against the same data table, making your scripts modular and clean.

Whether you are fetching all rows or limiting your search to specific records using parameters, the For Loop Cursor can make your data processing routines clearer, safer, and more maintainable in Oracle PL/SQL programming. Experiment further by adjusting the cursor queries, changing parameters, or expanding the output!