游标和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游标用法的资料请关注开心学习网其它相关文章!
- sqlserver游标使用场景(解析SQL Server聚焦移除Bookmark Lookup、RID Lookup、Key Lookup)
- mysql权限收回(MySQL如何利用DCL管理用户和控制权限)
- sql server作业的调度信息中各列的含义
- centos安装sql server(Centos 7.3下SQL Server安装配置方法图文教程)
- docker创建mysql环境(docker上部署MySQL的示例)
- sqlserver语句创建窗口布局(SQL Server 2012 开窗函数)
- 对mysql性能优化的看法(聊聊MySQL的COUNT的性能,看看怎么最快?)
- sql常遇到的问题(SQL语句执行超时引发网站首页访问故障问题)
- mysql 时间戳获取(MySQL时间盲注的五种延时方法实现)
- mysql的简单介绍(MySQL Shell的介绍以及安装)
- sqlserver索引介绍(浅析SQL Server的聚焦使用索引和查询执行计划)
- mysql安装失败原因和解决方法(MySQL MGR搭建过程中常遇见的问题及解决办法)
- mysql怎么看脱机数据(MYSQL电话号码,身份证数据脱敏的实现)
- 查看SQL SERVER中某个查询用了多少TempDB空间
- 安装SQL Server 2016出错提示:需要安装oracle JRE7 更新 51(64位)或更高版本问题的解(安装SQL Server 2016出错提示:需要安装oracle JRE7 更新 5164位或更高版本问题的解)
- sql提交事务代码(SQL SERVER提交事务回滚机制)
- 今日大雪,大雪养生这样做,一年都有好气色(大雪养生这样做)
- 粗盐是什么 粗盐的功效与作用(粗盐的功效与作用)
- 会 吃人 的客机 从天堂到地狱只需5分钟,图-104如何做到(会吃人的客机)
- 男人犯的错,为什么要女人来承担(为什么要女人来承担)
- 心理学 四个金蟾,哪个最招财 测你今生的运势有多棒(心理学四个金蟾)
- 吉善缘《聚宝金蟾》金蟾招财摆件 三足全铜蟾蜍客厅店铺开业礼品(吉善缘聚宝金蟾金蟾招财摆件)
热门推荐
- html5 webrtc技术详解(Html5 webRTC简单实现视频调用的示例代码)
- dedecmsv6如何安装(dedecms 下载地址加迅雷专用链的操作方法 比较全)
- linux部署flask项目(用uWSGI和Nginx部署Flask项目的方法示例)
- mysql读写分离代码实现(MySQL Router实现MySQL的读写分离的方法)
- html中hr标签
- sqlserver技术文档(sql server2016里面的json功能浅析)
- canvas绘图问题(记一次高分屏下canvas模糊问题)
- react路由原理解析(React配置子路由的实现)
- 阿里云盘8月永久激活码(再分享6个可无限激活阿里云盘邀请码,不信你不能激活阿里云盘)
- 升级mysql客户端版本(超详细教你怎么升级Mysql的版本)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9