参考: DECLARE @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_lnameFROM authorsWHERE state = "UT"ORDER BY au_id OPEN authors_cursor FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lname WHILE @@FETCH_STATUS = 0BEGIN 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_lnameEND CLOSE authors_cursorDEALLOCATE authors_cursor
比如SQL语句是 select PName from Point 我如何在循环中获得PName的值?
declare cur cursor read_only for select PName from Point declare @name varchar(40) open curfetch next from cur into @name while (@@fetch_status ==0) begin print @name fetch next from cur into @name endclose cur deallocate cur go
--写错了,只有一个=declare cur cursor read_only for select PName from Point declare @name varchar(40) open curfetch next from cur into @name while (@@fetch_status =0) begin print @name fetch next from cur into @name endclose cur deallocate cur go
declare c_test cursor for select col1,col2,col3,...,from yourtable where ... declare @col1 yourtype,@col2 yourtype,@col3 yourtypeopen c_test fetch next from c_test into @Col1,@col2,@col3,... while @@FETCH_STATUS=0 begin insert into yournewtable select @col1,@col2,@col3,... fetch next from c_test into @Col1,@col2,@col3 endclost c_test deallocate c_test
游标格式: Declare 游标名 cursor for 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 游标名
DECLARE @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_lnameFROM authorsWHERE state = "UT"ORDER BY au_id OPEN authors_cursor FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lname WHILE @@FETCH_STATUS = 0BEGIN 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_lnameEND CLOSE authors_cursorDEALLOCATE authors_cursor
select PName from Point
我如何在循环中获得PName的值?
declare cur cursor
read_only
for select PName from Point declare @name varchar(40)
open curfetch next from cur into @name
while (@@fetch_status ==0)
begin
print @name fetch next from cur into @name
endclose cur
deallocate cur
go
read_only
for select PName from Point declare @name varchar(40)
open curfetch next from cur into @name
while (@@fetch_status =0)
begin
print @name fetch next from cur into @name
endclose cur
deallocate cur
go
declare @col1 yourtype,@col2 yourtype,@col3 yourtypeopen c_test
fetch next from c_test into @Col1,@col2,@col3,...
while @@FETCH_STATUS=0
begin
insert into yournewtable select @col1,@col2,@col3,...
fetch next from c_test into @Col1,@col2,@col3
endclost c_test
deallocate c_test
Declare 游标名 cursor for 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 游标名