Implicit Cursors in PL/SQL – Syntax, Attributes, and Example

Implicit Cursors in PL/SQL are automatically created by Oracle whenever you execute a DML or single-row SELECT INTO statement. Unlike explicit cursors, they require no manual handling, making them perfect for straightforward operations.

What Are Implicit Cursors in PL/SQL?

When Oracle executes a SQL statement, it uses an implicit cursor to manage the context area and result. You don’t need to declare, open, fetch, or close it.

Instead, Oracle handles the entire lifecycle, and you can access the outcome using cursor attributes like SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, and SQL%ISOPEN.

Cursor Attributes for Implicit Cursors in PL/SQL

Attribute	Description
`SQL%FOUND`	TRUE if the SQL affected at least one row
`SQL%NOTFOUND`	TRUE if no rows were affected
`SQL%ROWCOUNT`	Returns how many rows were affected
`SQL%ISOPEN`	Always FALSE for implicit cursors
`%BULK_ROWCOUNT`	Used only with `BULK COLLECT`
`%BULK_EXCEPTIONS`	Used for `FORALL` error tracking

Example Using Implicit Cursors in PL/SQL

SET SERVEROUTPUT ON
BEGIN
  UPDATE books
  SET price = price * 0.90
  WHERE isbn = '78824389';

  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated.');

  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No update performed.');
  END IF;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

In this example:

Oracle internally creates an implicit cursor.
SQL%ROWCOUNT displays affected rows.
SQL%NOTFOUND checks if the update had no effect.

Implicit Cursors vs. Explicit Cursors in PL/SQL

Feature	Implicit Cursor	Explicit Cursor
Manual Control	❌ Not required	✅ Required
Declaration	❌ Automatic	✅ Manual using `CURSOR`
Best For	Simple DML, `SELECT INTO`	Multi-row SELECT operations
Row-by-Row Fetching	❌ Not possible	✅ Supported
Locking/Params	❌ Not available	✅ Supported with `FOR UPDATE` and params

Read more about Explicit Cursors in PL/SQL

When to Use Implicit Cursors in PL/SQL

Use implicit cursors when:

You’re executing DML like INSERT, UPDATE, or DELETE
Using SELECT INTO for single-row queries
You don’t need to loop through multiple rows
You want Oracle to manage cursor behavior automatically

Summary

Implicit Cursors in PL/SQL simplify the execution of single-row and DML operations by automating cursor handling. They’re best suited for updates, inserts, deletes, and SELECT INTO statements where you don’t need to manually loop through results.

By using attributes like SQL%ROWCOUNT and SQL%FOUND, you still get feedback on what happened—without the overhead of managing the cursor lifecycle manually.

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