Oracle PL/SQL for DBAs

Implicit Cursors

PL/SQL declares and manages an implicit cursor every time you execute a SQL DML statement (INSERT, UPDATE, or DELETE) or a SELECT INTO that returns a single row from the database directly into a PL/SQL data structure. This kind of cursor is called implicit because Oracle implicitly or automatically handles many of the cursor-related operations for you, such as allocating a cursor, opening the cursor, fetching, and so on.

An implicit query is a SELECT statement that has the following special characteristics:

• The SELECT statement appears in the executable section of your block; it is not defined in the declaration section, as explicit cursors are.

• The query contains an INTO clause (or BULK COLLECT INTO for bulk processing). The INTO clause is a part of the PL/SQL (not the SQL) language and is the mechanism used to transfer data from the database into local PL/SQL data structures.

• You do not open, fetch, or close the SELECT statement; all of these operations are done for you.

The general structure of an implicit query is as follows:

    SELECT column_list
      [BULK COLLECT] INTO PL/SQL variable list...rest of SELECT statement...

If you use an implicit cursor, Oracle performs the open, fetches, and close for you automatically; these actions are outside your programmatic control. You can, however, obtain information about the most recently executed SQL statement by examining the values in the implicit SQL cursor attributes, as explained later in "Implicit SQL cursor attributes