游标和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触发器的使用场景及方法实例)
- SQL SERVER获取指定数据库中所有存储过程的参数
- mysql为啥使用b+树(MySQL用B+树作为索引结构有什么好处)
- 常见NoSQL数据库
- sql语句的groupby是做什么的(深入浅析SQL中的group by 和 having 用法)
- mysqlinnodb有什么功能(Mysql技术内幕之InnoDB锁的深入讲解)
- mybatis测试出现空指针(Mybatis非配置原因,导致SqlSession was not registered for synchronization异常)
- sqlserver分表后如何查询(SQL Server中row_number分页查询的用法详解)
- docker部署tomcat(Docker安装Tomcat、MySQL和Redis的步骤详解)
- mssql 存储过程查询语句(MSSQL分页存储过程完整示例支持多表分页存储)
- mysql8.0.25.0安装配置(MySQL8.0.23免安装版配置详细教程)
- sql server中通过datename获取日期中部分数据
- 用mysql编写test数据库(MySQL制作具有千万条测试数据的测试库的方法)
- mysql查询很慢怎么回事(MySQL Like模糊查询速度太慢如何解决)
- sqlserver提供的内置函数(Sqlserver 自定义函数 Function使用介绍)
- mysql经典问题(MySQL null的一些易错点)
- 保温好 容量大 颜值高 保温杯你给娃娃买对了吗(保温好容量大颜值高)
- 《道德经》 人生避开骄狂,才能免去祸患(道德经人生避开骄狂)
- 郭麒麟(郭麒麟)
- 古人十句 戒骄 名言,醍醐灌顶,受益匪浅(古人十句戒骄名言)
- 《道德经》:功成不局,泰而不骄(道德经:功成不局)
- 每日一典 过江之鲫(每日一典过江之鲫)
热门推荐
- 云服务器最低配置可以吗(云服务器内存怎么选择?)
- pythonrequests爬虫使用教程(Python 通过requests实现腾讯新闻抓取爬虫的方法)
- iframe标签教程(关于解决iframe标签嵌套问题的解决方法)
- dedecms关闭站点(dedecms 会员登录或者退出直接跳转到首页的修改方法)
- mybatis如何插入空格字符串(MyBatis SQL xml处理小于号与大于号正确的格式)
- navicat怎么和mysql连接(Navicat Premium远程连接MySQL数据库的方法)
- 云服务器自建服务器成本比较(云服务器与服务器租用之间的区别在哪里?)
- linq not in 查询
- 安装apache服务失败怎么办(Apache安装后出现服务无法启动服务里启动出现错误代码1)
- dedecms添加板块(DedeCMS关键词替换问题较完美解决方法)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9