Cusror in DB2 ---> Part II

==> Click Here to Read Part III <==

In first part we discussed about, what the cursor is and why we need it.

In this part we will discuss about the types of cursor. Yes there are different types of cursor and they work differently depending on their type.

In normal or simple cursor the data can only be retrieved one row at a time in forward direction using "FETCH" statement.

Scrollable Cursor :-

With Scrollable cursor, you can scroll forward/backward through the data(Result set of select statement) in an application program.

Ok but before everything else how do you distinguish that the cursor defined in the program is scrollable or not.

The keyword "SCROLL" is use to specify that the cursor defined is scrollable.

Example:

Declare  Cursor1 (cursor name) 

             SCROLL Cursor

Ok so this is how you define it.Now, how does it work? how do you move through the data?

Similar to normal cursor, Fetch statement is still used  but is combined with different keyword to move the fetching in specific direction.

Example :

FIRST            :- This will fetch the first row in the result set.

CURRENT     :- This will fetch the current row from the result set.

Similarly there are different keywords like LAST,NEXT,BEFORE,AFTER,PRIOR etc.

So for the above defined cursor , the fetch statement will be

FETCH FIRST Cursor1 INTO .....

Fetch first statement will position the cursor to the first row of the result set and then fetch it to the defined host variable.

Scrollable cursor makes it easy to scroll through the data thus saves amount of time and effort.

There are two types of scrollable cursor.

Yes there are again types of scrollable cursor but it make sense to understand on what factor they are diffracted.

1. Sensitive      : With this type of scrollable cursor,the program can access the
                           data changed /updated by the user.So at the time, the data
                           being fetch from the table using the cursor if some  other

                           process update/delete the data from table that get reflected in
                           the result set.

                           However, Inserts will not be reflected in the result set.

2. Insensitive :   This type of scrollable cursor will not show any change that are 
                          made to the data at the time of fetch so the underlying data will 
                          not be reflected in the result set

                            

So the definition of cursor finally would finally look like

Declare Cursor1 (cursor name) 

         SENSITIVE SCROLL Cursor

Thing to remember: -

Though easier to use , you should not define every cursor to be scrollable as it require more overhead to the normal/conventional type of cursor.

To be specific , if you do not need to move forward /backword through the result set do not use scrollable cursor.

Until now we saw that using cursor(be it any type) we can fetch only one row at a time.We can also fetch multiple rows using single fecth statement in a cursor.

How do you do it?? we will see it in next part.

==> Click Here to Read Part III <==