SQL Server 游标基础使用

来源：互联网发布：经传证券炒股软件编辑：程序博客网时间：2024/05/19 23:17

游标基础学习

1.简单游标使用

--Student表，sno varchar(36),sname varchar(36)--内置数据--sno001sname001--sno002sname002--sno003sname003--定义简单循环游标DECLARE GetStudent CURSOR FAST_FORWARDFOR    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)--内置数据--sno001sname001--sno002sname002--sno003sname003DECLARE GetStudent1 CURSORFOR    SELECT  Sno ,            Sname    FROM    dbo.Student FOR UPDATE;OPEN GetStudent1DECLARE @sno VARCHAR(36) ,    @sname VARCHAR(36)FETCH NEXT FROM GetStudent1 INTO @sno, @snameWHILE @@FETCH_STATUS = 0    BEGIN        PRINT '学号：' + @sno + '姓名：' + @sname        UPDATE  Student        SET     Sname = '2017' + Sname        WHERE CURRENT OF GetStudent1        FETCH NEXT FROM GetStudent1 INTO @sno, @sname    ENDCLOSE GetStudent1DEALLOCATE GetStudent1

3.可滚动游标

--Student表，sno varchar(36),sname varchar(36)--内置数据--sno001sname001--sno002sname002--sno003sname003DECLARE GetStudent2 CURSOR SCROLLFOR    SELECT  Sno ,            Sname    FROM    dbo.Student OPEN GetStudent2DECLARE @sno VARCHAR(36) ,    @sname VARCHAR(36)FETCH FIRST FROM GetStudent2 INTO @sno, @snamePRINT '【首行】 ' + '学号：' + @sno + '姓名：' + @snameFETCH LAST FROM GetStudent2 INTO @sno, @snamePRINT '【末行】 ' + '学号：' + @sno + '姓名：' + @snameFETCH ABSOLUTE 2 FROM GetStudent2 INTO @sno, @snamePRINT '【绝对定位第2行】 ' + '学号：' + @sno + '姓名：' + @snameFETCH RELATIVE -1 FROM GetStudent2 INTO @sno, @snamePRINT '【相对定位第-1行】 ' + '学号：' + @sno + '姓名：' + @snameFETCH PRIOR FROM GetStudent2 INTO @sno, @snamePRINT '【当前游标上一行】 ' + '学号：' + @sno + '姓名：' + @snameFETCH NEXT FROM GetStudent2 INTO @sno, @snamePRINT '【当前游标下一行】 ' + '学号：' + @sno + '姓名：' + @snameCLOSE GetStudent2DEALLOCATE GetStudent2

阅读全文

0 0