Why do we go for CURSOR instead of SELECT

lucky.rohith · New User Joined: 06 Aug 2006 Posts: 10

Hi
This was the question asked by one intervier.
The question is
Why do v go for CURSOR instead of SELECT.Through these two v can select all the rowsin the table,Then why v go for CURSOR.
He asked to explain tat difference.

guptae · Posted: Thu Sep 28, 2006 10:39 am

Hi lucky,

If the query return multiple rows then we need to use cursor to process every record of resultset that cant be possible in using select in program.

cj10 · New User Joined: 18 Sep 2006 Posts: 9

The host program can process only 1 row at a time. The SELECT query might return multiple rows, but for the program to process 1 at a time, we require cursors.

lucky.rohith · New User Joined: 06 Aug 2006 Posts: 10

HI Guptae,
Thanks fpr ur reply.But i'm not satisfied with ur reply.I hav answered the same thing to my intervier,he was not satisfied too.
So can u explain tat throughexample.

Thanks
lucky

guptae · Posted: Sun Oct 01, 2006 8:23 pm

Hi Lucky,

If we have Employee Db2 table & if we want to write empoyees record whose salary > 10000 in to O/p File then we have to go for cursor.

Employee db2 table: Emp name, DOJ, Basic Pay ,Emp code

O/p File : Emp name , Doj , Basic pay, Emp code , HRA , TA

HRA should be calculated 20% of basic pay &
TA should be 15% of basic Pay

SO we will use cursor in main program & fetch record by record then write in to flat file after calculating HRA ,TA

Hope I make myself clear enogh.

lucky.rohith · New User Joined: 06 Aug 2006 Posts: 10

Hi Guptae,
Thanks for ur reply.I got the use of the Cursor.But when we wil go for cousor and wen for simple select.

guptae · Posted: Mon Oct 02, 2006 12:53 pm

Hi Lucky,

U need to go for cursor when u know the result of ur query is more than 1 row & u want to process them otherwise go for simple select.

meetreks · New User Joined: 28 Sep 2006 Posts: 26 Location: Edinburgh

Hi,

The reason why we dont use a SELECT when multiple rows/table are possible is, we get a -811 when we do so. This exception is avoided when we use a cursor. The reason behind it is simple.

When we sue a cursor, contary to the poppular belief that only one row is selected during the execution of the FETCH statement, DB2 actually retuns all qualified rows/paging size for the FETCH statement. But then manner in which they are communicated back to the application program slightly differs from that of the SELECT statement.

While we use the FETCH , information about only one row is communicated back in the communication area (SQLCA) and hence all the information will be stable and correct.

In SELECT, DB2 cannot correctly provide the information in the communication area, and hence you could have incorrect information in the communication area. ALso DB2 cannot confirm that you will always get the same row whenever you reissue the SELECT.

Hope this helps.

lal_arun_272 · New User Joined: 09 Oct 2006 Posts: 31 Location: Bangalore

Why do we use cursor ???.

I use to see when ever some one ask this question, every one will go for select statment is retrive more than one row we will go for cursor. else the job end with 811.

Let me put it different way..

1. in SQL if can write select statment with into clause which retrive more than one row we don't need cursor. <<DB2 won't have such select statment>>so we should use cursor

2.we can update or delete rows from the table using update or DELETE statment using queries But think before update or delete we need to check what is existing value thier ...that case we should use cursor

Thanks
Arunlal

Jotedup7 · New User Joined: 24 Sep 2006 Posts: 1

lol check this one

thefunnyvids.org