O'Reilly logo

Oracle PL/SQL Programming, Third Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Working with Implicit Cursors

PL/SQL declares and manages animplicit 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 allocation of a cursor, opening the cursor, fetching, and so on.

Tip

The implicit DML statements are covered in Chapter 13. This chapter is concerned only with the implicit SQL query.

An implicit cursor 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required