Maintenance SOA.org, My SOA, e-Learning, SOA store and all other SOA online services will be unavailable beginning Monday, Oct 31 at 3:00pm through Tuesday, Nov. 1 at 4:00pm CST. Thank you for your patience. Introduction A cursor in SQL is a temporary work area created in system memory when a SQL statement is executed. A SQL cursor is a set of rows together with a pointer that identifies a current row. It is a database object to retrieve data from a result set one row at a time. It is useful when we want to manipulate the record of a table in a singleton method, in other words, one row at a time. In other words, a cursor can hold more than one row but can process only
one row at a time. The set of rows the cursor holds is called the active set. Types of Cursors in SQLThere are the following two types of cursors in SQL: - Implicit Cursor
- Explicit Cursor
Implicit Cursor These types of cursors are generated and used by the system during the manipulation of a DML query (INSERT, UPDATE, and DELETE). An implicit cursor is also generated by the system when a single row is selected by a SELECT
command. Explicit Cursor This type of cursor is generated by the user using a SELECT command. An explicit cursor contains more than one row, but only one row can be processed at a time. An explicit cursor moves one by one over the records. An explicit cursor uses a pointer that holds the record of a row. After fetching a row, the cursor pointer moves to the next row. Main components of CursorsEach cursor contains the followings 5 parts, - Declare
Cursor: In this part, we declare variables and return a set of values.
- Open: This is the entering part of the cursor.
- Fetch: Used to retrieve the data row by row from a cursor.
- Close: This is an exit part of the cursor and used to close a cursor.
- Deallocate: In this part, we delete the cursor definition and release all the system resources associated with the cursor.
Syntax of a Cursor - DECLARE @Variable nvarchar(50) /* Declare All Required Variables */
- DECLARE Cursor_Name CURSOR /* Declare Cursor Name*/
- [LOCAL | GLOBAL] /* Define Cursor Scope */
- [FORWARD_ONLY | SCROLL] /* Define Movement Direction of Cursor */
- [ KEYSET | DYNAMIC |STATIC | FAST_FORWARD] /* Define basic type of cursor */
- [ SCROLL_LOCKS | OPTIMISTIC |READ_ONLY ] /* Define Locks */
-
- OPEN Cursor_Name /* Open Cursor */
- FETCH NEXT FROM Cursor_Name /* Fetch data From Cursor */
- Implement SQL QUery
- CLOSE Cursor_Name /* Clsoe The Cursor */
- DEALLOCATE Cursor_Name /* Deallocate all resources and Memory */
Now we will explain 4 important terminologies of cursors. Cursor ScopeMicrosoft SQL Server supports the GLOBAL and LOCAL keywords on the DECLARE CURSOR statement to define
the scope of the cursor name. - GLOBAL - specifies that the cursor name is global to the connection.
- LOCAL - specifies that the cursor name is local to the Stored Procedure, trigger, or query that holds the cursor.
Data Fetch Option in CursorsMicrosoft SQL Server supports the following two fetch options for data: - FORWARD_ONLY - Specifies that the cursor can only be scrolled from the first to the last row.
- SCROLL - It provides 6
options to fetch the data (FIRST, LAST, PRIOR, NEXT, RELATIVE, and ABSOLUTE).
Types of cursorsMicrosoft SQL Server supports the following 4 types of cursors. - STATIC CURSOR
A static cursor populates the result set during cursor creation and the query result is cached for the lifetime of the cursor. A static cursor can move forward and backward. - FAST_FORWARD
This is the default type of cursor. It is identical to the static except
that you can only scroll forward. - DYNAMIC
In a dynamic cursor, additions and deletions are visible for others in the data source while the cursor is open. - KEYSET
This is similar to a dynamic cursor except we can't see records others add. If another user deletes a record, it is inaccessible from our recordset.
Types of LocksLocking is the process by which a DBMS restricts access to a row in a multi-user environment. When a row or column
is exclusively locked, other users are not permitted to access the locked data until the lock is released. It is used for data integrity. This ensures that two users cannot simultaneously update the same column in a row. Microsoft SQL Server supports the following three types of Locks. - READ ONLY
Specifies that the cursor cannot be updated. - SCROLL_LOCKS
Provides data integrity into the cursor. It specifies that the cursor will lock
the rows as they are read into the cursor to ensure that updates or deletes made using the cursor will succeed. - OPTIMISTIC
Specifies that the cursor does not lock rows as they are read into the cursor. So, the updates or deletes made using the cursor will not succeed if the row has been updated outside the cursor.
First, we create a table as in the following,
- GO
-
- CREATE TABLE [dbo].[Employee](
- [Emp_ID] [int] NOT NULL,
- [Emp_Name] [nvarchar](50) NOT NULL,
- [Emp_Salary] [int] NOT NULL,
- [Emp_City] [nvarchar](50) NOT NULL,
- CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
- (
- [Emp_ID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
-
- GO
Now insert some values into the table as in the following, - Insert into Employee
- Select 1,'Pankaj',25000,'Alwar' Union All
- Select 2,'Rahul',26000,'Alwar' Union All
- Select 3,'Sandeep',25000,'Alwar' Union All
- Select 4,'Sanjeev',24000,'Alwar' Union All
- Select 5,'Neeraj',28000,'Alwar' Union All
- Select 6,'Naru',20000,'Alwar' Union All
- Select 7,'Omi',23000,'Alwar'
Select all values from the table as in the following, Example 1
- SET NOCOUNT ON
- DECLARE @EMP_ID INT
- DECLARE @EMP_NAME NVARCHAR(MAX)
- DECLARE @EMP_SALARY INT
- DECLARE @EMP_CITY NVARCHAR(MAX)
-
- DECLARE EMP_CURSOR CURSOR
- LOCAL FORWARD_ONLY FOR
- SELECT * FROM Employee
- OPEN EMP_CURSOR
- FETCH NEXT FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- WHILE @@FETCH_STATUS = 0
- BEGIN
- PRINT 'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+ ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY) + ' EMP_CITY ' +@EMP_CITY
- FETCH NEXT FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- END
- CLOSE EMP_CURSOR
- DEALLOCATE EMP_CURSOR
Output
EMP_ID: 1 EMP_NAME Pankaj EMP_SALARY 25000 EMP_CITY Alwar EMP_ID: 2 EMP_NAME Rahul EMP_SALARY 26000 EMP_CITY Alwar EMP_ID: 3 EMP_NAME Sandeep EMP_SALARY 25000 EMP_CITY Alwar EMP_ID: 4 EMP_NAME Sanjeev EMP_SALARY 24000 EMP_CITY Alwar EMP_ID: 5 EMP_NAME Neeraj EMP_SALARY 28000 EMP_CITY Alwar EMP_ID: 6 EMP_NAME Naru EMP_SALARY 20000 EMP_CITY Alwar EMP_ID: 7 EMP_NAME Omi EMP_SALARY 23000 EMP_CITY Alwar
This is a simple example of a cursor that
prints the value of a table. Example 2 (SCROLL)
- SET NOCOUNT ON
- DECLARE @EMP_ID INT
- DECLARE @EMP_NAME NVARCHAR(MAX)
- DECLARE @EMP_SALARY INT
- DECLARE @EMP_CITY NVARCHAR(MAX)
-
- DECLARE EMP_CURSOR CURSOR
- LOCAL SCROLL FOR
- SELECT * FROM Employee
- OPEN EMP_CURSOR
- FETCH NEXT FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
-
- FETCH RELATIVE 3 FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- PRINT 'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+ ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY) + ' EMP_CITY ' +@EMP_CITY
- FETCH ABSOLUTE 3 FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
-
- PRINT 'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+ ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY) + ' EMP_CITY ' +@EMP_CITY
- FETCH FIRST FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- PRINT 'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+ ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY) + ' EMP_CITY ' +@EMP_CITY
- FETCH LAST FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- PRINT 'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+ ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY) + ' EMP_CITY ' +@EMP_CITY
- FETCH PRIOR FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- PRINT 'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+ ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY) + ' EMP_CITY ' +@EMP_CITY
- FETCH NEXT FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- PRINT 'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+ ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY) + ' EMP_CITY ' +@EMP_CITY
-
-
- CLOSE EMP_CURSOR
- DEALLOCATE EMP_CURSOR
Output EMP_ID: 4 EMP_NAME Sanjeev EMP_SALARY 24000 EMP_CITY Alwar EMP_ID: 3 EMP_NAME Sandeep EMP_SALARY 25000 EMP_CITY Alwar EMP_ID: 1 EMP_NAME Pankaj EMP_SALARY 25000 EMP_CITY Alwar EMP_ID: 7 EMP_NAME
Omi EMP_SALARY 23000 EMP_CITY Alwar EMP_ID: 6 EMP_NAME Naru EMP_SALARY 20000 EMP_CITY Alwar EMP_ID: 7 EMP_NAME Omi EMP_SALARY 23000 EMP_CITY Alwar
In this example, we will use SCROLL to fetch the data. This example contains all six modes of SCROLL (FIRST, LAST, PRIOR, NEXT, RELATIVE, and ABSOLUTE). Example 3 (STATIC CURSOR) - SET NOCOUNT ON
- DECLARE @EMP_ID INT
- DECLARE @EMP_NAME NVARCHAR(MAX)
- DECLARE @EMP_SALARY INT
- DECLARE @EMP_CITY NVARCHAR(MAX)
-
- DECLARE EMP_CURSOR CURSOR
- STATIC FOR
- SELECT * FROM Employee
- OPEN EMP_CURSOR
- FETCH NEXT FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- WHILE @@FETCH_STATUS = 0
- BEGIN
- If @EMP_ID%2=0
- BEGIN
- PRINT 'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+ ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY) + ' EMP_CITY ' +@EMP_CITY
- END
- FETCH FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- END
- CLOSE EMP_CURSOR
- DEALLOCATE EMP_CURSOR
Output EMP_ID: 2 EMP_NAME Rahul EMP_SALARY 26000 EMP_CITY Alwar EMP_ID: 4 EMP_NAME Sanjeev EMP_SALARY 24000 EMP_CITY Alwar EMP_ID: 6 EMP_NAME Naru EMP_SALARY 20000 EMP_CITY Alwar
Example 4 - SET NOCOUNT ON
- DECLARE @EMP_ID INT
- DECLARE @EMP_NAME NVARCHAR(MAX)
- DECLARE @EMP_SALARY INT
- DECLARE @EMP_CITY NVARCHAR(MAX)
-
- DECLARE EMP_CURSOR CURSOR
- STATIC FOR
- SELECT * FROM Employee
- OPEN EMP_CURSOR
- FETCH NEXT FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- WHILE @@FETCH_STATUS = 0
- BEGIN
- If @EMP_ID%2=0
- BEGIN
- UPDATE Employee SET Emp_Salary=15000 WHERE CURRENT OF EMP_CURSOR
- END
- FETCH FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- END
- CLOSE EMP_CURSOR
- DEALLOCATE EMP_CURSOR
Output Msg 16929, Level 16, State 1, Line 16 The cursor is READ ONLY. The statement has been terminated. Msg 16929, Level 16, State 1, Line 16 The cursor is READ ONLY. The statement has been terminated. Msg 16929, Level 16, State 1, Line 16 The cursor is READ ONLY. The statement has been terminated.
When executing this cursor, we will get an error because the static cursor does not allow
modifications in data. Example 5 (DYNAMIC CURSOR)
- SET NOCOUNT ON
- DECLARE @EMP_ID INT
- DECLARE @EMP_NAME NVARCHAR(MAX)
- DECLARE @EMP_SALARY INT
- DECLARE @EMP_CITY NVARCHAR(MAX)
-
- DECLARE EMP_CURSOR CURSOR
- DYNAMIC FOR
- SELECT * FROM Employee
- OPEN EMP_CURSOR
- FETCH NEXT FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- WHILE @@FETCH_STATUS = 0
- BEGIN
- If @EMP_ID%2=0
- BEGIN
- UPDATE Employee SET Emp_Salary=15000 WHERE CURRENT OF EMP_CURSOR
- END
- FETCH FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- END
- CLOSE EMP_CURSOR
- DEALLOCATE EMP_CURSOR
- SELECT * FROM Employee
Output Example 6
- SET NOCOUNT ON
- DECLARE @EMP_ID INT
- DECLARE @EMP_NAME NVARCHAR(MAX)
- DECLARE @EMP_SALARY INT
- DECLARE @EMP_CITY NVARCHAR(MAX)
-
- DECLARE EMP_CURSOR CURSOR
- FAST_FORWARD FOR
- SELECT * FROM Employee
- OPEN EMP_CURSOR
- FETCH NEXT FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- WHILE @@FETCH_STATUS = 0
- BEGIN
- If @EMP_ID%2=0
- BEGIN
- UPDATE Employee SET Emp_Salary=15000 WHERE CURRENT OF EMP_CURSOR
- END
- FETCH FROM EMP_CURSOR INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- END
- CLOSE EMP_CURSOR
- DEALLOCATE EMP_CURSOR
Output Msg 16929, Level 16, State 1, Line 16 The cursor is READ ONLY. The statement has been terminated. Msg 16929, Level 16, State 1, Line 16 The cursor is READ ONLY. The statement has been terminated. Msg 16929, Level 16, State 1, Line 16 The cursor is READ ONLY. The statement has been terminated.
A FAST_FORWARD cursor also reads as a static cursor. We cannot modify data in a FAST_FORWARD cursor.
Example 7 - SET NOCOUNT ON
- DECLARE @EMP_ID INT
- DECLARE @EMP_NAME NVARCHAR(MAX)
- DECLARE @EMP_SALARY INT
- DECLARE @EMP_CITY NVARCHAR(MAX)
-
- DECLARE EMP_CURSOR1 CURSOR
- KEYSET scroll
- FOR
- SELECT EMP_ID ,EMP_NAME,EMP_SALARY,EMP_CITY FROM Employee order by Emp_Id
- OPEN EMP_CURSOR1
- IF @@CURSOR_ROWS > 0
- BEGIN
- FETCH NEXT FROM EMP_CURSOR1 INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- WHILE @@FETCH_STATUS = 0
- BEGIN
-
- If @EMP_ID%2=0
- UPDATE Employee SET EMP_NAME='PANKAJ KUMAR CHOUDHARY' WHERE CURRENT OF EMP_CURSOR1
- FETCH NEXT FROM EMP_CURSOR1 INTO @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
- END
- END
- CLOSE EMP_CURSOR1
- DEALLOCATE EMP_CURSOR1
- SET NOCOUNT OFF
- SELECT * FROM Employee
Output SummaryIn this article, we learned about cursors in SQL. We learned about types of SQL cursors, how to write a cursor in SQL and execute it.
Which statement is used to retrieve the row from the cursor?
FETCH statement to retrieve rows from the result table of the cursor.
Which statement is used to get the data from the table into cursor?
The FETCH statement advances the cursor to the first or next row in the set, and loads the values indicated in the SELECT clause of the DECLARE CURSOR statement into host language variables.
Which statement is used to define a cursor?
The DECLARE CURSOR statement defines a cursor.
In which type of cursor the key values of the rows are saved in tempdb?
The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.
|