Exforsys.com
 
Home Tutorials Oracle 9i
 

Tutorial 10: Oracle 9i : Cursors

 
In this week tutorial 10 as part of Oracle 9i SQL and PL/SQL training explains about the Introduction to Cursors, different types and the Cursors and Loops with sample code and screen sh

 

Sponsored Links

 

CURSOR


For every SQL statement execution certain area in memory is allocated. PL/SQL allow you to name this area. This private SQL area is called context area or cursor. A cursor acts as a handle or pointer into the context area. A PL/SQL program controls the context area using the cursor. Cursor represents a structure in memory and is different from cursor variable.

When you declare a cursor, you get a pointer variable, which does not point any thing. When the cursor is opened, memory is allocated and the cursor structure is created. The cursor variable now points the cursor. When the cursor is closed the memory allocated for the cursor is released.

Cursors allow the programmer to retrieve data from a table and perform actions on that data one row at a time. There are two types of cursors implicit cursors and explicit cursors.


Implicit cursors


For SQL queries returning single row PL/SQL declares implicit cursors. Implicit cursors are simple SELECT statements and are written in the BEGIN block (executable section) of the PL/SQL. Implicit cursors are easy to code, and they retrieve exactly one row. PL/SQL implicitly declares cursors for all DML statements. The most commonly raised exceptions here are NO_DATA_FOUND or TOO_MANY_ROWS.
Syntax:

SELECT ename, sal INTO ena, esa FROM EMP WHERE EMPNO = 7844;

Note: Ename and sal are columns of the table EMP and ena and esa are the variables
used to store ename and sal fetched by the query.



Explicit Cursors


Explicit cursors are used in queries that return multiple rows. The set of rows fetched by a query is called active set. The size of the active set meets the search criteria in the select statement. Explicit cursor is declared in the DECLARE section of PL/SQL program.

Syntax:


CURSOR <cursor-name> IS <select statement>


Sample Code:


DECLARE
CURSOR emp_cur IS SELECT ename FROM EMP;
BEGIN
----
---
END;


Processing multiple rows is similar to file processing. For processing a file you need to open it, process records and then close. Similarly user-defined explicit cursor needs to be opened, before reading the rows, after which it is closed. Like how file pointer marks current position in file processing, cursor marks the current position in the active set.


Opening Cursor


Syntax: OPEN <cursor-name>;


Example : OPEN emp_cur;



When a cursor is opened the active set is determined, the rows satisfying the where clause in the select statement are added to the active set. A pointer is established and points to the first row in the active set.

Fetching from the cursor: To get the next row from the cursor we need to use fetch statement.



Syntax:  FETCH <cursor-name> INTO <variables>;


Example: FETCH emp_cur INTO ena;


FETCH statement retrieves one row at a time. Bulk collect clause need to be used to fetch more than one row at a time.

Closing the cursor: After retrieving all the rows from active set the cursor should be closed. Resources allocated for the cursor are now freed. Once the cursor is closed the execution of fetch statement will lead to errors.


CLOSE <cursor-name>;


Explicit Cursor Attributes


 Every cursor defined by the user has 4 attributes. When appended to the cursor name these attributes let the user access useful information about the execution of a multirow query.


The attributes are:


  1. %NOTFOUND: It is a Boolean attribute, which evaluates to true, if the last fetch failed. i.e. when there are no rows left in the cursor to fetch. 
  2. %FOUND: Boolean variable, which evaluates to true if the last fetch, succeeded. 
  3.  %ROWCOUNT: It’s a numeric attribute, which returns number of rows fetched by the cursor so far. 
  4. %ISOPEN: A Boolean variable, which evaluates to true if the cursor is opened otherwise to false.


In above example I wrote a separate fetch for each row, instead loop statement could be used here. Following example explains the usage of LOOP.


Using WHILE:



While LOOP can be used as shown in the following example for accessing the cursor values.


Example:




Fetch is used twice in the above example to make %FOUND available. See below example.


 

Sponsored Links

 

Using Cursor For Loop:


The cursor for Loop can be used to process multiple records. There are two benefits with cursor for Loop

1. It implicitly declares a %ROWTYPE variable, also uses it as LOOP index
2. Cursor For Loop itself opens a cursor, read records then closes the cursor automatically. Hence OPEN, FETCH and CLOSE statements are not necessary in it.

Example:


emp_rec is automatically created variable of %ROWTYPE. We have not used OPEN, FETCH , and CLOSE in the above example as for cursor loop does it automatically. The above example can be rewritten as shown in the Fig , with less lines of code. It is called Implicit for Loop.



