Cursors
In PL/SQL, a cursor is a pointer to a private memory area (context area) used by the Oracle engine to process and store the result of an SQL statement.
Types of Cursors
Cursors are classified based on how they are created and managed:
1. Implicit Cursors
These are automatically created and managed by Oracle whenever a DML statement (
INSERT, UPDATE, DELETE) or a SELECT INTO statement is executed without an explicit cursor being defined.- Automatic Management: Oracle handles the entire lifecycle (opening, fetching, and closing).
- Single-Row Limit: Primarily used for queries that return exactly one row.
- Attributes: Accessed using the
SQL%prefix (e.g.,SQL%FOUND,SQL%ROWCOUNT).
2. Explicit Cursors
These are user-defined cursors declared by the programmer to gain finer control over the context area, especially for queries that return multiple rows.
- Manual Control: The programmer must explicitly
DECLARE,OPEN,FETCH, andCLOSEthe cursor. - Multi-Row Processing: Capable of processing multiple records sequentially through a loop.
- Attributes: Accessed using the cursor’s name (e.g.,
cursor_name%FOUND).
Comparison of Cursor Type
| Feature | Implicit Cursor | Explicit Cursor |
|---|---|---|
| Creation | Automatically by the database | Manually by the programmer |
| Declaration | No declaration needed | Declared in the DECLARE section |
| Lifecycle | Auto-opened and auto-closed | Manually opened, fetched, and closed |
| Use Case | Single-row SELECT or DML | Multi-row SELECT statements |
| Efficiency | Faster for single-row operations | Better for complex data sets |