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.
Algorithm or steps of use SQL Cursor:
1. DECLARE CURSOR:
For declare a cursor in sql database.
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.
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