declare cursor_insert cursor for select c# from cs
declare
@i int
open cursor_insert
fetch cursor_insert into @i
while @@fetch_status=0
begin
print @i
fetch cursor_insert into @i
end
close cursor_insert
deallocate cursor_insert
declare
@i int
open cursor_insert
fetch cursor_insert into @i
while @@fetch_status=0
begin
print @i
fetch cursor_insert into @i
end
close cursor_insert
deallocate cursor_insert
可以采用将一个游标放到另一存储过程中,来实现你的功能。
@1 cursor varying out,
@2 cursor varying out
as
declare b cursor local for select * from table1
declare c cursor local for select * from table1
open b
open c
set @1=b
set @2=c
godeclare @a cursor,@b cursorexec a @a out,@b out
fetch @a
fetch @b
fetch @b
fetch @b
fetch @aclose @a
close @bdeallocate @a
deallocate @bdrop proc a
请详述另一存储过程,难道将一游标放到另一存储过程中就不影响对前面游标的判断?
declare b cursor local for select * from tableb
open a
fetch a into @a1
while fetch_stattus=0
{
fetch b into @b1
while fetch_stattus=0 {
//do sth with @a1 and @a2
...
fetch b into @a2
}
fetch a into @a1
}
sorry ,this is correct!declare a cursor local for select * from tablea
declare b cursor local for select * from tableb
open a
fetch a into @a1
while fetch_stattus=0
{
fetch b into @b1
while fetch_stattus=0 {
//do sth with @a1 and @b1
...
fetch b into @b1
}
fetch a into @a1
}
open afetch a into @a1 ---a游标
while fetch_stattus=0 ----在这里检查只a游标,应为在执行这条语句的前一条总是在fetch a游标
begin
declare b cursor local for select * from tableb where 列=@a1 --根据a游标的变量得到b游标的表
open b
fetch b into @b1
while fetch_stattus=0 --在这里检查只b游标,应为在执行这条语句的前一条总是在fetch b游标
begin
exec 过程名 @a1,@b1 --这里执行过程
fetch b into @b1
end
close b ---在下一次生成b游标必须先关闭,并释放
deallocate b fetch a into @a1
end
close a
deallocate a
create proc a
@1 cursor varying out,
@2 cursor varying out
as
declare b cursor local for select * from table1
declare c cursor local for select * from table2 --table1 table2数据不一样大小
open b
open c
set @1=b
set @2=c
godeclare @a cursor,@b cursorexec a @a out,@b outWHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @a
FETCH NEXT FROM @b
END
CLOSE @aCLOSE @b
DEALLOCATE @a
DEALLOCATE @b
drop procedure a运行可以都取出table1 table2的记录 设table1记录10条 table2记录>10
结果
table1 记录1
table2 记录1
table1 记录2
table2 记录2
table1 记录3
table2 记录3
....
table1 记录10
table2 记录10
talbe2 记录11
talbe2 记录12
.....
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。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