游标和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在AlwaysOn中使用内存表的“踩坑”记录)
- sql中where和having可以同时用吗(SQL where条件和jion on条件的详解及区别)
- sqlserver语句创建窗口布局(SQL Server 2012 开窗函数)
- innodb和myisam(Mysql InnoDB和MyISAM区别原理解析)
- sql取整数函数(SQL中位数函数实例)
- windows docker 使用数据库教程(docker安装并持久化postgresql数据库的操作步骤)
- 通过接口访问mysql数据库(使用mysql记录从url返回的http GET请求数据操作)
- sqlserver使用简介(SQL Server Page结构深入分析)
- SQL语句分析工具Plan Explorer
- mysql的连接代码(MySQL 连接查询的原理和应用)
- mysql常见的存储引擎(如何选择MySQL的存储引擎?)
- mysql主从配置教程(MySQL大库搭建主从的一种思路分享)
- sql server日志文件过大怎么办
- sqlcount优化(SQL优化教程之in与range查询)
- mysql查询时间格式化(MySQL时间格式化)
- mysql读写分离怎么做(MySQL读写分离,写完读不到问题如何解决)
- 超撩人治愈的绝美水彩,原来出自她之手 一笔一画令无数人沉醉(超撩人治愈的绝美水彩)
- 新手的勾线(新手的勾线)
- ()
- 书法欣赏 宋.志南诗《绝句》(宋.志南诗绝句)
- 每周一首古诗 《绝句》(每周一首古诗绝句)
- 蓝色代表什么(蓝色代表什么性格的人)
热门推荐
- html5canvas案例(h5使用canvas画布实现手势解锁)
- js限制用户(如何用JS追踪用户)
- python3常用内建函数(Python3中函数参数传递方式实例详解)
- 云服务器和物理服务器有什么区别(云服务器是什么?云服务器有什么优势?)
- 小程序canvas不显示(小程序canvas中文字设置居中锚点)
- phpstudy修改为什么进不去(phpstudy2018升级后站点及phpmyadmin打开404解决方案)
- C# Windows服务的创建、安装、调试
- 虚拟主机哪里有免备案(免备案云主机的真假鉴别)
- php排序代码详解(PHP实现数据四舍五入的方法小结4种方法)
- win10下安装mysql8.0.23 及 “服务没有响应控制功能”问题解决办法(win10下安装mysql8.0.23 及 “服务没有响应控制功能”问题解决办法)