Cursor in Oracle
It is very important for a programmer/developer to understand what cursor is.
The article below explain cursor with example.
What is a Cursor.
When a query is executed in oracle, a result set is produced and stored in the
memory. Oracle allows the programmer to access this result set in the memory
Why use a cursor?
Many times, when a query returns more than one row as a result, we might want to
go through each row and process the data in different way for them. Cursor is
Types of cursors:
Oracle PL/SQL declares a cursor implicitly for all queries and DML statements
(including queries that return only one row). But in most of the cases we donít
use these cursors for queries that return one row. Based on this, there are 2
types of cursors
1. Explicit cursor
2. Implicit cursor
The programmer declares a cursor explicitly to process the result set of query
(Mostly the query that returns multiple rows as result). The following are the
steps to use an explicit cursor.
a. Declare the cursor -- This means give the cursor a name and
associate the query that is going to return multiple rows.
b. Open the cursor -- execute the query
c. Fetch the cursor -- Get the result set and loop through to process them
d. Close the cursor -- Close cursor processing.
The following sample program gives a description of how an explicit cursor can
For the ease of explanation we consider the following 3 tables of a simple loan
The table T_CUSTOMER lists the attributes of a customer.
The table T_LOANGRANT lists the attributes of disbursement of a loan
The table T_REPAY lists the attributes of repayment of the loan.
T_CUSTOMER ( The fields in pink color indicate primary key)
T_LOANGRANT ( The fields in pink color indicate primary key and the ones
in orange color indicate foreign key)
||CHAR(1) DEFAULT 'O' -- O means Open, C means Closed
||NUMBER (12, 2)
T_REPAY ( The fields in pink color indicate primary key )
Implementing a procedure which requires to loop through all the loans, find
out the sum of amount repaid for these loans and update the Open_amount Field in
the T_LOANGRANT Table. Below is an example of explicit cursors.
|CREATE OR REPLACE PROCEDURE UPDATE_OPEN_AMOUNT IS
CURSOR cur_loanGrant IS
SELECT * FROM T_LOANGRANT where status = 'O'; -- Declaring the cursor and
associating the query that returns more than one row.
OPEN cur_loanGrant; -- Open the cursor
Fetch cur_loanGrant into rec_loangrant; -- Get the cursor in the record
Exit when cur_loanGrant%NOTFOUND; -- Exit the loop when no more rows are
-- Get the sum of repaid amount for each loan.
SELECT SUM(REPAY_AMOUNT) INTO V_total_repay WHERE loan_nbr =
-- Calculate Open amount:
v_open_amt := rec_loangrant.disb_amount - v_total_repay;
-- update the t_loangrant.open_Amt
UPDATE T_LOANGRANT SET OPEN_AMT = v_open_amt WHERE LOAN_NBR =
-- If The loan is fully closed then set the Status to 'C'
IF v_open_amt = 0 then
UPDATE T_LOANGRANT SET STATUS = 'C' WHERE LOAN_NBR = rec_loanGrant.loan_nbr;
IF cur_loanGrant%ISOPEN then
END IF; -- Close the cursor.
Oracle implicitly opens a cursor to process each sql statement that is not
associated with an explicit cursor. An implicit cursor is opened for all queries
(even if it returns only one row as the result set).The most recent implicit
cursor (that is, the cursor for the most recent query ) can be referred to with
SQL cursor. Unlike explicit cursors, there is no necessity to use the OPEN,
FETCH and CLOSE statements with these cursors. Instead cursor attributes could
Cursor attributes are variables that take some value about the status of the
cursor. These values are automatically set by Oracle and the programmer can read
them not write values for them. There are four cursor attributes. They are
After a cursor is opened before the first fetch, the value of this variable is
null. After the first fetch, if the query returns one or more rows as result
set, this variable is set to TRUE. When a fetch is made after the last row of
the result set is reached, this variable is set to FALSE.
This variable is extensively used to in stored procedures to handle exceptions
when a query returns no data set. If this variable is referenced before the
cursor is opened, an exception INVALID_CURSOR is raised.
This variable is set to TRUE if a cursor is opened and false when the cursor is
This variable is a logical opposite of %FOUND. This variable is set to TRUE if
the last fetch returns no rows an FALSE when the last fetch returns a row. This
can also be used in exception handing when a query returns no rows.
This variable acts like a counter. It is set to zero when a cursor is opened.
Thereafter, with each fetch, the value of this variable is incremented by 1 if
the fetch returns a row. This variable is handy when processing needs to be done
for only a few rows of the result set.