Cursor
General Attributes
Implicit Cursor
~ bY Jhanvikuvarba Solanki
Khushboo nawani
nensi Chauhan
Cursor
The Oracle Engine uses a work area for its internal processing in
order to execute an SQL Statement. This work area is private to
SQL's operations and is called a Cursor.
The data that is stored in the cursor is called the Active Data
Set.
The values retrieved from the table are held in a cursor opened
in memory on the Oracle Engine. This data is then transferred to
the client machine via the network. In order to hold this data, a
cursor is opened at the client end.
Types of Cursors
Implicit
Cursor
If the Oracle Engine opened a cursor for its
internal processing it is known as an Implicit
Cursor.
Explicit Cursor
A cursor can also be opened for processing data
through a Pl/SQL block, on demand. Such a user-
defined cursor is known as an Explicit Cursors.
General Cursor
attributes
When the Oracle engine creates an Implicit or Explicit cursor,
cursor control variables are also created to control the
execution of the cursor.
Whenever any cursor is opened and used, the Oracle engine
creates a set of four system variables which keeps track of the
‘Current’ status of a cursor.
These cursor variables can be accessed and used in a PL/SQL
code block.
Both Implicit and Explicit cursors have four attributes.
They are describes
Attribute below
Description
Name
%ISOPEN Returns TRUE if cursor is open, FALSE otherwise.
Returns TRUE if record was fetched successfully, FALSE
%FOUND otherwise
%NOTFOUN Returns TRUE if record was not fetched successfully, FALSE
D otherwise.
%ROWCOUN
Returns number of records processed from the cursor
T
Implicit
statement.
Cursor
The Oracle engine implicitly opens a cursor on the Server to process each SQL
Since the implicit cursor is opened and managed by Oracle engine
internally, it performs the following tasks:
(i) Reserves a private SQL area in memory.
(ii) Populates this area with the data requested in the SQL sentence.
(iii) Processes the data in this memory area as required.
(iv) Releases the memory area when the processing of data is complete.
The resultant data is then passed to the client machine via the network. A
cursor is then opened in memory on the client machine to hold the rows
returned by the Oracle engine.
The number of rows held in the cursor on the client is managed by the
client’s operating system and it’s swap area.
Implicit
Cursor
Implicit cursor attributes can be used to access information about the status
of last insert, update, delete or single-row select statements.
Implicit Cursor Processing in Client Server
Implicit Cursor
%ISOPEN
Attributes
The Oracle engine automatically opens
%FOUND
It evaluates to TRUE if an insert,
and closes the SQL cursor after update or delete affected one or
executing its associated select, insert, more rows or a single-row select
update or delete SQL statement in returns one or more rows. Otherwise
Implicit Cursor. SQL%ISOPEN always it evaluates to FALSE. The syntax for
evaluates to FALSE this attribute is SQL%FOUND.
%NOTFOUND %ROWCOUNT
It is logical opposite of %FOUND. It It returns the number of rows
evaluates to TRUE, if an insert,
affected by an insert, update or
update or delete affected no rows, or
delete or select into statement.
a single-row select returns no rows.
Otherwise, it evaluates to FALSE.
The syntax for accessing this
The syntax for this attribute is SQL attribute is SQL%ROWCOUNT.
%NOTFOUND
Write a PL/SQL block to display message that
whether a record is updated or not.
DECLARE
BEGIN
UPDATE emp SET salary=50000 WHERE
EMP_ID=10;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘RECORD UPDATED’);
ELSIF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(‘RECORD NOT
UPDATED’);
Example of %ROWCOUNT
DECLARE
NUM NUMBER(2);
BEGIN
UPDATE EMP SET SALARY=55000
WHERE SALARY<45000;
NUM := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(‘TOTAL ROWS AFFECTED’ ||
NUM);
END;
ThAnK YOU
FOR
WATChInG
