Static (STATIC) cursor to create a temporary copy of the data used by the cursor. From this temporary table in tempdb all requests to the cursor are answered; Therefore, the data returned by fetches the cursor does not reflect the changes made to the base table, and the cursor is not allowed to modify
Dynamic (dynamic) cursor reflects all data changes made in the scrollable cursor for each row in the result set. Rows of data values, order, and membership will change on each fetch. Dynamic cursors are not supported ABSOLUTE fetch option.
STATIC keyword is specified, the default definition of the cursor is dynamic (dynamic)
Very concerned about the type of cursor should be in the definition of the cursor, and cursor type defined keywords, and plus TYPE_WARNING keywords in order to receive a warning in the case of the cursor type is not expected
Demo:
The following demonstration STATIC and DYNAMIC cursors, both in cursor loop of the difference
[Code = SQL]
– Define the demo data
IF OBJECT_ID (‘tempdb .. # tb’) IS NOT NULL
DROP TABLE # tb
CREATE TABLE # tb (
id int PRIMARY KEY,
col sysname)
Insert # tb (
ID, col)
SELECT 1, ‘AA’ UNION ALL
SELECT 2, ‘BB’ UNION ALL
SELECT 3, ‘CC’ UNION ALL
SELECT 4, ‘DD’
– Cursor test
DECLARE CUR_tb CURSOR LOCAL FORWARD_ONLY READ_ONLY TYPE_WARNING STATIC – DYNAMIC
FOR
SELECT
ID, col
FROM # tb
– Open the front of the cursor delete records
DELETE TOP (1)
FROM # tb
WHERE id = 4
SELECT ‘before cursor open’, * FROM # tb
– Open the cursor
OPEN CUR_tb
– Delete records after a cursor is open
DELETE TOP (1)
FROM # tb
WHERE id = 3
SELECT ‘after cursor open’, * FROM # tb
FETCH CUR_tb
WHILE @ @ FETCH_STATUS = 0
BEGIN
– Delete records in the cursor loop
DELETE TOP (1)
FROM # tb
WHERE id = 2
FETCH CUR_tb
END
CLOSE CUR_tb to
Deallocate CUR_tb is
[/ Code]
This article comes from databaseskill.com blog, reproduced, please indicate the source:
