Cursors:
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis.
Syntax:
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
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 ] ] ]
[;]
Example:
DECLARECUR_ERRORDETAILS CURSOR FOR
SELECT JOBNO,NOOFITEMS,QTY FROM dbo.ERRDETAILS
OPENCUR_ERRORDETAILS
FETCH NEXT FROM CUR_ERRORDETAILS INTO @JOBNO,@NOOFITEMS,@QTY
WHILE @@FETCH_STATUS =0
BEGIN
PRINT @JOBNO
PRINT @NOOFITEMS
PRINT @QTY
PRINT '..........................'
FETCH NEXTFROM CUR_ERRORDETAILS INTO@JOBNO,@NOOFITEMS,@QTY
END
CLOSECUR_ERRORDETAILS
DEALLOCATE CUR_ERRORDETAILS
Cursor Types
- Static cursors
- Dynamic cursors
- Forward-only cursors
- Keyset-driven cursors
Static Cursors:
The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened.
The cursor does not reflect any changes made in the database that affect either the membership of the result set or changes to the values in the columns of the rows that make up the result set.
A static cursor does not display new rows inserted in the database after the cursor was opened.No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened), not even modifications made using the same connection that opened the cursor.
Microsoft® SQL Server™ static cursors are always read-only.
Because the result set of a static cursor is stored in a work table in tempdb, the size of the rows in the result set cannot exceed the maximum row size for a SQL Server table.
Dynamic Cursors
Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor. Updates are visible immediately if they are made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause.
Forward – Only Cursors:
A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are fetched. The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor. Because the cursor cannot be scrolled backward, however, changes made to rows in the database after the row was fetched are not visible through the cursor, except for the case where a value used to determine the location of the row within the result set is modified, such as updating a column covered by a clustered index.
Keyset-driven Cursors
The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a set of unique identifiers (keys) known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.
Inserts to the database made outside the cursor are not visible in the cursor unless the cursor is closed and reopened. Inserts made through the cursor using an API function such as the ODBC SQLSetPos function are visible at the end of the cursor. @@FETCH_STATUS returns a "row missing" status when an attempt is made to fetch a row deleted after the cursor was opened.
An update to a key column operates like a delete of the old key value followed by an insert of the new key value. The new key value is not visible if the update was not made through the cursor; it is visible at the end of the cursor if the update was made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause and the SELECT statement did not contain a JOIN condition in the FROM clause.
CREATE PROCEDURE UDP_UPDATE_ORDERMASTER
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNTON;
DECLARE @JOBNO INT
DECLARE @NOOFITEMS INT
DECLARE @QTY BIGINT
DECLARE CUR_ERRORDETAILS CURSOR FOR
SELECT JOBNO,NOOFITEMS,QTY FROM dbo.ERRDETAILS
OPEN CUR_ERRORDETAILS
FETCH NEXTFROM CUR_ERRORDETAILS INTO @JOBNO,@NOOFITEMS,@QTY
WHILE @@FETCH_STATUS=0
BEGIN
--PRINT @JOBNO
--PRINT @NOOFITEMS
--PRINT @QTY
--PRINT '..........................'
UPDATE dbo.SERVICEMASTER SET AMOUNT = 0 WHERE JOBNO = @JOBNO
UPDATE dbo.SERVICEMASTER
SET
AMOUNT = (@NOOFITEMS *@QTY/100)+12
WHERE JOBNO= @JOBNO
FETCH NEXT FROMCUR_ERRORDETAILS INTO @JOBNO,@NOOFITEMS,@QTY
END
CLOSE CUR_ERRORDETAILS
DEALLOCATE CUR_ERRORDETAILS
SELECT *FROM SERVICEMASTER
END
GO
EXECUDP_UPDATE_ORDERMASTER
Advantages:
- Cursors allow row-by-row prcessing of the resultsets.
Disadvantages of cursors:
- Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors
No comments:
Post a Comment