Job Seekers   Employers
dbapool: Authors | Submissions | Contact Us
   Forgot password? | Sign up
  Home   Discussion Forum   Articles   Interview Questions   FAQs   Scripts   Rewards   Analyzer   White Papers   Blog   Certification   Downloads   Tools
   
Cursor in Oracle




By Gayathri Sathiamoorthy
Sep 07, 2006

Digg! digg!     Print    email to friend Email to Friend

Note: This article was written for educational purpose only. Please refer to the related vendor documentation for detail.




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 through cursors.

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 handy here.

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

Explicit 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 be used.
For the ease of explanation we consider the following 3 tables of a simple loan processing system/
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)        

CUST_NBR NUMBER
CUST_NAME VARCHAR2(50)
CUST_DOB DATE
ACC_NBR NUMBER
OPEN_DT DATE

T_LOANGRANT ( The fields in pink color indicate primary key and the ones in orange color indicate foreign key)

LOAN_NBR NUMBER
DISB_DATE DATE
DISB_AMOUNT NUMBER(12,2)
CUST_NBR NUMBER
ACC_NBR NUMBER
STATUS CHAR(1) DEFAULT 'O' -- O means Open, C means Closed
OPEN_AMT NUMBER (12, 2)

T_REPAY ( The fields in pink color indicate primary key )

LOAN_NBR NUMBER
INSTLMNT_NBR NUMBER
REPAY_AMOUNT NUMBER(12,2)
REPAY_DATE DATE

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.

rec_LoanGrant t_loangrant%rowtype;
V_total_repay t_loangrant.disb_amount%type;
v_open_amt t_loangrant.open_amt%Type

BEGIN

OPEN cur_loanGrant; -- Open the cursor
Loop
Fetch cur_loanGrant into rec_loangrant; -- Get the cursor in the record variable.
Exit when cur_loanGrant%NOTFOUND; -- Exit the loop when no more rows are found.

-- Get the sum of repaid amount for each loan.
SELECT SUM(REPAY_AMOUNT) INTO V_total_repay WHERE loan_nbr = rec_loanGrant.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 = rec_loanGrant.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;
END IF;

END LOOP;

IF cur_loanGrant%ISOPEN then
CLOSE cur_loanGrant;
END IF; -- Close the cursor.

ENDUPDATE_OPEN_AMOUNT;
/
 

Implicit 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 be used.

Cursor attributes:

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

1. %FOUND
2. %ISOPEN
3. %NOTFOUND
4. %ROWCOUNT

1. %FOUND:

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.

2. %ISOPEN

This variable is set to TRUE if a cursor is opened and false when the cursor is closed.

3. %NOTFOUND

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.

4. %ROWCOUNT

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.

 

 

 
About author:

Gayathri is an Oracle certified DBA with over 6 yrs of experience. She has expertise in Oracle Database Administration, Query optimization, PL/SQL Programming, Transaction Management Mechanism, Referential Integrity, Stored Procedures, Triggers, Auditing, Locking & Concurrency and Export/Import utilities.

 




  About Us Advertise Terms of Use Privacy Newsletters Contact Us    

Home   Discussion Forum   FAQs  Articles  Jobs   Newsletters  Directory  Downloads 

Our Premium Sponsor