Cursors in SQL Server

Discussion in 'SQL Server' started by MinalS, May 5, 2016.

  1. 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:
    1. The cursor is declared individually and is different from the execution one.
    2. The result set in a cursor remains open till it is closed explicitly by the user
    3. Using some of the navigation commands provided by the cursor, the data can be navigated
    4. 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:
    1. 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.
    2. 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.
    3. 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.
    4. Close the cursor: Once all the changes to the cursor data is finalized, the cursor is closed.
    5. 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.
    1. 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’)
      
    2. 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
      
     
    shabbir likes this.
  2. vickya4n

    vickya4n New Member

    Joined:
    Aug 23, 2016
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    Gender:
    Male
    SQL Server is a relational database management system (RDBMS) from Microsoft that's designed for the enterprise environment.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice