游标嵌套,主要用于复杂计算,数据分析和处理。
不过要消耗系统性能。可以做一个jobs 在晚上夜里运行。
S Q L 游 标 嵌 套 存 储 过 程
文库:http://wenku.baidu.com/view/28e3414de518964bcf847c04.html
里面有用到游标变量:
set @cmd = 'declare cursor_tmp_2 cursor for select val from tmp1 where id in (' + @vals + ')'
EXEC (@cmd)
open cursor_tmp_2
在游标中更新数据:
set @command = 'update tmp2 set vals =''' + @vals3 + ''' where id =' + convert(char, @id)
print @command
EXEC (@command)
---------------------------------------------------------------------------------------
嵌套例子:
SET NOCOUNT ON
DECLARE @au_id varchar(11),@au_fname varchar(20),@au_lname varchar(40),@message varchar(80), @title varchar(80)
PRINT "--------Authors report --------"
DECLARE authors_cursor CURSOR FOR SELECT au_id, au_fname, au_lname FROM authors
WHERE state = "UT" ORDER BY au_id
OPEN authors_cursor
FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lname
WHILE @@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>>" --no found: -1 false ,-2 not exist
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
END
CLOSE authors_cursor
DEALLOCATE authors_cursor