cursordemo12.sql

T-SQL示例大全

/* 文件名称:  CursorDemo12.sql */
USE pubs
GO
-- Create and open a global named cursor that
-- is visible outside the batch.
DECLARE abc CURSOR GLOBAL SCROLL FOR
SELECT * FROM authors

OPEN abc
GO

-- Reference the named cursor with a cursor variable.
DECLARE @MyCrsrRef1 CURSOR

SET @MyCrsrRef1 = abc

-- Now deallocate the cursor reference.
DEALLOCATE @MyCrsrRef1

-- Cursor abc still exists.
FETCH NEXT FROM abc
GO

-- Reference the named cursor again.
DECLARE @MyCrsrRef2 CURSOR

SET @MyCrsrRef2 = abc

-- Now deallocate cursor name abc.
DEALLOCATE abc

-- Cursor still exists, referenced by @MyCrsrRef2.
FETCH NEXT FROM @MyCrsrRef2

-- Cursor finally is deallocated when last referencing
-- variable goes out of scope at the end of the batch.
GO

-- Create an unnamed cursor.
DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM titles

-- The following statement deallocates the cursor
-- because no other variables reference it.
DEALLOCATE @MyCursor
GO