CURSOR

      What is Cursor ? 

                  It is a memory allocation. i.e. used for storing multi row select statements data under the program. Normal variables can store only one value at a point of time where as a cursor can store n number of records at a point of time. 
To work with a cursor we need to understand cursor management which involves in 5 steps.

   Step 1 : Declaring a Cursor

   Step 2 : Opening a Cursor

   Step 3 : Fetching data from the Cursor

   Step 4 : De-allocation the Cursor

   Step 5 : Closing a Cursor 

  

  Declaring a Cursor 

              In this process we declare a cursor and intimate to the database engine that we want a memory  allocation for processing the select statement associate with the cursor declaration. 

 Syntax:-

                Declare Cursor_name cursor

                [ Local | Global ]

                [ Forward only | Scroll ]  

                [ Static | Dynamic ]

                for <Select Statement>

  Opening a Cursor 

                In this process it will allocate the memory for the cursor and loads that a into the cursor by execution the select statement  specified on the declare cursor.

  Syntax:-  

               open <cursor_name> 

Note:

    After loading the data into the cursor it will also provide a pointer for accessing the data from the cursor.

Fetching data from the cursor 

                In this process we retrieve one by one record from the cursor for processing.

  Syntax:-

                Fetch First | Last |Next | Prior | Absolute n | Relative n                       from cursor_name into  <variables>

When we perform  a fetch operation it will return status of the last fetch statement and value can be any of the following.

   0 :  The fetch statement was successful.

 - 1 : The fetch statement failed or the row was beyond the result set

 - 2 : The row fetched is missing.

 Closing the cursor  

                In this process it releases that current result set or data from the memory but leaves the data structure  available for re-opening. 

   Syntax:- 

                Close <cursor_name>

   

De-Allocation the Cursor  

                    In this process it will remove the cursor references and De-allocates the data structure of the cursor.

   Syntax:- 

              Deallocate <cursor_name>; 

For Example applying all step :-
----------------------------------------
Suppose we have a table Employee

   Step 1 :   

        Declare @ename varchar(50), @job varchar(40);       
        Declare EmpCur Cursor for select Empname,job from employee

Step 2 :

        open empcur

Step 3:

        fetch next from empcur into @ename,@job

        while @@fetch_status=0

          Begin

                print @ename + ' ' +@ job;

                fetch next from empcur into @ename,@job;

          End

Step 4 :

       close empcur;

Step 5:

       deallocate empcur;

Note:-
     The value that returns by the fetch statement is stored under an implicit local variable  '@@fetch_status'

 [Local or Global ] cursor
---------------------------------

                                 If the cursor is declare as 'local' the scope of the cursor is only to the program in which the cursor is declare. So in the end of the program execution even if we did not De-allocate the cursor also the cursor gets De-allocate implicitly.

Ex:-       
        Declare @empno int
     Declare EmpCur Cursor local for select Empno from employee

        open empcur;

        fetch next from empcur into @empno;

        while @@fetch_status=0

          Begin

                print @empno;

                fetch next from empcur into @empno;

          End

      close empcur;

      deallocate empcur;

                                               Open a new query window and write the above code in it, we declare the above cursor as a local cursor. So even if we did not de-allocate the cursor also in the end of the program the cursor gets de-allocate. So to test this declare cursor statement and execute the program for second time get the error.

Note:-
---------
  If the cursor is declare as 'global' and not de-allocated that cursor can be used in the other program also with in the connection.
Ex:-       
 Declare @empno int
    Declare EmpCur Cursor global for select Empno from employee

        open empcur;

        fetch next from empcur into @empno;

        while @@fetch_status=0

          Begin

                print @empno;

                fetch next from empcur into @empno;

          End

      close empcur;

      deallocate empcur;

write the above program in a new connection window and execute, we declare the cursor as global in a program and did not de-allocate cursor that means  without re-deallocate.
To test this comment the declare cursor statement and execute the same program for n number of times the connection.
Ex:-

Declare @empno int
--declare Empcur cursor global for select empno from Employee
--open empcur
 fetch next from empcur into @empno
  while @@FETCH_STATUS=0
      begin
         print @empno
         fetch next from empcur into @empno
     end

close empcur

Note:-
-----------
All global cursors declared under a connection or de-allocation once we close the connection.

[ Static or dynamic ] cursor

-----------------------------------

                           If a cursor is declared as a static after opening the cursor and loading data into the cursor if any modification are made to the data in the table those changes will not be reflected in to the cursor. But 

If the cursor is declare as dynamic the modification of the table reflects into the cursor even after the cursor is open also.

Ex:-

    Declare @sal money

    declare Empcur cursor Static for select salary from Employee           where Empno=1001
      open empcur
       update employee set salary+=500 where Empno=1001
       fetch next from empcur into @sal
    print @sal
close empcur

  output:
-----------

In the above case we declare the cursor as static and open the cursor for loading data into the cursor. So, After opening the cursor the update operation we performed on the table will not reflect into the cursor. So, Print statement will print the old salary of the employee but not in updated value.
Execute the same program by changing static as dynamic and watch the output the print statement will display the new salary of the employee.
Ex:-
    Declare @sal money
    declare Empcur cursor dynamic for select salary from Employee           where Empno=1001
      open empcur
       update employee set salary+=500 where Empno=1001
       fetch next from empcur into @sal
    print @sal
close empcur
  output:
-----------

[ Forward only or scroll ] cursor

----------------------------------------
                                          In the cursor is declare forward-only we can navigate only to the next record from the current cursor position. The only fetch method support in forward-only cursor is fetch next.
Where as if a cursor is declared as scroll we can navigate to any record under the cursor from any position because scroll cursor support 6 fetch method.
  i) Fetch Next
 ii) Fetch prior
iii) Fetch  First
iv) Fetch  Last
 v) Fetch  Absolute
vi) Fetch relative n

Ex:-
     Declare @empno int
     declare Empcur cursor scroll for select empno from Employee
     open empcur
        fetch last from empcur into @empno
             print @empno
        fetch prior from empcur into @empno
             print @empno
        fetch first from empcur into @empno
             print @empno
        fetch next from empcur into @empno
             print @empno
        fetch absolute 3 from empcur into @empno
             print @empno
        fetch relative -3 from empcur into @empno
             print @empno
  close empcur

Output:-

-----------