The cursor is used for accessing the set of records from the result set with a row at a time. The difference between T-SQL statement and a cursor is that the statement operates on all the records. The result set created contains the common rows and user needs to process the data per row. The features of cursors in sql are as listed below: The cursor is declared individually and is different from the execution one. The result set in a cursor remains open till it is closed explicitly by the user Using some of the navigation commands provided by the cursor, the data can be navigated The cursor are easily referred by the name added in its declaration Life cycle of a cursor The life cycle of a cursor contains various stages. All the stages are mentioned in brief: Declaration of a cursor: The cursor is declared using the sql statement. The DECLARE CURSOR cursor_name statement is used for cursor declaration. A result set is returned by the cursor. Open a cursor: The cursor opens and is filled with data by using the sql statement. The OPEN cursor_name is used for opening the cursor. Fetch a cursor: Once the cursor is opened, the rows are fetched one at a time or in a block. The changes on the data is possible. Close the cursor: Once all the changes to the cursor data is finalized, the cursor is closed. Deallocate the cursor: The cursor definition and data can be removed by deallocating the cursor. Syntax for cursor declaration The DECLARE cursor_name CURSOR is used for declaring the cursor. The cursor declaration contains attributes for scroll and the content on which the output is dependent. Syntax: Code: DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] For select_statement [ FOR UPDATE [ OF column_name [ , ….n ] ] ] [ ; ] Where, cursor_name is the name of the cursor used. LOCAL: The scope of the cursor is limited to the local stored procedure, batch or trigger in which it is used. GLOBAL: The scope of the cursor is global to the specific connection FORWARD_ONLY: The scrolling of cursor is possible from the first to the last row. STATIC: A temporary copy of data is created KEYSET: The order of the rows present in the cursor are fixed DYNAMIC: The data changes done to the rows in the result are assigned on scrolling the cursor FAST_FORWARD: The performance optimization is enabled READ_ONLY: Any modifications done through the cursor are avoided SCROLL_LOCKS: The update or delete done through the cursor which are successful OPTIMISTIC: The updates or modifications done through the cursor are not successful when the last update was performed TYPE_WARNING: The warning message which is sent to the client when the cursor is converted from a specific type selectstatement: The result set of the cursor is defined using the statement FOR UPDATE [ OF columnname [ ,…n ]]: The columns which can be updated are defined Fetch cursor The row from the cursor are accessed using the fetch statement. The particular row is accessed from the sql server. Syntax: Code: FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ] FROM ] { { [ GLOBAL ] cursor_name } | @cursor_var } [ INTO @var_name [ ,….n ] ] Where NEXT: The row set containing the present rows and the increments. PRIOR: The row set immediately following the current row FIRST: The first row in the cursor and is made the current row LAST: The last row in the cursor is the current row ABSOLUTE { n | @nvar } is used for returning the n rows from the start of the cursor RELATIVE { n | @nvar } is used for returning the rows beyond the current row GLOBAL is used to define the global cursor cursor_name is the name of the cursor from using which the data is fetched @cursor_var is the cursor variable used for referencing the open cursor INTO @var_name [ ,…n ] The data from the columns is fetched and added in the local variables Close and deallocate cursor The resources available in the cursor are removed and the cursor is closed. The data structures are used for reusing the closed cursor, accessing the data and updates are not applicable till the cursor opens again. User cannot use close cursor if it is declared or closed earlier. The deallocate cursor is used for removing the reference of the cursor. Once the last reference of the cursor is deallocated, the corresponding data structure is removed. Syntax for close cursor Code: CLOSE { { [ GLOBAL ] cursorname } | cur_var_name } Where, GLOBAL: The name of the cursor which is used for referencing the global cursor cursorname: name assigned to the open cursor cur_var_name: the name of cursor variable related to the open cursor Syntax for deallocate cursor Code: DEALLOCATE { { [ GLOBAL ] cursorname } | cur_var_name } Where, cursorname: name assigned to the cursor cur_var_name: the name of cursor variable related to the open cursor Example of cursor Consider the following example to demonstrate the use of cursor in SQL. Create a table as electiondata and add the values in it. Code: create table electiondata ( voterid int not null, age int not null, location varchar(20) not null, electiondate date not null) insert into electiondata values ( 415, 30, ‘Mumbai’, ‘2014-02-02’) insert into electiondata values ( 345, 22, ‘Pune’, ‘2014-02-02’) insert into electiondata values ( 9001, 43, ‘Bangalore’, ‘2014-02-22’) insert into electiondata values ( 12301, 34, ‘Delhi’, ‘2014-02-13’) insert into electiondata values ( 22201, 26, ‘Srinagar’, ‘2014-02-24’) Declare the cursor and fetch the results from the table Code: declare @VoterId int declare @Age int declare @Location varchar(20) declare @ElectionDate date declare election_data cursor static for select voterid, age, location, electiondate from electiondata open election_data if @@CURSOR_ROWS > 0 begin fetch next from election_data into @VoterID, @Age, @Location, @Electiondate while @@FETCH_STATUS = 0 begin print ‘VoterID:’+ convert(varchar(20) ,@VoterId)+’Age:’ +convert(varchar(20),@Age+’, Location:’+@Location) fetch next from election_data into @VoterId, @Age, @Location end end close election_data
SQL Server is a relational database management system (RDBMS) from Microsoft that's designed for the enterprise environment.