Cursor in Sql Server
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.
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.
The server side cursors were first added in SQL Server 6.0 release and now supported in all editions of SQL Server 7.0 and SQL Server 2000.
Before using cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close cursor and deallocate it to release SQL Server resources
Life Cycle of 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
Syntax to Open Cursor:-A Cursor can be opened locally or globally. By default it is opened locally. The basic syntax to open cursor is given below:
Syntax to Fetch Cursor:-Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option. The basic syntax to fetch cursor is given below:
Syntax to Close Cursor:-Close statement closed the cursor explicitly. The basic syntax to close cursor is given below:
Syntax to Deallocate Cursor:- Deallocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below:
Types of Cursors:-
Example:-We are going first create table in SQL SERVER and insert some records into table as shown in below.
Examples of Cursors:-Here we create a cursor which fetch some records as shown in below.
Output:-
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.
The server side cursors were first added in SQL Server 6.0 release and now supported in all editions of SQL Server 7.0 and SQL Server 2000.
Before using cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close cursor and deallocate it to release SQL Server resources
Life Cycle of Cursor:-
- Declare Cursor:-A cursor is declared by defining the SQL statement that returns a result set.
- Open:- A Cursor is opened and populated by executing the SQL statement defined by the cursor.
- Fetch:-When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
- Close:-After data manipulation, we should close the cursor explicitly.
- 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
Syntax to Open Cursor:-A Cursor can be opened locally or globally. By default it is opened locally. The basic syntax to open cursor is given below:
OPEN [GLOBAL] cursor_name --by default it is local
Syntax to Fetch Cursor:-Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option. The basic syntax to fetch cursor is given below:
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n] FROM [GLOBAL] cursor_name INTO @Variable_name[1,2,..n]
Syntax to Close Cursor:-Close statement closed the cursor explicitly. The basic syntax to close cursor is given below:
CLOSE cursor_name --after closing it can be reopen
Syntax to Deallocate Cursor:- Deallocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below:
DEALLOCATE cursor_name --after deallocation it can't be reopen
Types of Cursors:-
- Forward-Only
- Static
- Keyset
- Dynamic
Example:-We are going first create table in SQL SERVER and insert some records into table as shown in below.
CREATE TABLE tblEmployee ( EmpID int PRIMARY KEY, EmpName varchar (50) NOT NULL, EmailId varchar(200), Salary int NOT NULL, Address varchar (200) NOT NULL, ) INSERT INTO tblEmployee VALUES(1001,'Santosh kumar singh','s@gmail.com',1200,'A-566') INSERT INTO tblEmployee VALUES(1002,'Reena kumari','r@gmail.com',1700,'H-560') INSERT INTO tblEmployee VALUES(1003,'Pooaj','p@gmail.com',1200,'P-566') INSERT INTO tblEmployee VALUES(1004,'Prem kumar','pk@gmail.com',1900,'D-320') INSERT INTO tblEmployee VALUES(1005,'Jon','j@gmail.com',1300,'J-506') INSERT INTO tblEmployee VALUES(1006,'Jemmy','jm@gmail.com',1100,'K-500') INSERT INTO tblEmployee VALUES(1007,'Santosh kumar singh','sk@gmail.com',1800,'T-120')
Examples of Cursors:-Here we create a cursor which fetch some records as shown in below.
SET NOCOUNT ON DECLARE @Id int DECLARE @name varchar(50) DECLARE @salary int DECLARE cur_emp CURSOR STATIC FOR SELECT EmpID,EmpName,Salary from tblEmployee 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
Output:-