| Lesson 3 | Implicit cursors |
| Objective | Describe implicit Cursors |
Implicit Cursors in PL/SQL
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
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.
The following Slide Show further explains implicit cursors.
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.
The following Slide Show further explains implicit cursors.
- A SELECT statement must return exactly one record.
-
DECLARE pet_count NUMBER;
Implicit Cursor Guidelines Example
When to use Implicit cursors
An implicit cursor can be used when:
- A single record must be processed within a
SELECTstatement - Multiple records must be processed within a
DELETEor anUPDATEstatement
Implicit Cursor
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. In the next lesson, we will look at explicit cursors.
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. In the next lesson, we will look at explicit cursors.
