Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 38 ยท Cursors, Procedures and Functions

Implicit and Explicit Cursors in PL/SQL

Learn how PL/SQL cursors work: the implicit SQL cursor, explicit cursors with OPEN, FETCH and CLOSE, cursor attributes, cursor FOR loops and parameters.

SQL works on sets of rows, but PL/SQL is a procedural language that likes to handle one thing at a time. A cursor is the bridge between the two: a handle on the private memory area where Oracle keeps a statement's result set, plus a pointer to the "current" row. Every SQL statement a PL/SQL block runs uses a cursor, whether you see it or not.

There are two kinds. An implicit cursor is created and managed for you for every INSERT, UPDATE, DELETE, MERGE and single-row SELECT INTO. An explicit cursor is one you declare yourself so you can walk through a multi-row query row by row. This lesson covers both, plus the cursor attributes that tell you what happened.

Oracle cannot run on this site, so the PL/SQL blocks below are read-only. The runnable examples are labelled stand-ins: a Python model of the cursor life cycle and SQLite queries that mirror the behavior.

The implicit cursor: SQL%

After any DML statement, Oracle exposes the result through the cursor named SQL. You never open or close it. Its attributes describe the most recent SQL statement:

Attribute Meaning
SQL%ROWCOUNT Number of rows affected (or fetched, for SELECT INTO)
SQL%FOUND TRUE if at least one row was affected
SQL%NOTFOUND TRUE if no row was affected
SQL%ISOPEN Always FALSE for the implicit cursor, because Oracle closes it for you
SQL
BEGIN
  UPDATE employees
     SET salary = salary * 1.05
   WHERE department_id = 50;

  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees got a raise');

  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('Department 50 has no employees');
  END IF;
END;
/

Read the attribute right after the statement. The next SQL statement, including one hidden inside a function you call, overwrites it. A single-row SELECT INTO is different: if it finds no row it raises NO_DATA_FOUND, and if it finds two or more it raises TOO_MANY_ROWS, so the attributes are of little use there. Exception handling shows how to catch these.

The SQLite demo below mirrors SQL%ROWCOUNT with changes(), which reports the rows touched by the last statement.

SQL Rows affected, like SQL%ROWCOUNT

Press Run (or Ctrl + Enter) to see the result here.

Explicit cursors: four steps

When a query returns many rows, you declare an explicit cursor and drive it yourself:

  1. DECLARE the cursor with its query.
  2. OPEN it. Oracle runs the query and positions before the first row.
  3. FETCH a row into variables, repeatedly.
  4. CLOSE it to free the memory.
SQL
DECLARE
  CURSOR c_staff IS
    SELECT employee_id, first_name, salary
      FROM employees
     WHERE department_id = 50
     ORDER BY salary DESC;

  v_id     employees.employee_id%TYPE;
  v_name   employees.first_name%TYPE;
  v_salary employees.salary%TYPE;
BEGIN
  OPEN c_staff;
  LOOP
    FETCH c_staff INTO v_id, v_name, v_salary;
    EXIT WHEN c_staff%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_salary);
  END LOOP;
  CLOSE c_staff;
END;
/

The EXIT WHEN c_staff%NOTFOUND line must come immediately after the FETCH. Put it at the end of the loop body and the last row is processed twice or an empty row slips through. Explicit cursors have their own attributes: %FOUND, %NOTFOUND, %ISOPEN and %ROWCOUNT (rows fetched so far). Forgetting CLOSE leaks cursors, and eventually you hit ORA-01000: maximum open cursors exceeded.

The Python model below is a stand-in, not Oracle: it reproduces the life cycle and attributes so you can see which call changes which state.

Python Cursor life cycle (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The cursor FOR loop

The four steps are so common that PL/SQL has a shortcut. A cursor FOR loop opens, fetches, tests and closes for you, and declares the record variable itself:

SQL
BEGIN
  FOR r IN (SELECT first_name, salary FROM employees WHERE department_id = 50) LOOP
    DBMS_OUTPUT.PUT_LINE(r.first_name || ' earns ' || r.salary);
  END LOOP;
END;
/

Prefer it whenever you just need to process every row. You cannot forget CLOSE, there is no NOTFOUND check to misplace, and with the default optimization level the compiler fetches rows in batches of 100 for you (see bulk processing). Use the manual style only when you need to stop partway, keep the cursor open across calls, or pass it around.

Cursors with parameters

A cursor can take parameters so one declaration serves many calls:

SQL
DECLARE
  CURSOR c_dept (p_dept NUMBER, p_min NUMBER DEFAULT 0) IS
    SELECT first_name, salary
      FROM employees
     WHERE department_id = p_dept AND salary >= p_min;
BEGIN
  FOR r IN c_dept(50, 4000) LOOP
    DBMS_OUTPUT.PUT_LINE(r.first_name);
  END LOOP;
END;
/

Parameters are evaluated when the cursor is opened and are read-only inside it. They are treated as bind variables, so Oracle reuses the parsed statement instead of re-parsing for each department.

Locking rows: FOR UPDATE and WHERE CURRENT OF

If you intend to change the rows you read, lock them first so nobody else changes them under you:

SQL
DECLARE
  CURSOR c_raise IS
    SELECT employee_id, salary FROM employees
     WHERE department_id = 50
       FOR UPDATE OF salary NOWAIT;
BEGIN
  FOR r IN c_raise LOOP
    UPDATE employees SET salary = r.salary * 1.1
     WHERE CURRENT OF c_raise;
  END LOOP;
  COMMIT;
END;
/

NOWAIT fails immediately with ORA-00054 if another session holds the lock, while WAIT 5 waits up to five seconds. WHERE CURRENT OF updates exactly the row the cursor just fetched. Note the COMMIT at the end, after the loop: committing inside a FOR UPDATE loop releases the locks and invalidates the cursor (ORA-01002: fetch out of sequence). See transactions.

Common mistakes

  • Reading SQL%ROWCOUNT after another statement has run.
  • Testing %NOTFOUND after the loop body instead of right after FETCH.
  • Opening a cursor twice (ORA-06511) or fetching from a closed one (ORA-01001).
  • Using SELECT INTO for a query that may return several rows. Use a cursor instead.
  • Looping row by row with a DML statement per row when one set-based SQL statement would do. It is almost always faster to let SQL do the whole job.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which attribute tells you how many rows an UPDATE just changed?

    SQL%ROWCOUNT holds the number of rows affected by the most recent statement. SQL%FOUND only says whether it was at least one.
  2. 2. Where must EXIT WHEN c%NOTFOUND appear in a manual fetch loop?

    NOTFOUND is only meaningful for the fetch you just made. Testing it right away avoids processing the last row twice.
  3. 3. What does a cursor FOR loop do for you?

    The loop manages the whole life cycle and implicitly declares the record variable.
  4. 4. What is SQL%ISOPEN for the implicit cursor?

    Oracle opens and closes the implicit cursor itself, so by the time your code looks, it is closed.

Summary

  • A cursor is a handle on a statement's result set. DML and SELECT INTO use the implicit cursor SQL; multi-row queries use explicit cursors.
  • Explicit cursors are declared, opened, fetched and closed; %FOUND, %NOTFOUND, %ISOPEN and %ROWCOUNT describe their state.
  • A cursor FOR loop is the shortest and safest way to process all rows.
  • Cursor parameters make one query reusable; FOR UPDATE with WHERE CURRENT OF locks and updates the current row.

Next: REF Cursors and Bulk Processing