What is a Cursor ?

A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set . 

                                                             Or

 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 

We have basically two type of cursor in SQL

·        Implicit cursors

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

·        Explicit cursors

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

Life Cycle/Syntax of Cursor

Life Cycle/Syntax of Cursor

1.            Declare Cursor

A cursor is declared by defining the SQL statement that returns a result set.

  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

2.            Open

A Cursor is opened and populated by executing the SQL statement defined by the cursor.

OPEN [GLOBAL] cursor_name --by default it is local

3.            Fetch

When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.

FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]

FROM [GLOBAL] cursor_name

INTO @Variable_name[a,b,c.....n]

4.            Close

After data manipulation, we should close the cursor explicitly.

CLOSE cursor_name

We car reopen a cursor after closing it.

5.            Deallocate

Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

DEALLOCATE cursor_name

Example:

SET NOCOUNT ON

DECLARE @Id int

DECLARE @name varchar(50)

DECLARE @salary int

DECLARE cur_emp CURSOR

STATIC FOR

     SELECT EmpID,EmpName,Salary from Employee

OPEN cur_emp

IF @@CURSOR_ROWS > 0

     BEGIN

           FETCH NEXT FROM cur_emp INTO @Id,@name,@salary

           WHILE @@Fetch_status = 0

           BEGIN

PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)

                FETCH NEXT FROM cur_emp INTO @Id,@name,@salary

           END

END

CLOSE cur_emp

DEALLOCATE cur_emp

SET NOCOUNT OFF

Statements operations supported by Cursor are

•           CLOSE      
•          CREATE PROCEDURE      
•          DEALLOCATE      
•          DECLARE CURSOR      
•          DECLARE @local_variable      
•          DELETE      
•          FETCH      
•          OPEN      
•          UPDATE      
•          SET      

System functions and system stored procedures also support cursors

•          @@CURSOR_ROWS      
•          CURSOR_STATUS      
•          @@FETCH_STATUS      
•          sp_cursor_list      
•          sp_describe_cursor      
•          sp_describe_cursor_columns      
•       sp_describe_cursor_tables