Loading...

The main functionality of cursor is to retrieve data one row at a time. Means cursor provide a functionality of looping in SQL Server. A cursor uses System Memory when execute.  
Its useful when we want to manipulate the records of table in a single method, in other words one record at a time. Cursor holds more than one rows but process only one row at a time. 

There are two types of cursor:

  1. Implicit Cursor
  2. Explicit Cursor

Implicit Cursor: 
Implicit type cursor is generated and used by the system during manipulation of data in a DML Statement like Insert, Update and Delete. For Insert operation, the cursor holds the data that need to be inserted. For Update and Delete operations the cursor identifies the rows that has to be Updated or Deleted.

Explicit Cursor:
Explicit type cursor is generated by use using Select statement. An explicit cursor holds more than one rows but only one row processed at a time. Explicit cursor moves one by one over the rows. Explicit cursor uses a pointer to holds the record of a row. Explicit cursor move one row to next row. 


Each cursors contains below steps

Declare Cursor: Here, we declare variables and cursor.
Open: This is the opening of cursor.
Fetch: Fetch is used to retrieve the data row by row and assign its values to variables.
Close: This is the closing of cursor.
Deallocate: In this section we delete the cursor definition and release all the system resources with the cursor. 


Example of cursor 

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = 'C:\Backup\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
      BACKUP DATABASE @name TO DISK = @fileName 

      FETCH NEXT FROM db_cursor INTO @name 
END 
CLOSE db_cursor  
DEALLOCATE db_cursor 
  

Microsoft SQL Server supports 4 types of cursors.

1. STATIC CURSOR - Static cursor populate the result during cursor creation and result set is cached for timeline of the cursor. A static cursor can move forward and backward. 

2. FAST_FORWARD - This is the default type cursor. It is identical to the static except we can move forward only.

3. DYNAMIC - The dynamic cursor are exactly opposed to static cursor and we can use this type of cursor to insert, update and delete operations. In the dynamic cursor, all the changes made in dynamic cursor will reflect to the original data. 

Example of Dynamic Cursor
-- Declaring the Variables 
DECLARE @EmpID INT, @EmpName VARCHAR(50),@EmpEducation VARCHAR(50),@EmpOccupation VARCHAR(50),@EmpYearlyIncome DECIMAL (10, 2), @EmpSales DECIMAL (10, 2);

-- SQL Dynamic Cursor Declaration
DECLARE dynamic_employee_cursor CURSOR 
DYNAMIC FOR 
SELECT [ID] ,[Name],[Education],[Occupation],[YearlyIncome],[Sales]FROM EmployeeTable
        ORDER BY Occupation

OPEN dynamic_employee_cursor
IF @@CURSOR_ROWS > 0
BEGIN 
      FETCH NEXT FROM dynamic_employee_cursor
            INTO @EmpID, @EmpName, @EmpEducation,
         @EmpOccupation, @EmpYearlyIncome, @EmpSales
      WHILE @@FETCH_STATUS = 0
      BEGIN
IF @EmpOccupation = N'Management'
      UPDATE [EmployeeTable] 
SET [YearlyIncome] = 999999,
    [Sales] = 15000
    WHERE CURRENT OF dynamic_employee_cursor                
        FETCH NEXT FROM dynamic_employee_cursor 
             INTO @EmpID, @EmpName, @EmpEducation,
          @EmpOccupation, @EmpYearlyIncome, @EmpSales
      END
END
CLOSE dynamic_employee_cursor
DEALLOCATE dynamic_employee_cursor


4. KEYSET - This type of cursor is similar to dynamic cursor. The KEYSET cursor can only move from the first row to last row and last row to first row.  If another user deletes a record, it is inaccessible from our record set.