Explicit Cursors in PL/SQL – Syntax, Attributes, and Loop Examples

Explicit cursors in PL/SQL provide precise control over query execution and result set handling, especially when dealing with multi-row SELECT statements. Unlike implicit cursors that are managed automatically by PL/SQL, explicit cursors must be explicitly declared, opened, fetched from, and closed.

What Are Explicit Cursors in PL/SQL?

Explicit cursors in PL/SQL are named memory areas that store the result set of a SELECT statement that returns multiple rows. Unlike implicit cursors (which are automatically created), explicit cursors must be declared and controlled by the developer.

Use them when:

You need row-by-row control of a query
You’re locking rows with FOR UPDATE
You want to pass parameters into the query

Syntax to Declare Explicit Cursors in PL/SQL

CURSOR cursor_name [(parameter_list)]
[RETURN return_type]
IS
SELECT_statement
[FOR UPDATE [OF column_list] [NOWAIT]];

Example – Simple Declaration:

CURSOR author_cur1 IS
SELECT rowid FROM authors WHERE id > 50;

Example – With Parameter:

CURSOR author_cur1 (i_id IN NUMBER) IS
SELECT rowid FROM authors WHERE id > i_id;

Opening the Cursor

The cursor must be opened before fetching:

OPEN author_cur1;
-- Or with parameters:
OPEN author_cur1(50);

Opening:

Parses the query
Evaluates parameters
Creates a result set in the context area

Fetching Records

FETCH cursor_name INTO variable_list | record_variable;

Examples:

FETCH author_cur1 INTO v_rowid;
FETCH author_cur INTO v_first_name, v_last_name;

-- With record variable
FETCH author_cur INTO v_author;

You can reference fields in a record like:

v_author.id

Closing the Cursor

Always close the cursor to release memory:

CLOSE cursor_name;

Failure to close a cursor causes memory leaks and potential ORA-01001: invalid cursor errors.

Cursor Attributes

Attribute	Description
`%FOUND`	TRUE if last FETCH returned a row
`%NOTFOUND`	Opposite of `%FOUND`
`%ROWCOUNT`	Number of rows fetched so far
`%ISOPEN`	TRUE if the cursor is open
`%BULK_ROWCOUNT`	Used in BULK COLLECT – rows affected
`%BULK_EXCEPTIONS`	Used in BULK COLLECT – errors info

Using Loops with Explicit Cursors in PL/SQL

1. Simple Loop

LOOP
  FETCH auth_cur INTO v_author;
  EXIT WHEN auth_cur%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(v_author.last_name);
END LOOP;

2. While Loop

FETCH auth_cur INTO v_author;
WHILE auth_cur%FOUND LOOP
  DBMS_OUTPUT.PUT_LINE(v_author.last_name);
  FETCH auth_cur INTO v_author;
END LOOP;

3. Cursor FOR Loop (Most Efficient)

FOR v_author IN auth_cur LOOP
  DBMS_OUTPUT.PUT_LINE(v_author.last_name);
END LOOP;

Full Example – Using Cursor Attributes

SET SERVEROUTPUT ON
DECLARE
  v_first_name AUTHORS.FIRST_NAME%TYPE;
  v_last_name AUTHORS.LAST_NAME%TYPE;
  v_row_count PLS_INTEGER := 0;
  v_book_count PLS_INTEGER := 0;
  CURSOR auth_cur IS
    SELECT a.first_name, a.last_name, COUNT(b.title)
    FROM authors a, books b
    WHERE a.id IN (b.author1, b.author2, b.author3)
    GROUP BY a.first_name, a.last_name
    HAVING COUNT(b.title) > 0
    ORDER BY a.last_name;
BEGIN
  OPEN auth_cur;
  LOOP
    FETCH auth_cur INTO v_first_name, v_last_name, v_book_count;
    EXIT WHEN auth_cur%NOTFOUND;
    v_row_count := auth_cur%ROWCOUNT;
    DBMS_OUTPUT.PUT_LINE(v_row_count || ' rows processed so far');
    DBMS_OUTPUT.PUT_LINE(v_last_name || ', ' || v_first_name || ' wrote ' || v_book_count || ' book(s).');
  END LOOP;
  CLOSE auth_cur;
  
  IF NOT auth_cur%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor closed');
  END IF;
END;
/

Conclusion

Explicit cursors are powerful tools in PL/SQL that give you fine-grained control over result sets. Use them when working with multi-row queries, performing row-by-row processing, or needing update locks. Always remember to close cursors and consider cursor FOR loops for cleaner syntax.

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