What is cursor in PLSQL?
A cursor is a
temporary work area created in the system memory when a SQL statement is
executed in PLSQL block. A cursor contains information on a select statement
and the data retrieved by the select statement.
Types of Cursor:
There are 2 types of cursor.
- Implicit cursor
- Explicit cursor
Implicit cursor also called as sql cursor is a temporary work
area which is created and
Handled
automatically by the system/plsql. It can hold only one row.
We can track the information about the
execution of implicit cursors through its attributes.
There are mainly 4 cursor attributes.
·
%FOUND
If a DML Statement changed a row then the %FOUND attribute returns True
·
%ISOPEN
If a cursor is open %ISOPEN attribute returns True. But for implicit
cursor it is always false
·
%NOTFOUND
If a DML Statement is not changed any row then the %NOTFOUND attribute
returns True
·
%ROWCOUNT
It returns the number of rows affected from DML operation.
Note: Cursor has another attribute called %BULK_ROWCOUNT which is
designed for use with FORALL statement.
Explicit cursor is a temporary work area which is
created and Handled by the developer. It
can hold multiple rows.
Steps to follow to handle explicit cursor:
Step 1: Declaring a cursor
Explicit
cursor need to declare at declarative part of any pl/sql block, subprogram or
packages.
Step 2: Opening a cursor
Opening a cursor executes the statement which is in the declared cursor.
Step 3: Fetching with a cursor
After open the cursor you need to fetch the rows using LOOP.
Step 4: Closing a cursor
Once all the rows are fetched the control will come out from
the loop and you need to close the cursor.
Example:
Cursor with Parameter:
You can declare a cursor with parameter and you can use this parameter in the query.
While opening the cursor you have to pass the parameter to
the cursor.
Example:
