Oracle PL/SQL: implicit vs explicit cursors
PL/SQL issues an implicit cursor whenever you execute a SQL statement directly in your code, as long as that code does not employ an explicit cursor. It is called an “implicit” cursor because you do not explicitly declare a cursor for the SQL statement.
If you use an implicit cursor, Oracle performs the open, fetches, and close for you automatically; these actions are outside of 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 this chapter.
Implicit cursor is simpler to code, but suffers from:
- inefficiency (the ANSI standard specifies that it must fetch twice to check if there is more than one
- vulnerability to data errors (if you ever get two rows, it raises a TOO_MANY_ROWS exception)
Example of implicit cursor:
SELECT col INTO var FROM table WHERE ….. ;
An explicit cursor is a SELECT statement that is explicitly defined in the declaration section of your code and, in the process, assigned a name. There is no such thing as an explicit cursor for UPDATE, DELETE, and INSERT statements.
With explicit cursors, you have complete control over how to access information in the database. You decide when to OPEN the cursor, when to FETCH records from the cursor (and therefore from the table or tables in the SELECT statement of the cursor) how many records to fetch, and when to CLOSE the cursor.
Information about the current state of your cursor is available through examination of the cursor attributes.
This granularity of control makes the explicit cursor an invaluable tool for your development effort.
CURSOR cur IS SELECT col FROM table WHERE … ;
FETCH cur INTO var;