应用场景痛点
| 场景类型 | 技术痛点 | 游标解决方案 | 代码特征关键词 |
|---|---|---|---|
| 分页查询 | 万级数据全量加载卡顿 | 流水线逐行读取+内存常驻 |
OFFSET FETCH @@FETCH_STATUS
|
| 批量更新 | 条件计算依赖前序结果 | 状态跟踪+即时更新 | WHERE CURRENT OF |
| 树形结构遍历 | 层级关系SQL难以表达 | 递归回溯+路径拼接 |
WHILE循环+父节点查询 |
| 复杂数据转换 | 原始日志解析困难 | 逐行解析+结构化存储 |
SUBSTRING PARSE
|
| 事务分阶段处理 | 原子性操作难以保证 | 阶段标记+状态控制 |
BEGIN TRANSACTION COMMIT
|
image.png
| MemberId | MemberAccount | MemberPwd | MemberNickname | MemberPhone |
|---|---|---|---|---|
| 1 | liubei | 123456 | 刘备 | 4659874564 |
| 2 | guanyu | 123456 | 关羽 | 42354234124 |
| 3 | zhangfei | 123456 | 张飞 | 41253445 |
| 4 | zhangyun | 123456 | 赵云 | 75675676547 |
| 5 | machao | 123456 | 马超 | 532523523 |
创建游标 打开游标 使用游标 关闭游标 删除游标
declare mycur cursor scroll
scroll 滚动游标，可以向上向下滚动
如果没有只能向上滚动
声明游标for关键字
-- 创建游标
declare mycur cursor scroll
for select Memberaccount from member
-- 打开游标
open mycur
-- 提取某行数据
fetch first from mycur
fetch last from mycur
--提取指定行数据
fetch absolute 2 from mycur
-- 当前行下移行 偏移用法
fetch relative 2 from mycur
-- 下一个 上一个
fetch next from mycur
fetch prior from mycur
-- 提取游标数据存变量，并查询
declare @acc varchar(20)
fetch absolute 2 from mycur into @acc
select * from MEMBER where MemberAccount= @acc
-- 遍历游标
declare @acc varchar(20)
fetch absolute 1 from mycur into @acc
--@@fetch_status 0 提取成功 -1 提取失败 -2提取内容不存在
while @@fetch_status = 0
begin
print '提取' + @acc
fetch next from mycur into @acc
end
-- 利用游标进行数据修改删除
select * from member
fetch absolute 2 from mycur
update MEMBER set MemberPwd='654321' where current of mycur
fetch absolute 2 from mycur
delete from MEMBER where current of mycur
-- 游标指向结果集多列
--关闭游标
close mycur
-- 删除游标
deallocate mycur
image.png
-- 游标指向结果集多列
select * from member
declare mycur cursor scroll
for select Memberaccount,MemberPwd,MemberNickname from member
open mycur
-- 遍历游标
declare @acc varchar(20)
declare @pwd varchar(20)
declare @nickname varchar(20)
fetch absolute 1 from mycur into @acc,@pwd,@nickname
--@@fetch_status 0 提取成功 -1 提取失败 -2提取内容不存在
while @@fetch_status = 0
begin
print '提取' + @acc+@pwd+@nickname
fetch next from mycur into @acc
end
