游标和sql语句区别(详解SQL游标的用法)
游标和sql语句区别
详解SQL游标的用法类型:
1.普通游标 只有NEXT操作
2.滚动游标 有多种操作
1.普通游标
DECLARE @username varchar(20),@UserId varchar(100) DECLARE cursor_name CURSOR FOR --定义游标 SELECT TOP 10 UserId,UserName FROM UserInfo ORDER BY UserId DESC OPEN cursor_name --打开游标 FETCH NEXT FROM cursor_name INTO @UserId,@username --抓取下一行游标数据 WHILE @@FETCH_STATUS = 0 BEGIN PRINT '用户ID:'+@UserId+' '+'用户名:'+@username FETCH NEXT FROM cursor_name INTO @UserId,@username END CLOSE cursor_name --关闭游标 DEALLOCATE cursor_name --释放游标
结果:
用户ID:zhizhi 用户名:邓鸿芝
用户ID:yuyu 用户名:魏雨
用户ID:yujie 用户名:李玉杰
用户ID:yuanyuan 用户名:王梦缘
用户ID:YOUYOU 用户名:lisi
用户ID:yiyiren 用户名:任毅
用户ID:yanbo 用户名:王艳波
用户ID:xuxu 用户名:陈佳绪
用户ID:xiangxiang 用户名:李庆祥
用户ID:wenwen 用户名:魏文文
2.滚动游标
--带SCROLL选项的游标 SET NOCOUNT ON DECLARE C SCROLL CURSOR FOR --SCORLL 后,有了更多的游标操作(滚动游标) SELECT TOP 10 UserId,UserName FROM UserInfo ORDER BY UserId DESC OPEN C FETCH LAST FROM C --最后一行的数据,并将当前行为指定行 FETCH ABSOLUTE 4 FROM C --从第一行开始的第4行数据,并将当前行为指定行 这里的n可正可负,n>0 往下翻,n<0 往上翻 FETCH RELATIVE 3 FROM C --相对于当前行的后3行数据,并将当前行为指定行 这里的n可正可负 FETCH RELATIVE -2 FROM C --相对于当前行的前2行数据,并将当前行为指定行 FETCH PRIOR FROM C ----相对于当前行的前1行数据 FETCH FIRST FROM C --刚开始第一行的数据,并将当前行为指定行 FETCH NEXT FROM C --相对于当前行的后1行数据 CLOSE C DEALLOCATE C
结果(可以参考第一个结果分析):
具体FETCH用法:
FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ] FROM ] { { [ GLOBAL ] cursor_name } | @cursor_variable_name } [ INTO @variable_name [ ,...n ] ]
Arguments
NEXT
Returns the result row immediately following the current row and increments the current row to the row returned. If FETCH NEXT is the first fetch against a cursor, it returns the first row in the result set. NEXT is the default cursor fetch option.
PRIOR
Returns the result row immediately preceding the current row, and decrements the current row to the row returned. If FETCH PRIOR is the first fetch against a cursor, no row is returned and the cursor is left positioned before the first row.
FIRST
Returns the first row in the cursor and makes it the current row.
LAST
Returns the last row in the cursor and makes it the current row.
ABSOLUTE { n| @nvar}
If n or @nvar is positive, returns the row n rows from the front of the cursor and makes the returned row the new current row. If n or @nvar is negative, returns the row n rows before the end of the cursor and makes the returned row the new current row. If n or @nvar is 0, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.
RELATIVE { n| @nvar}
If n or @nvar is positive, returns the row n rows beyond the current row and makes the returned row the new current row. If n or @nvar is negative, returns the row n rows prior to the current row and makes the returned row the new current row. If n or @nvar is 0, returns the current row. If FETCH RELATIVE is specified with n or @nvar set to negative numbers or 0 on the first fetch done against a cursor, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.
GLOBAL
Specifies that cursor_name refers to a global cursor.
cursor_name
Is the name of the open cursor from which the fetch should be made. If both a global and a local cursor exist with cursor_name as their name, cursor_name to the global cursor if GLOBAL is specified and to the local cursor if GLOBAL is not specified.
@cursor_variable_name
Is the name of a cursor variable referencing the open cursor from which the fetch should be made.
INTO @variable_name[ ,...n]
Allows data from the columns of a fetch to be placed into local variables. Each variable in the list, from left to right, is associated with the corresponding column in the cursor result set. The data type of each variable must either match or be a supported implicit conversion of the data type of the corresponding result set column. The number of variables must match the number of columns in the cursor select list.
以上就是详解SQL游标的用法的详细内容,更多关于SQL游标用法的资料请关注开心学习网其它相关文章!
- 宝塔mysql怎么设置优化(宝塔面板mysql内存占用高如何优化)
- mysql 删除数据回收空间(浅谈为什么MySQL不建议delete删除数据)
- python实现sql脚本规范(基于Python的SQL Server数据库实现对象同步轻量级)
- SQL语句查询SQL SERVER作业的调度信息
- sql数据拆分插入另一个表(Sql Server数据把列根据指定内容拆分数据的方法实例)
- mysql操作时间的函数(一篇文章搞定Mysql日期时间函数)
- sqlcount优化(SQL优化教程之in与range查询)
- sql server性能调优(SQL Server性能调优之缓存)
- 阿里云sql server 2012(远程连接阿里云SqlServer 2012 数据库服务器的图文教程)
- 简单的sql注入举例(分享一个简单的sql注入)
- 如何在mysql中批量插入数据(MySQL如何快速批量插入1000w条数据)
- mysql数据库触发器(MySQL中使用游标触发器的方法)
- sqlserver 空间数据类型(SQL Server数据类型转换方法)
- plsql常用函数
- sqlserver2012海量数据查询(SQL Server 2012 多表连接查询功能实例代码)
- 2021-10-18 11:58:15
- 追连续剧,品古今联4 明代三杨,联妙诗佳(追连续剧品古今联4)
- 三杨 共辅四朝帝王,构建明帝国内阁行政圈(三杨共辅四朝帝王)
- 红色文化进国企(红色文化进国企)
- 车友的选择| 轮毂该如何选(车友的选择轮毂该如何选)
- 秦海璐炫耀和王新军热恋蜜事,不料对方吐槽她吃饱后肚子撅老高(秦海璐炫耀和王新军热恋蜜事)
- 秦海璐一袭旗袍惹人倾心,将高级与淡雅展现的游刃有余(秦海璐一袭旗袍惹人倾心)
热门推荐
- python time模块记录时间(Python Datetime模块和Calendar模块用法实例分析)
- python3 代码解读(Python3列表内置方法大全及示例代码小结)
- python面向对象编写案例(Python面向对象程序设计示例小结)
- 小鸟云服务器免流搭建(小鸟云服务器如何进行数据盘挂载?Windows)
- linux中ipconfig命令的功能(Linux ipcs命令的使用)
- 微信小程序简易计算器制作(微信小程序实现简单计算器功能)
- mysql 分库分表步骤(MySQL读多写少设计方案 - 分库分表还能这么做?)
- mysql用什么类型数据(MySQL 数据类型选择原则)
- 安装SQL Server 2016出错提示:需要安装oracle JRE7 更新 51(64位)或更高版本问题的解(安装SQL Server 2016出错提示:需要安装oracle JRE7 更新 5164位或更高版本问题的解)
- laravel查询限制输出设置(laravel validate 设置为中文的例子验证提示为中文)