PL/SQL offers two types of cursors: 1) implicit and 2) explicit. In this lesson, we will look at implicit cursors.
PL/SQL declares and manages cursors implicitly every time you execute a SQL statement,
such as a SELECT or an INSERT statement that returns only a single record. Within Oracle, the most recent implicit cursor is referred to as "SQL." Oracle opens and closes an implicit cursor automatically.
You cannot open, fetch, or close an implicit cursor. However, you can use the attributes of a cursor to get information about the most recently executed implicit statement.
An implicit cursor is a session cursor that is constructed and managed by PL/SQL. PL/SQL opens an implicit cursor every time you run a SELECT or DML statement. You cannot control an implicit cursor, but you can get information from its attributes. The syntax of an implicit cursor attribute value is SQLattribute (therefore, an implicit cursor is also called a SQL cursor). SQL attribute always refers to the most recently run SELECT or DML statement. If no such statement has run, the value of SQLattribute is NULL. An implicit cursor closes after its associated statement runs; however, its attribute values remain available until another SELECT or DML statement runs. The most recently run SELECT or DML statement might be in a different scope. To save an attribute value for later use, assign it to a local variable immediately. Otherwise, other operations, such as subprogram invocations, might change the value of the attribute before you can test it.
When to use Implicit cursors
An implicit cursor can be used when:
A single record must be processed within a SELECT statement
Multiple records must be processed within a DELETE or an UPDATE statement
The following series of images further explain implicit cursors.
Implicit Cursor Guidelines
A SELECT statement must return exactly one record.
If the result set includes no record or more than one record, PL/SQL returns an error and passes, control to the exception section of the PL/SQL block.
In an implicit cursor with an INSERT, UPDATE, or DELETE statement, the result set can be more than one record.
DECLARE
pet_count NUMBER;
BEGIN
SELECT CURRENT_INVENTORY_COUNT
INTO pet_count FROM PRODUCT
WHERE PRODUCT_NAME = 'Canary';
DBMS_OUTPUT.PUT_LINE('Number of Canary's = ' || pet_count);
END;
A SELECT statement must return exactly one record.
DECLARE
pet_count NUMBER;
Implicit Cursor Attributes
The implicit cursor attributes are:
SQL%ISOPEN Attribute: Is the Cursor Open?
SQL%FOUND Attribute: Were Any Rows Affected?
SQL%NOTFOUND Attribute: Were No Rows Affected?
SQL%ROWCOUNT Attribute: How Many Rows Were Affected?
SQL%BULK_ROWCOUNT
SQL%BULK_EXCEPTIONS
Getting Number of Rows Affected by FORALL Statement
After a FORALL statement completes, you can get the number of rows that each DML statement affected from the
implicit cursor attribute SQL%BULK_ROWCOUNT. To get the total number of rows affected by the FORALL statement, use the implicit cursor attribute SQL%ROWCOUNT described in the following section.
SQL%BULK_ROWCOUNT is like an associative array whose ith element is the number of rows affected by the ith DML statement in the most recently completed FORALL statement. The data type of the element is PLS_INTEGER.
SQL%ROWCOUNT Attribute: How Many Rows Were Affected?
SQL%ROWCOUNT returns:
NULL if no SELECT or DML statement has run
Otherwise, the number of rows returned by a SELECT statement or affected by a DML statement (a PLS_INTEGER)
Note: If the number of rows exceeds the maximum value for a PLS_INTEGER, then the element has a negative value.
Cursor attributes for FORALL
You can use cursor attributes after you execute a FORALL statement to get information about the DML operation run within FORALL. Oracle also offers an additional attribute, %BULK_ROWCOUNT, to give you more granular information about the results of the bulk DML statement.
Table 6-3 describes the significance of the values returned by these attributes for FORALL.
Table 6-3. Implicit SQL cursor attributes for FORALL statements
In the next lesson, we will look at explicit cursors.
