SQL-游标-查询

/***DB版本：SQL Server 2022***/
--切换数据库
use MyDatabase

--创建游标(scroll：滚动游标)
declare mycur cursor scroll for select EmpNo from Employee

--打开游标
open mycur
--提取第一行
fetch first from mycur
--提取最后一行
fetch last from mycur
--提取第二行
fetch absolute 2 from mycur
--当前行下移两行
fetch relative 2 from mycur
--下移一行
fetch next from mycur
--上移一行
fetch prior from mycur
--关闭游标
close mycur
--删除游标
deallocate mycur


--例1：提取游标数据存入变量
declare @empno int 
fetch absolute 2 from mycur into @empno
select * from Employee where EmpNo = @empno

--例2：遍历游标 (@@fetch_status：0：提取成功,-1：失败,·-2：提取的内容不存在)
declare @empno int
fetch absolute 1 from mycur into @empno
while @@fetch_status = 0
	begin
		print '提取成功 :' + cast(@empno as varchar(3))
		fetch next from mycur into @empno
	end

例１結果：

 例２結果：