Deletion or Updation Using Cursor:



In all the previous examples I explained about how to retrieve data using cursors. Now we will see how to modify or delete rows in a table using cursors. In order to Update or Delete rows, the cursor must be defined with the FOR UPDATE clause. The Update or Delete statement must be declared with WHERE CURRENT OF

Following example updates comm of all employees with salary less than 2000 by adding 100 to existing comm. 




 








Read Next: Oracle 9i:Download example SQL Scripts used in Tutorials



 

 

Comments


rkphalke said:

  Notes on Cursors.
August 10, 2005, 4:21 am

vishal mehta said:

  The notes are quite good :D
July 18, 2006, 4:38 am

mca_banti said:

  really it\'s very nice
September 4, 2006, 5:06 am

Mihir Kumar said:

  :).it will be the best if you have also mentioned the internal diffrence between implicit and explicit cursor .
September 20, 2006, 12:09 am

anonymous said:

  Nice Work ....... Short and simple
October 3, 2006, 3:48 am

mukka ramesh said:

  it helped me a lot.i did it without listening to classes
October 10, 2006, 5:28 am

ekiran said:

  its very good explanation. and provide real time scenarios then its look very good.
November 15, 2006, 4:01 am

spriya said:

  The way it was explained is really good.
Thank u
November 22, 2006, 4:49 am

vjaiswal said:

  very nice explanation is given
December 19, 2006, 11:29 pm

sunil_hirlekar said:

  its very good explanation. and provide real time scenarios then its look very good.
December 22, 2006, 1:36 am

nitin agarwal said:

  this is exactly what i needed....everthing is mentioned in its completeness and is to the point.

thank a ton
December 22, 2006, 11:45 am

HABIB_UR_REHMAN said:

  Very Nice.

It Helps alot to the New Comer's. I hope and Prayer this kind of this will be going on from your kind side. I suggest you to go forward with the new & advance features of 9i as well as 10 g technology.

Have a nice Day.
January 9, 2007, 12:56 am

Ajay mishra said:

  very nice .
January 14, 2007, 5:29 am

Sunil Anant Hirlekar said:

  It helps a lot for beginner like me. Thanks
February 3, 2007, 12:50 am

dinesh jain said:

  really u have explained complex things in a very simple manner
February 27, 2007, 2:01 am

SANGRAM KESHARI MOHANTY said:

  Thanks, Good explanatoon it really helps us.
March 31, 2007, 8:34 am

john smith said:

  any example for ref cursor
;D
April 3, 2007, 5:41 am

Hardik Vyas said:

  Its excellent . Gr8 job man. U can easily understand by reading this topics coz its written in simple language and most of all its with examples.
Thanks man. Carry on the good work
April 11, 2007, 8:06 pm

Hari Kancharla said:

  Its good example for learners.It is very clear to understand.
April 12, 2007, 9:09 am

Deeptichowdary said:

  it's very very nice , it's a greate site . it looks like user friendly. easy to understand without taking the course and joing to institution. The examples are given here is very good to gain knowledge on particular topic.
April 17, 2007, 4:54 pm

Imtiaz Ali said:

  Awsum, Excellent it really helps me Keep it up.
April 17, 2007, 10:47 pm

Vitor Galveia said:

  Hi everyone,
This is an example of a great site. unfortunately, when we want to know more about some topic this informations is not enough. I have one doubt about who to know the number of rows of a cursor, for example, if we have the following example:

open c_myCursor for
select p.myName
from partners p

how can I know the number of rows afected to this cursor after this? Can anyone help me?

Thansk.

April 20, 2007, 4:11 am

JOSEPH GUDAPATI (VIJAYAWADA) said:

  ur detailed explainations helps a lot
April 30, 2007, 6:35 am

PRAVEEN RAJ said:

  VERY CLEARLY EXPLAINED.. THXS.
May 7, 2007, 1:39 pm

Sandeep Bh said:

  Really Appreciate
May 16, 2007, 12:53 am

naveen yenigandla said:

  it is very good. provide some more examples thats is very much good
June 5, 2007, 1:24 am

Sreekanth.K said:

  Examples are good,Its very easy to understand the beginners
June 15, 2007, 12:09 am

Ram roja said:

  Nice explanation . Pls explain about which tablespace that cursor is located??
July 7, 2007, 3:35 am

Vijayalakshmi Gangadasu said:

  This site is really helpful.Provide us with some more examples and complex problems.
July 8, 2007, 6:05 am

