PL/SQL Cursors Explained: Implicit, Explicit, and Cursor FOR Loops

If you write PL/SQL, you write cursors — whether you realize it or not. Every SQL statement executed inside a PL/SQL block uses a cursor. Understanding how cursors work, when to use each type, and how to handle them properly is one of the fundamental skills that separates junior Oracle developers from senior ones.

In this guide, we cover implicit cursors, explicit cursors, cursor FOR loops, cursor variables (REF CURSORs), and the cursor attributes that give you visibility into query execution.

What Is a Cursor?

A cursor is a pointer to the result set of a SQL query. When Oracle executes a SELECT statement, it creates a private memory area called a cursor context area to hold the query results and execution state. A cursor is your handle to that area.

Cursors allow you to:

• Retrieve query results row by row
• Process each row with custom PL/SQL logic
• Control when rows are fetched and when the cursor is closed
• Return multiple result sets from stored procedures

Implicit Cursors

Every DML statement (INSERT, UPDATE, DELETE) and every single-row SELECT INTO statement in PL/SQL uses an implicit cursor — one that Oracle creates and manages automatically without any code from you.

BEGIN
  -- Implicit cursor: Oracle creates this automatically
  UPDATE employees
  SET    salary = salary * 1.10
  WHERE  department_id = 10;
  
  -- SQL%ROWCOUNT tells us how many rows were affected
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees updated.');
END;

The implicit cursor is referenced through the built-in SQL cursor name. Its attributes are:

Attribute	What It Returns
SQL%FOUND	TRUE if the last DML affected at least one row
SQL%NOTFOUND	TRUE if the last DML affected zero rows
SQL%ROWCOUNT	Number of rows affected by the last DML
SQL%ISOPEN	Always FALSE for implicit cursors

BEGIN
  DELETE FROM old_log_entries WHERE log_date < SYSDATE - 90;
  
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No old log entries to delete.');
  ELSE
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' log entries deleted.');
  END IF;
END;

SELECT INTO: Single-Row Implicit Cursor

The SELECT INTO statement uses an implicit cursor to fetch exactly one row. If zero rows or more than one row is returned, Oracle raises an exception:

DECLARE
  v_salary   employees.salary%TYPE;
  v_emp_name VARCHAR2(100);
BEGIN
  SELECT first_name || ' ' || last_name, salary
  INTO   v_emp_name, v_salary
  FROM   employees
  WHERE  employee_id = 101;
  
  DBMS_OUTPUT.PUT_LINE(v_emp_name || ' earns ' || v_salary);
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found.');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Query returned multiple rows — use an explicit cursor.');
END;

Explicit Cursors

When you need to process multiple rows returned by a query, you need an explicit cursor. You declare it, open it, fetch rows from it one at a time, and close it when done.

The lifecycle has four steps:

1. DECLARE — Define the cursor with its SQL query
2. OPEN — Execute the query and populate the result set
3. FETCH — Retrieve one row into PL/SQL variables
4. CLOSE — Release the cursor resources

DECLARE
  -- Step 1: Declare the cursor
  CURSOR emp_cur IS
    SELECT employee_id, first_name, last_name, salary
    FROM   employees
    WHERE  department_id = 20
    ORDER BY last_name;
  
  -- Variables to hold the fetched row
  v_emp_id   employees.employee_id%TYPE;
  v_fname    employees.first_name%TYPE;
  v_lname    employees.last_name%TYPE;
  v_salary   employees.salary%TYPE;

BEGIN
  -- Step 2: Open the cursor
  OPEN emp_cur;
  
  LOOP
    -- Step 3: Fetch one row
    FETCH emp_cur INTO v_emp_id, v_fname, v_lname, v_salary;
    
    -- Exit when no more rows
    EXIT WHEN emp_cur%NOTFOUND;
    
    -- Process the fetched row
    DBMS_OUTPUT.PUT_LINE(v_lname || ', ' || v_fname || ' — Salary: ' || v_salary);
  END LOOP;
  
  -- Step 4: Close the cursor
  CLOSE emp_cur;
END;

Explicit Cursor Attributes

