游标基础学习
1.简单游标使用
--Student表,sno varchar(36),sname varchar(36)
--内置数据
--sno001 sname001
--sno002 sname002
--sno003 sname003
--定义简单循环游标
DECLARE GetStudent CURSOR FAST_FORWARD
FOR
SELECT Sno ,
Sname
FROM dbo.Student
--打开游标
OPEN GetStudent
--声明变量,用来存储游标数据
DECLARE @sno VARCHAR(36) ,
@sname VARCHAR(36)
--填充数据
FETCH NEXT FROM GetStudent INTO @sno, @sname
--只要游标检索到数据,就执行打印
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '学号:' + @sno + '姓名:' + @sname
FETCH NEXT FROM GetStudent INTO @sno, @sname
END
--关闭游标
CLOSE GetStudent
--释放游标
DEALLOCATE GetStudent
2.更新游标
--Student表,sno varchar(36),sname varchar(36)
--内置数据
--sno001 sname001
--sno002 sname002
--sno003 sname003
DECLARE GetStudent1 CURSOR
FOR
SELECT Sno ,
Sname
FROM dbo.Student FOR UPDATE;
OPEN GetStudent1
DECLARE @sno VARCHAR(36) ,
@sname VARCHAR(36)
FETCH NEXT FROM GetStudent1 INTO @sno, @sname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '学号:' + @sno + '姓名:' + @sname
UPDATE Student
SET Sname = '2017' + Sname
WHERE CURRENT OF GetStudent1
FETCH NEXT FROM GetStudent1 INTO @sno, @sname
END
CLOSE GetStudent1
DEALLOCATE GetStudent1
3.可滚动游标
--Student表,sno varchar(36),sname varchar(36)
--内置数据
--sno001 sname001
--sno002 sname002
--sno003 sname003
DECLARE GetStudent2 CURSOR SCROLL
FOR
SELECT Sno ,
Sname
FROM dbo.Student
OPEN GetStudent2
DECLARE @sno VARCHAR(36) ,
@sname VARCHAR(36)
FETCH FIRST FROM GetStudent2 INTO @sno, @sname
PRINT '【首行】 ' + '学号:' + @sno + '姓名:' + @sname
FETCH LAST FROM GetStudent2 INTO @sno, @sname
PRINT '【末行】 ' + '学号:' + @sno + '姓名:' + @sname
FETCH ABSOLUTE 2 FROM GetStudent2 INTO @sno, @sname
PRINT '【绝对定位第2行】 ' + '学号:' + @sno + '姓名:' + @sname
FETCH RELATIVE -1 FROM GetStudent2 INTO @sno, @sname
PRINT '【相对定位第-1行】 ' + '学号:' + @sno + '姓名:' + @sname
FETCH PRIOR FROM GetStudent2 INTO @sno, @sname
PRINT '【当前游标上一行】 ' + '学号:' + @sno + '姓名:' + @sname
FETCH NEXT FROM GetStudent2 INTO @sno, @sname
PRINT '【当前游标下一行】 ' + '学号:' + @sno + '姓名:' + @sname
CLOSE GetStudent2
DEALLOCATE GetStudent2