Oracle PL/SQL Programming, 5th Edition

Working with Implicit Cursors

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

Note

The implicit DML statements are covered in Chapter 14, DML and Transaction Management. This chapter is concerned only with the implicit SQL query.

An implicit cursor is a SELECT statement that has these 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, the database performs the open, fetches, and close for you automatically; these actions are outside your programmatic control. You can, however, obtain information ...