发布：2021/1/4 14:38:02作者：管理员 来源：本站 浏览次数：1301
类型：
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.
DECLARE @PIDInt int,@IUIDInt int,@ImgURL nvarchar(200),@priceStr nvarchar(100)
DECLARE cursor_name CURSOR FOR --定义游标
select pp.PIDInt,pp.IUIDInt,pp.ImgURL, a.priceStr from COM_Product pp left join
(
select * from DR_TMedicalCard t where t.IUIDInt=10069 and EXISTS(select * from COM_Product p where t.NameStr=p.NameStr ) and CardType<>3
) a on pp.NameStr=a.NameStr where sys_19='体检产品模板' and a.priceStr is not null
OPEN cursor_name --打开游标
FETCH NEXT FROM cursor_name INTO @PIDInt ,@IUIDInt ,@ImgURL ,@priceStr --抓取下一行游标数据
WHILE @@FETCH_STATUS = 0
BEGIN
--嵌套执行
DECLARE @x int
DECLARE @price nvarchar(100),@title nvarchar(100)
set @x=1
set @title='上岗前'
DECLARE cursor_name_1 CURSOR FOR --定义游标
select * from dbo.f_split(@priceStr,',')
OPEN cursor_name_1 --打开游标
FETCH NEXT FROM cursor_name_1 INTO @price --抓取下一行游标数据
WHILE @@FETCH_STATUS = 0
BEGIN
if @x=1
begin
set @title='上岗前'
end
if @x=2
begin
set @title='在岗期间'
end
if @x=3
begin
set @title='离岗时'
end
insert into COM_Product_Cate(PIDInt,IUIDInt,CateNameStr,Description,PriceStr,ImgURL,AddTime) values(@PIDInt,@IUIDInt,@title,@title,@price,@ImgURL, getdate())
PRINT '用户ID：'+@title+' '+'用户名：'+@price
set @x=@x+1
FETCH NEXT FROM cursor_name_1 INTO @price
END
CLOSE cursor_name_1 --关闭游标
DEALLOCATE cursor_name_1 --释放游标
PRINT '用户ID：'+@ImgURL+' '+'用户名：'+@priceStr
FETCH NEXT FROM cursor_name INTO @PIDInt ,@IUIDInt ,@ImgURL ,@priceStr
END
CLOSE cursor_name --关闭游标
DEALLOCATE cursor_name --释放游标
