Sql Server 2000 中游标的使用示例 [ZT]
以下是使用游标的一种最直接的方式:
代码运行条件:一张Tb_paperInfo(paperID,paperName)表
![](/Images/OutliningIndicators/None.gif)
--声明一个标识为c的游标,该游标是从tb_paperInfo表中查出两列
declare c cursor for select top 3 paperID,paperName from tb_paperinfo
![](/Images/OutliningIndicators/None.gif)
--打开游标c
open c
![](/Images/OutliningIndicators/None.gif)
--声明两个变量,作为临时存储相应的字段的值
declare @paperID varchar(20);
declare @paperName varchar(20);
![](/Images/OutliningIndicators/None.gif)
--@@FETCH_STATUS返回游标的位置,用来标识该游标是否已达到末尾或是否还在开关
--在未执行Fetch Next From c时,@@Fetch_Status为-1
FETCH NEXT FROM C into @paperID,@paperName ----注意:此时into 之后的变量的个数对应于select 出来的列的个数,执行完该语句之后@@Fetch_Status返回值0
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
declare @i int;
set @i=1;
while @@FETCH_STATUS=0
begin
/*在此处,你可以通过使用@paperID和@paperName变量来做你想要的事*/
print '第' + cast(@i as varchar(20)) + ' 条记录的paperID:' + @paperID
print '第' + cast(@i as varchar(20)) + ' 条记录的paperName:' + @paperName
--通过fetch next from 语句取出下一条记录
fetch next from c into @paperID,@paperName
set @i=@i+1;
end
![](/Images/OutliningIndicators/None.gif)
--关闭游标,若想再用它,还可以用open的方法再一次打开它(要在释放之前打开)
close c
![](/Images/OutliningIndicators/None.gif)
--释放游标
deallocate c
代码运行条件:一张Tb_paperInfo(paperID,paperName)表
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)