Share Blog

Saturday, March 22, 2014

CURSOR

CURSOR:Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row.

Components Of Cursor:

1.  Declare Cursor: A cursor is declared by defining the SQL statement that returns a result set.
2.    Open: A Cursor is opened and populated by executing the SQL statement defined by the cursor.
3. Fetch: When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
4.  Close
After data manipulation, we should close the cursor explicitly.
5.  Deallocate
Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.
Syntax to Declare Cursor
Declare Cursor SQL Comaand is used to define the cursor with many options that impact the scalablity and loading behaviour of the cursor. The basic syntax is given below
           DECLARE cursor_name CURSOR
     [LOCAL | GLOBAL] --define cursor scope
     [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)      [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor        [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks                    FOR select_statement --define SQL Select statement                       FOR UPDATE [col1,col2,...coln] --define columns that need to be updated 

Type of Cursor

cursor allow us to retrieve data from a result set in singleton fashion means row by row. Cursor are required when we need to update records in a database table one row at a time. 
A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs.


Types of Cursors
1. Static Cursors
A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor. A static cursor can move forward and backward direction. A static cursor is slower and use more memory in comparison to other cursor. Hence you should use it only if scrolling is required and other types of cursors are not suitable.
You can't update, delete data using static cursor. It is not sensitive to any changes to the original data source. By default static cursors are scrollable.
2.  Dynamic Cursors
A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations. By default dynamic cursors are scrollable.
3.  Forward Only Cursors
A forward only cursor is the fastest cursor among the all cursors but it doesn't support backward scrolling. You can update, delete data using Forward Only cursor. It is sensitive to any changes to the original data source.
There are three more types of Forward Only Cursors.Forward_Only KEYSET, FORWARD_ONLY STATIC and FAST_FORWARD.
A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the data to the cursor lifetime. It is not sensitive to any changes to the data source.
A FAST_FORWARD Cursor is the fastest cursor and it is not sensitive to any changes to the data source.
4.  Keyset Driven Cursors

A keyset driven cursor is controlled by a set of unique identifiers as the keys in the keyset. The keyset depends on all the rows that qualified the SELECT statement at the time of cursor was opened. A keyset driven cursor is sensitive to any changes to the data source and supports update, delete operations. By default keyset driven cursors are scrollable.
SQL SERVER – Examples of Cursors
-------Create Table-----------------


CREATE TABLE EmployeeDetails(
 EmployeeID int PRIMARY KEY,
 EmployeeName varchar (50) NOT NULL,
 Salary int NOT NULL,
 Address varchar (200) NOT NULL,
)

-----Insert values in Table---------

insert into EmployeeDetails values(5,'Ravi',11000,'Noida'),(6,'Naveen',12000,'Delhi'),
(7,'shivam',13000,'UP'),(4,'Kavita',14000,'Kanpur'),(5,'Mukesh',15000,'Orissa')

1.Static Cursor - Example
---------Static Cursor--------
    DECLARE @EmployeeId int
DECLARE @Employeename varchar(50)
DECLARE @salary int
DECLARE cur_employee
CURSOR STATIC FOR 
SELECT EmployeeID,EmployeeName,Salary from EmployeeDetails
OPEN cur_employee
IF @@CURSOR_ROWS > 0
BEGIN 
FETCH NEXT FROM cur_employee INTO @EmployeeId,@Employeename,@salary
WHILE @@Fetch_status = 0
BEGIN
PRINT 'ID : '+ convert(varchar(20),@EmployeeId)+', Name : '+@Employeename+ ', Salary : '+convert(varchar(20),@salary)
FETCH NEXT FROM cur_employee INTO @EmployeeId,@Employeename,@salary
END
END
    CLOSE cur_employee
    DEALLOCATE cur_employee


2.Dynamic Cursor - Example

---------Dynamic Cursor For Update------------

    DECLARE @EmployeeId int
DECLARE @Employeename varchar(50)
DECLARE Dynamic_cur_employeeupdate 
CURSOR DYNAMIC FOR 
SELECT EmployeeID,EmployeeName from Employeedetails ORDER BY EmployeeName
OPEN Dynamic_cur_employeeupdate
IF @@CURSOR_ROWS > 0
BEGIN 
FETCH NEXT FROM Dynamic_cur_employeeupdate INTO @EmployeeId,@Employeename
WHILE @@Fetch_status = 0
BEGIN
IF @Employeename='naveen'
Update EmployeeDetails SET Salary=25000 
WHERE CURRENT OF Dynamic_cur_employeeupdate
FETCH NEXT FROM Dynamic_cur_employeeupdate INTO @EmployeeId,@Employeename
END
END
CLOSE Dynamic_cur_employeeupdate
DEALLOCATE Dynamic_cur_employeeupdate
select * from EmployeeDetails

---------Dynamic Cursor For Delete------------


       DECLARE @EmployeeId int
DECLARE @Employeename varchar(50)
DECLARE Dynamic_cur_employeedelete 
CURSOR DYNAMIC FOR 
SELECT EmployeeID,EmployeeName from EmployeeDetails ORDER BY EmployeeName
OPEN Dynamic_cur_employeedelete
IF @@CURSOR_ROWS > 0
BEGIN 
FETCH NEXT FROM Dynamic_cur_employeedelete INTO @EmployeeId,@Employeename
WHILE @@Fetch_status = 0
BEGIN
IF @Employeename='mukesh'
DELETE EmployeeDetails WHERE CURRENT OF Dynamic_cur_employeedelete
FETCH NEXT FROM Dynamic_cur_employeedelete INTO @EmployeeId,@Employeename
END
END
CLOSE Dynamic_cur_employeedelete
DEALLOCATE Dynamic_cur_employeedelete
select * from EmployeeDetails
3.Forward Only Cursor - Example
-------------Forward Only Cursor for Update--------------
DECLARE @EmployeeId int
DECLARE @Employeename varchar(50)
DECLARE Forward_cur_employeeupdate 
CURSOR FORWARD_ONLY FOR 
SELECT EmployeeID,EmployeeName from Employeedetails 
ORDER BY EmployeeName
OPEN Forward_cur_employeeupdate
IF @@CURSOR_ROWS > 0
BEGIN 
FETCH NEXT FROM Forward_cur_employeeupdate INTO @EmployeeId,@Employeename
WHILE @@Fetch_status = 0
BEGIN
IF @Employeename='Ravi'
Update EmployeeDetails SET Salary=24000 WHERE CURRENT OF Forward_cur_employeeupdate
FETCH NEXT FROM Forward_cur_employeeupdate INTO @EmployeeId,@Employeename
END
END
CLOSE Forward_cur_employeeupdate
DEALLOCATE Forward_cur_employeeupdate
Select * from EmployeeDetails 

------------- Forward Only Cursor for Delete------------------

      DECLARE @EmployeeId int
DECLARE @Employeename varchar(50)
DECLARE Forward_cur_employeedelete CURSOR
FORWARD_ONLY
FOR 
SELECT EmployeeID,EmployeeName from EmployeeDetails ORDER BY EmployeeName
       OPEN Forward_cur_employeedelete
IF @@CURSOR_ROWS > 0
BEGIN 
FETCH NEXT FROM Forward_cur_employeedelete INTO @EmployeeId,@Employeename
WHILE @@Fetch_status = 0
BEGIN
IF @Employeename='shivam'
DELETE EmployeeDetails WHERE CURRENT OF Forward_cur_employeedelete
FETCH NEXT FROM Forward_cur_employeedelete INTO @EmployeeId,@Employeename
END
END
CLOSE Forward_cur_employeedelete
DEALLOCATE Forward_cur_employeedelete
\
        Select * from EmployeeDetails 


4.Keyset Driven Cursor - Example
------------- Keyset driven Cursor for Update----------------
DECLARE @EmployeeId int
DECLARE @Employeename varchar(50)
DECLARE Keyset_cur_employeeupdate 
CURSOR KEYSET FOR 
SELECT EmployeeID,EmployeeName from EmployeeDetails ORDER BY EmployeeName
OPEN Keyset_cur_employeeupdate
IF @@CURSOR_ROWS > 0
BEGIN 
FETCH NEXT FROM Keyset_cur_employeeupdate INTO @EmployeeId,@Employeename
WHILE @@Fetch_status = 0
BEGIN
IF @Employeename='Naveen'
Update EmployeeDetails SET Salary=27000 WHERE CURRENT OF Keyset_cur_employeeupdate
FETCH NEXT FROM Keyset_cur_employeeupdate INTO @EmployeeId,@Employeename
END
END
CLOSE Keyset_cur_employeeupdate
     DEALLOCATE Keyset_cur_employeeupdate
      Select * from EmployeeDetails 




------------ Keyset Driven Cursor for Delete--------------
DECLARE @EmployeeId int
DECLARE @Employeename varchar(50)
DECLARE Keyset_cur_employeedelete 
CURSOR KEYSET FOR 
     SELECT EmployeeID,EmployeeName from Employeedetails ORDER BY EmployeeName
     OPEN Keyset_cur_employeedelete
IF @@CURSOR_ROWS > 0
BEGIN 
FETCH NEXT FROM Keyset_cur_employeedelete INTO @EmployeeId,@Employeename
WHILE @@Fetch_status = 0
BEGIN
IF @Employeename='shivam'
DELETE EmployeeDetails WHERE CURRENT OF Keyset_cur_employeedelete
FETCH NEXT FROM Keyset_cur_employeedelete INTO @EmployeeId,@Employeename
END
END
CLOSE Keyset_cur_employeedelete
DEALLOCATE Keyset_cur_employeedelete
Select * from EmployeeDetails



  






No comments:

Post a Comment