MSSQL帮助里的例子:下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。SET NOCOUNT ONDECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
@message varchar(80), @title varchar(80)PRINT "-------- Utah Authors report --------"DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_idOPEN authors_cursorFETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lnameWHILE @@FETCH_STATUS = 0
BEGIN
PRINT " "
SELECT @message = "----- Books by Author: " +
@au_fname + " " + @au_lname PRINT @message -- Declare an inner cursor based
-- on au_id from the outer cursor. DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title IF @@FETCH_STATUS <> 0
PRINT " <<No Books>>" WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = " " + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title
END CLOSE titles_cursor
DEALLOCATE titles_cursor
-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
ENDCLOSE authors_cursor
DEALLOCATE authors_cursor
GO-------- Utah Authors report --------
----- Books by Author: Anne Ringer
The Gourmet Microwave
Is Anger the Enemy?
----- Books by Author: Albert Ringer
Is Anger the Enemy?
Life Without Fear
@message varchar(80), @title varchar(80)PRINT "-------- Utah Authors report --------"DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_idOPEN authors_cursorFETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lnameWHILE @@FETCH_STATUS = 0
BEGIN
PRINT " "
SELECT @message = "----- Books by Author: " +
@au_fname + " " + @au_lname PRINT @message -- Declare an inner cursor based
-- on au_id from the outer cursor. DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title IF @@FETCH_STATUS <> 0
PRINT " <<No Books>>" WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = " " + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title
END CLOSE titles_cursor
DEALLOCATE titles_cursor
-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
ENDCLOSE authors_cursor
DEALLOCATE authors_cursor
GO-------- Utah Authors report --------
----- Books by Author: Anne Ringer
The Gourmet Microwave
Is Anger the Enemy?
----- Books by Author: Albert Ringer
Is Anger the Enemy?
Life Without Fear
DECLARE @vchDeleteStmt NVARCHAR(500)
DECLARE tableCursor CURSOR
FAST_FORWARD
FOR
SELECT name FROM SAXON_RAW.dbo.sysobjects
WHERE name LIKE 'Sasta_0103%'
AND xtype = 'U'
ORDER BY name ASCOPEN tableCursor
FETCH NEXT FROM tableCursor INTO @vchTableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @vchDeleteStmt = 'delete ' +
'FROM ' + @vchTableName +
' WHERE rtrim(ltrim(InvestorName))=''''' --EXEC @vchInsertStmt
PRINT @vchDeleteStmt
PRINT 'GO'
FETCH NEXT FROM tableCursor INTO @vchTableName
END
CLOSE tableCursor
DEALLOCATE tableCursor
while @@FETCH_STATUS=0
begin
...
你的处理语句
...
fetch next from 游标名 into @变量1,@变量2,...,@变量n
endclose 游标名
deallocate 游标名
Select 字段1,字段2,...,字段n from 表名fetch next from 游标名 into @变量1,@变量2,...,@变量n
while @@FETCH_STATUS=0
begin
...
你的处理语句
...
fetch next from 游标名 into @变量1,@变量2,...,@变量n
endclose 游标名
deallocate 游标名正解!