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:
DECLARE
CUR_ERRORDETAILS CURSOR FOR
SELECT JOBNO,NOOFITEMS,QTY FROM dbo.ERRDETAILS
OPEN
CUR_ERRORDETAILS
FETCH NEXT FROM CUR_ERRORDETAILS INTO @JOBNO,@NOOFITEMS,@QTY
WHILE @@FETCH_STATUS =0
BEGIN
PRINT @JOBNO
PRINT @NOOFITEMS
PRINT @QTY
PRINT '..........................'
FETCH NEXT
FROM CUR_ERRORDETAILS INTO
@JOBNO,@NOOFITEMS,@QTY
END
CLOSE
CUR_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 NOCOUNT
ON;
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 NEXT
FROM
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 FROM
CUR_ERRORDETAILS INTO @JOBNO,@NOOFITEMS,@QTY
END
CLOSE CUR_ERRORDETAILS
DEALLOCATE CUR_ERRORDETAILS
SELECT *
FROM SERVICEMASTER
END
GO
EXEC
UDP_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