SQL Server 游標(biāo)語(yǔ)句 聲明/打開/循環(huán)實(shí)例

SQL Server游標(biāo)語(yǔ)句使用方法：?

代碼如下：

--聲明一個(gè)游標(biāo)?
DECLARE?MyCursor?CURSOR?
FOR?SELECT?TOP?5?FBookName,FBookCoding?FROM?TBookInfo//定義一個(gè)叫MyCursor的游標(biāo)，存放for?select?后的數(shù)據(jù)?

--打開一個(gè)游標(biāo)?
OPEN?MyCursor//即打開這個(gè)數(shù)據(jù)集?

--循環(huán)一個(gè)游標(biāo)?
DECLARE?@BookName?nvarchar(2000),@BookCoding?nvarchar(2000)?
FETCH?NEXT?FROM?MyCursor?INTO?@BookName,@BookCoding//移動(dòng)游標(biāo)指向到第一條數(shù)據(jù)，提取第一條數(shù)據(jù)存放在變量中?
WHILE?@@FETCH_STATUS?=0//如果上一次操作成功則繼續(xù)循環(huán)?
BEGIN?
print?'name'+@BookName?
FETCH?NEXT?FROM?MyCursor?INTO?@BookName,@BookCoding//繼續(xù)提下一行?
END?

--關(guān)閉游標(biāo)?
CLOSE?MyCursor?
--釋放資源?
DEALLOCATE?MyCursor

eg 代碼如下

create?table?#a?
(?
id?varchar(20),?
name?varchar(20)?
)?
insert?into?#a?select?1,'jack'?
insert?into?#a?select?2,'join'?
insert?into?#a?select?3,'make'?
declare?mycursor?cursor?
for?select?*?from?#a?
open?mycursor?
declare?@id?varchar(20),@name?varchar(20)?
fetch?next?from?mycursor?into?@id,@name?
while?@@fetch_status=0?
begin?
select?@id,@name?
fetch?next?from?mycursor?into?@id,@name?
end?
close?mycursor?
deallocate?mycursor