PL/SQL Cursors – Types, Syntax, and Real Examples

PL/SQL Cursors allow developers to process query results row by row. When working with SQL inside PL/SQL, a cursor helps manage and access query results efficiently. They point to a context area in memory where the SQL statement is processed.

What is a Cursor in PL/SQL?

A cursor in PL/SQL acts like a pointer to the result set of a query. When a cursor is opened, Oracle executes the SQL and stores the result in the Process Global Area (PGA). This ensures data consistency throughout the transaction.

Just like a city map zooms into relevant streets rather than showing the whole country, a cursor narrows down what data the program needs to process.

Types of Cursors in PL/SQL

1. Explicit Cursors in PL/SQL

You define and control these cursors explicitly using DECLARE, OPEN, FETCH, and CLOSE.

DECLARE
  CURSOR cur_authors IS SELECT first_name FROM authors;
  v_name authors.first_name%TYPE;
BEGIN
  OPEN cur_authors;
  LOOP
    FETCH cur_authors INTO v_name;
    EXIT WHEN cur_authors%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP;
  CLOSE cur_authors;
END;
/

2. Implicit Cursors

These are automatically created by Oracle when a DML or SELECT INTO operation is executed. Use %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN attributes.

DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM authors;
  DBMS_OUTPUT.PUT_LINE('Total Authors: ' || v_count);
END;
/

3. Cursor Variables (REF CURSOR)

A REF CURSOR can point to different queries dynamically.

DECLARE
  TYPE author_cursor IS REF CURSOR;
  cv_author author_cursor;
  v_name authors.first_name%TYPE;
BEGIN
  OPEN cv_author FOR SELECT first_name FROM authors;
  LOOP
    FETCH cv_author INTO v_name;
    EXIT WHEN cv_author%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP;
  CLOSE cv_author;
END;
/

4. Nested Cursor Expressions (Cursor Subqueries)

Useful for joining multiple row sets within a SQL query.

SELECT department_name,
       CURSOR(SELECT employee_name FROM employees WHERE dept_id = d.dept_id)
FROM departments d;

How PL/SQL Cursors Work – Context Area

When a cursor is opened, Oracle does the following:

Component	Description
Context Area	Memory for query info like rows, parsed SQL, counters
Pointer	Cursor points to the memory, not the actual table
Snapshot	Data at open time is static—no changes reflect later

Real-Life Cursor Behavior Example

DECLARE
  CURSOR cur1 IS SELECT rowid FROM authors WHERE id > 50;
  CURSOR cur2 IS SELECT rowid FROM authors WHERE id > 50;
  v_rowid ROWID;
BEGIN
  OPEN cur1;
  DELETE FROM authors WHERE id > 50;
  OPEN cur2;

  FETCH cur1 INTO v_rowid;
  IF cur1%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Cursor 1 includes the deleted rows');
  END IF;

  FETCH cur2 INTO v_rowid;
  IF cur2%ROWCOUNT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Cursor 2 does not include the deleted rows');
  END IF;

  CLOSE cur1;
  CLOSE cur2;
  ROLLBACK;
END;
/

Output:

Cursor 1 includes the deleted rows  
Cursor 2 does not include the deleted rows

Cursor Attributes

Attribute	Description
`%FOUND`	TRUE if the last fetch returned a row
`%NOTFOUND`	TRUE if the last fetch failed
`%ROWCOUNT`	Number of rows fetched so far
`%ISOPEN`	TRUE if cursor is open

OPEN_CURSORS Parameter

Oracle limits how many cursors can be open at once via the OPEN_CURSORS initialization parameter. Monitor and configure it in large systems to prevent memory issues.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.