A cursor is a database object that applications use to manipulate data by rows instead of recordsets. You can use cursors to perform multiple operations in a row-by-row manner, against the resultset. You can do this with or without returning to the original table. In other words, cursors conceptually return a resultset based on tables within a database.
You can use the cursors to do the following:
- Allow positioning at specific rows of the resultset.
- Retrieve a single row, or set of rows, from the current positions in the resultset.
- Support data modifications to the rows at the current position in the resultset.
- Support different levels of visibility to changes that others users make to the database data that is presented in the resultset.
- Provide T-SQL statements in scripts, stored procedures, and triggers, to access the data in the resultset
In order to work with a cursor we need to perform some steps in the following order:
Declare cursor.
Open Cursor.
Fetch row from the cursor.
Process Fetched row.
Close cursor.
De allocate cursor.
Creating a Cursor
You can use the following two methods to create a cursor in SQL Server:
- The T-SQL language, which supports the syntax for using cursors modelled after the Sql-92 cursor syntax.
- Database application programming interface.
Follow these steps to create a cursor:
- Associate a cursor with a resultSet of a T-SQL statement, and define the characteristics of the cursor, such as how the rows are going to be retrieved, and so forth.
- Execute the T-SQL statement to populate the cursor.
- Retrieve the rows in the cursor. The operation to retrieve one row or a set of rows is called fetch. Scrolling is a series of fetch operations to retrieve the rows in a backward or forward direction.
- You can also perform the modifications on a row at the cursor position.
- Close the cursor.
- Use the DECLARE statement to create the cursor. It contains a SQL statement to include the records from the table.
- After creating the cursor, you will open it. Use the OPEN statement to make the cursor accessible.
- Use the FETCH statement to obtain the records from the cursor for further processing.
- Use the CLOSE statement to temporarily close the cursor when it is not required. This statement releases the current resultset to close an open cursor. You have to re-open the cursor to fetch the rows.
- When you no longer require the cursor, you can use the DEALLOCATE statement to remove its reference.
DECLARECURSOR FOR
<Select Statement>
OPEN
FETCH
CLOSE
DEALLOCATE
Fetching and Scrolling Through a Cursor
| FETCH FIRST | Fetches the first row in the cursor |
| FETCH NEXT | Fetches the row after the previously fetched row |
| FETCH PRIOR | Fetches the row before the previously fetched row |
| FETCH LAST | Fetches the last row in the cursor |
| FETCH ABSOLUTE n | If n is a positive integer, it fetches the nth row in a cursor. If n is a negative integer, it fetches the nth row before the last row. If n is 0, no row is fetched. |
| FETCH RELATIVE n | If n is positive, it fetches the nth row from the previously fetched row. If n is negative, it fetches the nth row before the previously fetched row.If n is 0, the same row is fetched again. |
DECLARE Statement Options
DECLARE
[LOCAL | GLOBAL]
[FORWARD ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR
[FOR UPDATE [OF Column_name[,....N]]]
- Scope of cursor.
- Cursor type.
- Navigation type.
- Lock type
Scope may be local or global. Once we declare cursor in stored procedure as local then it can be accessible only with in stored procedure and if we declare cursor inside stored procedure as Global then it can be accessible outside the procedure execution also.
Navigation Type:
It can be forward_only (or) scroll.
- Forward_Only: Allows processing of cursor only in forward direction.
- Scroll: It allows processing of cursor result in any direction
Sql Server support 4 Types of cursors.
a)Static
Static cursor takes more memory, any operations in the tables is not reflected in static cursor result.
b)Keyset:
Keyset cursor takes less memory. deletions and updations in the tables are reflected in keyset cursor.
c)Fast_Forward:
It is similar to keyset cursor, it gives high performance and fast_forward cursor allows you to process the cursor result forward_only.
d)Dynamic:
Any operations in the tables is dynamically reflected in dynamic cursor. Dynamic cursor gives less performance. It does not allow to process a particular position in a row.
Lock Type:
We can apply three types of locks on cursors
a)Read_Only[shared lock]
We can perform only read operation on cursor result.
b)Scroll_locks[Exclusive lock]
We can perform modifications in the cursor result also and only one user can modify cursor result at a time.
c)Optimistic[Update lock]
We can perform modifications in the cursor result also, multiple users can modify cursor result at a time.
------------------------
Absolute Position or Relative Position
Absolute Position always fetches the particular position row which starts from the beginning of the cursor.
Relative Position can fetch the particular position of the row, based on current position. relative may be positive or negative.
Advantages of Cursors
- Cursor allows us to process the result row by row and printing in formatted results.
- we can perform batch operations, deletions in the table through cursor result updations.
Drawbacks of Cursors
- Cursor degrades the performance while processing row by row.
- Alternative is user defined functions, common table expressions.
Posts
No comments:
Post a Comment