sql server游标
语法:
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
参数 [ LOCAL | GLOBAL ] :
LOCAL :定义局部游标
GLOBAL :定义全局游标
参数 [ FORWARD_ONLY | SCROLL ] :
FORWARD_ONLY :(默认)从数据集开始到数据集结束的方向读取,只支持FETCH NEXT
eg:
创建一个自定义函数,统计users表中name为张三的人,返回数量。
CREATE FUNCTION [dbo].[ceshi]
(
)
RETURNS INT
AS
BEGIN
DECLARE @c INT = 0
DECLARE @id INT
DECLARE @name VARCHAR(50)
DECLARE try_cursor CURSOR FOR (SELECT id,name FROM users)
OPEN try_cursor
WHILE @@FETCH_STATUS = 0 #循环结果集必须加这一句,一般与FETCH NEXT FROM配合使用
BEGIN
FETCH NEXT FROM try_cursor INTO @id,@name
IF @name = '张三'
BEGIN
SET @c = @c + 1
END
END
CLOSE try_cursor
DEALLOCATE try_cursor
RETURN @c
END
SCROLL :支持游标向任意方向移动
eg:
返回第9行用户的姓名
CREATE FUNCTION [dbo].[ceshi]
(
)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @c INT = 0
DECLARE @id INT
DECLARE @name VARCHAR(50)
DECLARE try_cursor CURSOR SCROLL FOR (SELECT id,name FROM users)
OPEN try_cursor
BEGIN
FETCH ABSOLUTE 9 FROM try_cursor INTO @id,@name
END
CLOSE try_cursor
DEALLOCATE try_cursor
RETURN @name
END
参数 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] :
STATIC:当游标被创建时,会自动缓存结果集,被执行的结果集不会受到其他数据修改的影响(比如在执行游标的过程中,数据修改不会影响到游标循环体的执行)。
eg:
ID | name |
---|---|
1 | 张三 |
2 | 张三 |
3 | 李四 |
4 | 张三 |
5 | 王五 |
6 | 张三 |
7 | 张三 |
8 | 张三 |
9 | 张三 |
下面的存储过程统计users表中name为‘张三‘的人员的数量,在游标循环体中,在循环ID为1的数据时把ID=9的name做修改,执行结果num = 7
ALTER PROCEDURE [dbo].[ceshi]
@num INT output
AS
BEGIN
DECLARE @c INT = 0
DECLARE @cout INT
DECLARE @id INT
DECLARE @name VARCHAR(50)
DECLARE try_cursor CURSOR SCROLL STATIC FOR (SELECT ID,name FROM users)
SELECT @cout = count(*) FROM users
OPEN try_cursor
WHILE @cout > 0
BEGIN
FETCH NEXT FROM try_cursor INTO @id,@name
IF(@name = '张三')
BEGIN
SET @c = @c + 1
END
IF(@id = '1')
BEGIN
UPDATE users SET name = '李四' WHERE ID = 9
END
SET @cout = @cout - 1
END
SET @num = @c
CLOSE try_cursor
DEALLOCATE try_cursor
END
执行存储过程,获得查询条数:
DECLARE @num int
EXEC dbo.ceshi @num output
SELECT @num # 结果:num = 6
DYNAMIC:与STATIC相反,当游标被创建时,被执行的结果集会受到其他数据修改的影响(比如在执行游标的过程中,数据修改会影响到游标循环体的执行)。
还是上面的表,把关键字STATIC改为DYNAMIC,执行结果num = 6。
ALTER PROCEDURE [dbo].[ceshi]
@num INT output
AS
BEGIN
DECLARE @c INT = 0
DECLARE @cout INT
DECLARE @id INT
DECLARE @name VARCHAR(50)
DECLARE try_cursor CURSOR SCROLL DYNAMIC FOR (SELECT ID,name FROM users)
SELECT @cout = count(*) FROM users
OPEN try_cursor
WHILE @cout > 0
BEGIN
FETCH NEXT FROM try_cursor INTO @id,@name
IF(@name = '张三')
BEGIN
SET @c = @c + 1
END
IF(@id = '1')
BEGIN
UPDATE users SET name = '李四' WHERE ID = 9
END
SET @cout = @cout - 1
END
SET @num = @c
CLOSE try_cursor
DEALLOCATE try_cursor
END
执行存储过程,获得查询条数:
DECLARE @num int
EXEC dbo.ceshi @num output
SELECT @num # 结果:num = 6
KEYSET :当游标被创建时,会自动缓存结果集的主键,若修改非主键信息,则游标中能获取到修改后的信息,若修改主键信息,游标就获取不到数据了。
参数 [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] :
READ_ONLY:意味着声明的游标只能读取数据,游标不能做任何更新操作
SCROLL_LOCKS:是另一种极端,将读入游标的所有数据进行锁定,防止其他程序进行更改,以确保更新的绝对成功
OPTIMISTIC:是相对比较好的一个选择,OPTIMISTIC不锁定任何数据,当需要在游标中更新数据时,如果底层表数据更新,则游标内数据更新不成功,如果,底层表数据未更新,则游标内表数据可以更新。
FETCH [ NEXT | LAST | … ] FROM cursor_name INTO @a,@b
当 [ FORWARD_ONLY | SCROLL ] 选择 SCROLL 时,才有以下参数,FORWARD_ONLY 只支持 NEXT
#取下一行
FETCH NEXT FROM try_cursor INTO @a
#取最后一行
FETCH LAST FROM try_cursor INTO @a
#取第一行
FETCH FIRST FROM try_cursor INTO @a
#取上一行
FETCH PRIOR FROM try_cursor INTO @a
#取第三行
FETCH ABSOLUTE 3 FROM try_cursor INTO @a
#取相对目前来说上一行
FETCH RELATIVE -1 FROM try_cursor INTO @a
CLOSE:关闭游标
CLOSE 游标名称
DEALLOCATE:释放游标
DEALLOCATE 游标名称
Mysql游标
DELIMITER $$
CREATE
FUNCTION youbiao()
RETURNS VARCHAR(20)
BEGIN
DECLARE cnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE g VARCHAR(20);
DECLARE num INT DEFAULT 0;
DECLARE idCur CURSOR FOR SELECT gname FROM `grade`; #创建游标
SELECT COUNT(*) INTO cnt FROM `grade`;
OPEN idCur; #开启游标
REPEAT #开始循环
FETCH idCur INTO g; #游标复制
IF g = '一年级' THEN SET num = num + 1; END IF;
SET i:= i+1;
UNTIL i >= cnt END REPEAT; #判断是否结束循环,循环次数与结果集条数对比
CLOSE idCur; #关闭游标
RETURN num;
END$$
DELIMITER ;
参考资料: sql server 游标的使用方法