Attribute	What It Returns
cursor%FOUND	TRUE if the last FETCH retrieved a row
cursor%NOTFOUND	TRUE if the last FETCH returned no row
cursor%ROWCOUNT	Number of rows fetched so far
cursor%ISOPEN	TRUE if the cursor is currently open

Cursor FOR Loops — The Cleaner Way

The Cursor FOR Loop is Oracle's elegant shorthand for the explicit cursor pattern. It automatically opens the cursor, fetches rows one at a time into a record variable, and closes the cursor when done. You write significantly less code:

BEGIN
  -- Cursor FOR loop: OPEN, FETCH, and CLOSE are implicit
  FOR emp_rec IN (
    SELECT employee_id, first_name, last_name, salary
    FROM   employees
    WHERE  department_id = 20
    ORDER BY last_name
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(
      emp_rec.last_name || ', ' || emp_rec.first_name || 
      ' — Salary: ' || emp_rec.salary
    );
  END LOOP;
END;

Notice: no DECLARE section for the cursor, no OPEN, FETCH, or CLOSE calls, and no %NOTFOUND check. The loop terminates automatically when there are no more rows.

You can also reference a named cursor in a FOR loop:

DECLARE
  CURSOR dept_cur IS
    SELECT department_id, department_name
    FROM   departments
    ORDER BY department_name;
BEGIN
  FOR dept_rec IN dept_cur LOOP
    DBMS_OUTPUT.PUT_LINE(dept_rec.department_name);
  END LOOP;
END;

Parameterized Cursors

You can pass parameters to cursors, making them reusable for different filter values:

DECLARE
  CURSOR emp_by_dept (p_dept_id NUMBER) IS
    SELECT employee_id, first_name, last_name
    FROM   employees
    WHERE  department_id = p_dept_id;
BEGIN
  -- Use same cursor for different departments
  FOR emp_rec IN emp_by_dept(10) LOOP
    DBMS_OUTPUT.PUT_LINE('Dept 10: ' || emp_rec.last_name);
  END LOOP;
  
  FOR emp_rec IN emp_by_dept(20) LOOP
    DBMS_OUTPUT.PUT_LINE('Dept 20: ' || emp_rec.last_name);
  END LOOP;
END;

REF CURSORs: Dynamic and Returnable Cursors

A REF CURSOR (cursor variable) is a pointer to a cursor that can be assigned at runtime and passed between program units. This is how PL/SQL stored procedures return result sets to calling applications.

-- Procedure that returns a result set via REF CURSOR
CREATE OR REPLACE PROCEDURE get_department_employees (
  p_dept_id IN  NUMBER,
  p_result  OUT SYS_REFCURSOR
) IS
BEGIN
  OPEN p_result FOR
    SELECT employee_id, first_name, last_name, salary
    FROM   employees
    WHERE  department_id = p_dept_id
    ORDER BY last_name;
END;

The calling application (Java, Python, APEX, etc.) receives the open cursor and fetches rows from it. This is the standard way Oracle stored procedures expose query results to external systems.

Cursor Best Practices

• Use Cursor FOR Loops for most situations — They are cleaner, less error-prone, and Oracle optimizes them well
• Always close explicit cursors — Unclosed cursors hold database resources. Use exception handlers to ensure cursors are closed even on errors
• Avoid row-by-row processing for large sets — If you are processing thousands of rows, BULK COLLECT with FORALL is dramatically faster
• Use %ROWTYPE for record variables — This makes your code resilient to table column changes
• Do not SELECT * in cursors — Always explicitly list the columns you need

When to Use Each Type

Scenario	Best Cursor Type
DML operations (update/delete)	Implicit cursor (SQL%)
Single-row lookup	SELECT INTO (implicit)
Process multiple rows with clean code	Cursor FOR Loop
Complex multi-pass or conditional fetch logic	Explicit cursor
Return results from a procedure/function	REF CURSOR (SYS_REFCURSOR)
Bulk data processing for performance	BULK COLLECT + FORALL

Conclusion

Cursors are the foundation of row-by-row data processing in PL/SQL. Most of the time, a Cursor FOR Loop gives you everything you need in the cleanest possible form. Explicit cursors are there when you need more control, and REF CURSORs are your bridge between PL/SQL and the applications that consume database results.

Master cursors, and you will handle virtually any data retrieval and processing challenge Oracle throws at you.