A cursor is a pointer to a private SQL area that stores information
about the processing of a SELECT or data manipulation language (DML) statement
(INSERT, UPDATE, DELETE, or MERGE). Cursor management of DML statements is
handled by Oracle Database, but PL/SQL offers several ways to define and
manipulate cursors to execute SELECT statements. This article focuses on the
most-common ways programmers execute SELECT statements in PL/SQL, namely
·
Using the
SELECT-INTO statement
·
Fetching from an
explicit cursor
·
Using a cursor FOR
loop (a cursor FOR loop, the body
of the loop is executed for each row returned by the query)
·
Using EXECUTE
IMMEDIATE INTO for dynamic queries
·
Using cursor
variables
Cursors are 2
types: Implicit and Explicit
Implicit Cursors :
Whenever a SQL statement is executed, Oracle
automatically allocates a memory area (known as context area) in Oracle
database PGA i.e. Process Global Area. This allocated memory space is the query
work area which holds the query related information.
All the DML statements
(INSERT, UPDATE or DELETE) and SELECT query with INTO or BULK COLLECT clauses
are candidates for implicit cursors.
For implicit cursor, the complete execution
cycle is internally handled and maintained by the oracle server. For developers,
implicit cursor appears to be an abstract concept. Only thing which is
physically available and visible to them is the cursor status flags and
information. Cursor attributes reveal the cursor related information and
status. Following are the cursor attributes available
·
SQL%ROWCOUNT –
Number of rows returned/changed in the last executed query. Applicable for
SELECT as well as DML statement
·
SQL%ISOPEN –
Boolean TRUE if the cursor is still open, else FALSE. For implicit cursor it is
FALSE only
·
SQL%FOUND –
Boolean TRUE, if the cursor fetch points to a record, else FALSE
·
SQL%NOTFOUND –
Inverse of SQL%FOUND. The flag is set as FALSE when the cursor pointer does not
point to a record in the result set.
These
attributes are set at the different stages of execution cycle and retained in
the context area.
Explicit Cursors :
These cursors are explicitly declared in the
DECLARE section of the block. They possess a specific name and a static SELECT
statement attached to them. Explicit cursors are manually executed by the
developers and follow complete execution cycle.
Explicit cursor information is also captured
in cursor attributes, which are set during the cursor processing and reveal
essential information about the cursors. These attributes, as listed below, are
same as that in implicit cursors but specific to the cursors.
·
CURSOR%ROWCOUNT
·
CURSOR%ISOPEN
·
CURSOR%FOUND
·
CURSOR%NOTFOUND
Cursor Execution
Cycle
The key steps in the cursor execution cycle
are OPEN, FETCH and CLOSE. But it would be worth expanding the complete
execution cycle for better understanding about the processing of a cursor. A
cursor execution cycle refers to the stages which a cursor follows to process
and execute the query. The phases of cursor execution are listed below.
Cursor For Loops
Dynamic Cursor For Loops
Parameterized Cursors
Using FOR UPDATE clause in cursor
Ref-Cursor
Exceptions with Cursors:
ORA-01001: invalid
cursor
ORA-06511: PL/SQL:
cursor already open
No comments:
Post a Comment