Asp.net programming we consider all important topic for knowledge about controls,web Services, JSON, Ajax, database, and interview related Question. In this Asp.net and sql server tutorial blog we give many examples of jquery as like How Play YouTube Video in Your Asp.net Website by URL, How to Play YouTube Videos in Asp.net by using Jquery , Jquery Change div background on mouseover in asp.net , JQuery UI Datepicker (Calendar) etc.

Tuesday, November 4, 2014

Sql server cursor declaration syntax example

Sql- what is cursor, use Cursor in  PL/Sql:


In this post we learn about sql cursor, what cursor is, why we use cursor and how we use cursor in sql. Also we give the syntax with description. And an Example of sql cursor.

In this tutorial blog we write many topics related to the sql database.
If you are new in sql database the start for Introduction of sql. And here we consider many other sql related Post these are listed here.




we can easily understand by following points:

  • A cursor is a temporary work area created in the system memory when a SQL statement is executed.
  • Some time we can say sql database cursor is a database objects to retrieve data from a result set one row at a time
  •  A cursor contains information on a select statement and the rows of data accessed by it.
  •  This temporary work area is used to store the data retrieved from the database, and manipulate this data.
  •  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.
  •  Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records.
  •  Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records.



Sql server cursor declaration syntax example



Algorithm or steps of use SQL Cursor:



1. DECLARE CURSOR:

For declare a cursor in sql database.

DECLARE <cursor_name> [INSENSITIVE] [SCROLL] CURSOR FOR Select_statement

Where cursor_name is user defined.

2. OPEN :

3. FETCH:

when the cursor is open you can fetch from it row by row.

FETCH        
        [    [    NEXT | PRIOR | FIRST | LAST
                | ABSOLUTE {n | @nvar}
                | RELATIVE {n | @nvar}
            ]
            FROM
        ]
{ { [GLOBAL] cursor_name } | @cursor_variable_name}
[INTO @variable_name[,...n] ]


4. CLOSE: 

After completed the work you can close the opened cursor for releasing the resources.

CLOSE { { [GLOBAL] cursor_name } | cursor_variable_name }

5. DEALLOCATE  : 

When you have finished work and want to completely release SQL Server resources as like memory , you can deallocate a cursor.

DEALLOCATE { { [GLOBAL] <cursor_name> } | <@cursor_variable_name>}

Syntax of Declaration of cursor in sql:


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 ] ] ]
[;]




Type of SQL cursor:

There are two type of sql server cursor.
  • Implicit cursors
  • Explicit cursors

Next sql cursor post:

  • Implicit sql cursor.
  • Explicit sql cursor.


Asp.net Related Other Post:


1 comment:

  1. T-SQL is the stored procedure for SQL Server however your diagram shows the PL/SQL. PL/SQL is the stored procedure for Oracle. Suggest you to correct it.

    ReplyDelete