Vibhuti said:

  Very good explanation. Really refreshes the concepts at a glance.
August 14, 2007, 10:08 am

manoj singh jadaun said:

  please tell me querry to insert the sysdate i.e date of the system into a column 9c_date of data type date )of a table
October 3, 2007, 3:16 am

TR said:

  ITS EXCELLENT COURSE.......... I HAVE READ SOME BOOKS BUT I DIDNT UNDERSTAND CLEARLY.
BY READING THIS I AM GETTING CLEARLY AND FULL KNOWLEDGE.
October 3, 2007, 11:15 pm

Sandeep Sharma1 said:

  the content of the article is very nice..

Very helpful indeed.

Thanks
October 9, 2007, 1:25 am

manoj singh jadaun said:

  excellent material ,fantastic,
Provide us with some more examples and complex problems
plz tell me a cursor which insert only the system time into table column
thanks
October 12, 2007, 12:16 am

Manus said:

  I'm just about to embark on my first PL/SQL course
this article has give me a great insight into cursors which I knew nothing about.

Supurbly explained

Thanks
October 13, 2007, 11:44 am

arnik said:

  good work . nice explanations
October 23, 2007, 10:56 am

Sanjay choubey said:

  It's realy a nice collection and verry eassy to understand......
November 12, 2007, 3:24 am

S. DeepakKumar said:

  Its a really amazing website
Very helpful and almost all my questions are answered
Thank u
November 14, 2007, 9:11 am

DuraiAS said:

  Very simple and very clear explanations. Good job. Pls continue yourgood effort on advanced topics also.
November 16, 2007, 3:17 pm

Mahender.e said:

  thanks friends
for explaing of cursor indetail
November 17, 2007, 12:12 am

Vidhu Devaraj said:

  Its was really very helpful.Thanks a lot
November 23, 2007, 5:08 am

raviraj1 said:

  it was very simple and understandable to begineers
November 29, 2007, 4:37 am

ankhenatraj@yahoo.com said:

  sir/madam,

thank you for providing such feature in site
can u please send me all the contents with examples

it would be very helpful, to me and my felow employees
December 4, 2007, 9:32 am

pankajs said:

  Its really great help providing to any person unknown about trigger ,function etc..
December 19, 2007, 1:33 am

ABHISHEK VERMA said:

  Really it is fabolus notes that you provided on net.They really helped me.
Thanks
January 12, 2008, 5:50 am

bunty said:

  had lots of confusion about cursors.. all cleared. thankyou so much for the same. keep the good work going.
regards
February 10, 2008, 9:28 pm

Brutus I said:

  Its Good, here its clesrly explianed about theCursors which are very Basics in writing any Procedure
February 21, 2008, 3:11 am

rerohit said:

  Excellent job in very easy and clear straight way
March 11, 2008, 1:10 am

Iniyavan SS said:

  Simple and Excellent examples. Anyone could please ellaborate on WHERE CURRENT OF
March 14, 2008, 3:55 am

yuvaraj.... said:

  Its very useful for beginners.
While reading itself,we can feel tat working with Orale
March 21, 2008, 6:57 am

ali hamamd said:

  Thanks for reminding me the oracle cursors. I was hopeless to find any good and simple help unless I got to your website..

Thanks.
April 4, 2008, 3:47 am

Mohit Makkar said:

  It is a very simple and good way to understand the meaning of Cursor.
Pls every one read it and get the Knoledge about cursor


Thanks
April 9, 2008, 4:31 am

syed salim said:

  It is very nice and examples are fantastic. One can learn a lot from this.
April 17, 2008, 1:55 pm

syed salim said:

  It is very nice. Examples are simply great and understandable. Nice work people -- congratulation
April 17, 2008, 1:58 pm

VRZ said:

  it's truly very nice.
appreciate your work. you explained difficult things in a very good and simple way.
thanks.... :)
have a gr8 day...
April 21, 2008, 4:14 am

Yashoshri Shrivastava said:

  The explanation is very clear and precise. Makes it easy for beginers.
Thanx..............
May 14, 2008, 6:18 am

Praveen kumar karekkadu neelappa said:

  One of The Best material i have come across.. Its an excellent desciption in a very simple way.
Thanks a lot..
May 15, 2008, 9:05 pm

Sarann said:

  Hi ,

It is Really Good Example;
June 4, 2008, 8:17 am

Mr.India said:

  very help ful for beginners.providing excellent examples with execution
June 5, 2008, 2:22 am

Pavindan said:

  its a great site for newcomers1
June 17, 2008, 7:53 am

Swagata said:

  its really very helpful
July 4, 2008, 2:38 am

koti said:

  this is really good one...thank you very much ...but after emp_rec when i try to work on emp_rec its not displaying records..i am a beginner..this notes helping me lot...once again thank you very much.
July 12, 2008, 4:29 pm

satyan said:

  its really very helpful for beginners
August 4, 2008, 4:02 am

theja said:

  very clear.
i am beginner this notes helped me a lot
August 7, 2008, 12:22 pm

Ajaysingh7 said:

  Hi Guys....
The information given here on cursor is very useful...
Its quite clearly specified...
Thanks
August 15, 2008, 2:33 pm

ji said:

  very useful
August 18, 2008, 5:51 am

rock said:

  great work ....
keep it up
August 30, 2008, 6:34 am

Mluhya Bena said:

  A good tutorial. I have never Imagined i could be that way
October 2, 2008, 7:59 am

Nirmala_14 said:

  its really good.. very useful...
October 3, 2008, 7:16 am

Sanjay said:

  Good Work
October 20, 2008, 6:14 pm

Sudhakar said:

  After reading this article we can understand Cursors concept easily...Thanks
October 31, 2008, 11:45 pm

umamaheshwar said:

  Its really excellent.. Please keep to post the important topics....
November 8, 2008, 11:06 am

jeetu said:

  nice explanation
December 22, 2008, 10:00 am

venugopal Gandham said:

  Good explanation
December 29, 2008, 9:46 am

Vishweshwar Nath said:

  this is very useful to me......
January 21, 2009, 8:26 pm

Neeraj said:

  I am not much into PL/SQL programming, however due to my project requirements I had to look onto Cursors. The tutorial has given a good insight. Thanks for the material
March 19, 2009, 2:33 pm

Supriya kadam said:

  Very Gud explanations ....Thanks
March 25, 2010, 2:57 pm

Amitava said:

  Hi,

This is nice and simple tutorial. It helped me a lot.Thanks
May 3, 2010, 7:27 am

Ravi.co.in said:

  Hey Guys,

Clear, concise and precise :)
Thanks for sharing such a superb tutorial.
May 10, 2010, 3:19 pm

Anand said:

  It is short, but gives detailed information. Good one to read.
May 21, 2010, 4:40 am

Ashok Kumar Das said:

  Its really good one.
May 27, 2010, 1:31 pm

cher said:

  Please give an example of a procedure returning a cursor
June 1, 2010, 12:36 am

xxxx said:

  Nice information for beginners.
continue with others topic
June 10, 2010, 12:51 am

Peru said:

  Excellent forum..Thankyou for sharing.
June 10, 2010, 5:31 am

kannan said:

  very nice excellent work keep it up
June 18, 2010, 4:52 am

dj said:

  Great job.......thanks..........its very helpful.......
June 18, 2010, 5:11 pm

dinesh said:

  well done ... its really helpfull
June 25, 2010, 11:19 pm

coolpatti said:

  Valuable
June 26, 2010, 2:44 am

sesirekha said:

  Nice. It was very helpful to me. Thank you
July 2, 2010, 1:43 am

Amir Bagul said:

  It is really good for beginner's.
It will help to clean and clear our idea for Cursor.
Thanks a lot.

July 12, 2010, 6:37 am

Nalini said:

  It's Really Good.
Thanks
July 13, 2010, 11:06 am

Himalaya said:

  Really Nice with good example
July 19, 2010, 3:26 am

SUBHASHCHANDRA S.M said:

  this is very simple to understand
July 20, 2010, 7:20 pm

Arun B S said:

  Great Notes..thanks a lot for this education.
July 25, 2010, 8:14 am

Hymakar said:

  Its very nice covered all the concepts . Appriciated ..
August 5, 2010, 1:17 pm

karur ravi said:

  it is a incredible explanation
August 12, 2010, 7:16 am

Deepak said:

  very usefull to the debut learners,very nice.thanks for..
August 13, 2010, 10:16 am

Adarsh said:

  Excellent job done great work. Really helpfull.
August 21, 2010, 6:07 am

Emeldah said:

  Easy to understand. Well done.
August 25, 2010, 6:16 am

arti said:

  It was really helpful to clear the concept of cursor.
September 1, 2010, 6:27 am

NAVNEET NISHANT said:

  It's very useful for every one thanks.............
September 5, 2010, 10:12 am

mulani pratik said:

  I am impressed with it nice yaar.........
September 8, 2010, 7:17 am

Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

 
 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2010 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape