===sqlserver cursor:
---语法:
--创建游标:
- DECLARE cursor_name CURSOR [LOCAL|GLOBAL] [FORWARD_ONLY|SCROLL] [STATIC|KEYSET|DYNAMIC] [READ_ONLY|SCROLL_LOCKS]
- FOR selet_statement [FOR UPDATE[of 列名[,列名]]
- 注:LOCAL 局部游标 GLOBAL 全局游标
- FORWARD_ONLY 仅向前 SCROLL 滚动
- STATIC 静态 KEYSET 键集 DYNAMIC 动态
- READ_ONLY 只读 SCROLL_LOCKS 锁定游标当前行
--获取游标的数据:
- FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar|RELATIVE{n|@nvar}}]
- From cursor_name [into 变量]
- 注:NEXT 下一行 PRIOR 上一行 FIRST 第一行
- LAST 最后一行 ABSOLUTE n 第n行
- RELATIVE n 当前位置开始的第n行
- into 变量 把当前行的各字段值赋值给变量
--游标状态变量:
- @@fetch_status 游标状态
- 0 成功 -1 失败 -2 丢失
- @@cursor_rows 游标中结果集中的行数
- n 行数 -1 游标是动态的 0 空集游标
--操作游标的当前行:
current of 游标名
--
(1) 尽管使用游标比较灵活,可以实现对数据集中单行数据的直接操作,但游标会在下面几个方面影响系统的性能:
--使用游标会导致页锁与表锁的增加
--导致网络通信量的增加
-增加了服务器处理相应指令的额外开销
(2) 使用游标时的优化问题:
--明确指出游标的用途:for read only或for update
--在for update后指定被修改的列
如果能不用游标,尽量不要使用游标;用完用完之后一定要关闭和释放
尽量不要在大量数据上定义游标;尽量不要使用游标上更新数据
尽量不要使用insensitive, static和keyset这些参数定义游标
如果可以,尽量使用FAST_FORWARD关键字定义游标
如果只对数据进行读取,当读取时只用到FETCH NEXT选项,则最好使用FORWARD_ONLY参数
---sample:
游标经常会和全局变量@@FETCH_STATUS与WHILE循环来共同使用,以达到遍历游标所在数据集的目的
- CREATE TABLE t_cursor(id INT,num INT);
- INSERT INTO t_cursor VALUES(1,11),(2,12),(3,13),(4,14),(5,15);
- DECLARE @id INT,@num INT;
- DECLARE c_row CURSOR FOR SELECT id,num FROM t_cursor; --定义游标
- OPEN c_row -- 打开游标
- FETCH NEXT FROM c_row INTO @id,@num; --初始游标数据,放入变量中
- WHILE @@fetch_status=0 --循环判断游标读取状态
- BEGIN
- SELECT @id as id, @num as num;
- FETCH NEXT FROM c_row INTO @id,@num; --读取游标下一行
- END
- CLOSE c_row; --关闭游标
- DEALLOCATE c_row; --摧毁游标
===mysql cursor:
http://blog.csdn.net/anialy/article/details/8106370
--语法:
注意MySQL的游标是向前只读的,也就是说,你只能顺序地从开始往后读取结果集,不能从后往前,也不能直接跳到中间的记录.
所以读取mysql游标:FETCH cursor_name INTO 变量; -- 不同于sqlserver的NEXT FROM
mysql不同于sqlserver有@@fetch_status变量,其游标读取结束的标识须另外declare变量进行处理;
如:DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; --若没有数据返回,程序继续,并将变量done设为true
或者:DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET n = NULL;
而且mysql的循环体处理方式也不同于sqlserver,可选择repeat,while,loop三种循环体来读取游标,对应不同的结束处理。
--方式1:repeat循环
- CREATE TABLE t_cursor(num INT);
- INSERT INTO t_cursor VALUES(11),(12),(13),(14),(15);
- DELIMITER $$
- DROP PROCEDURE IF EXISTS p_curosr$$
- CREATE PROCEDURE p_curosr()
- BEGIN
- DECLARE n INT;
- DECLARE done INT DEFAULT 0; -- 定义int结束标识
- DECLARE c_row CURSOR FOR SELECT num FROM t_cursor;
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 捕捉异常,设置结束标识
- OPEN c_row;
- REPEAT -- repeat循环
- FETCH c_row INTO n; -- 语法不同于sqlserver,不用先初始fetch next
- IF NOT done THEN -- 如果没有结束
- SELECT n;
- END IF;
- UNTIL done END REPEAT; -- 结束循环
- CLOSE c_row;
- -- DEALLOCATE c_row;
- END$$
- -- 调用
- SELECT * FROM t_cursor;
- CALL p_curosr();
--方式2:loop循环
- CREATE TABLE t_cursor(num INT);
- INSERT INTO t_cursor VALUES(11),(12),(13),(14),(15);
- DELIMITER $$
- DROP PROCEDURE IF EXISTS p_curosr$$
- CREATE PROCEDURE p_curosr()
- BEGIN
- DECLARE n INT;
- DECLARE done BOOL DEFAULT FALSE; -- 定义bool结束标识
- DECLARE c_row CURSOR FOR SELECT num FROM t_cursor;
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 捕捉异常,设置结束标识
- OPEN c_row;
- cursorloop: LOOP -- loop循环
- FETCH c_row INTO n; -- 语法不同于sqlserver,不用先初始fetch next
- IF NOT done THEN -- 如果没有结束
- SELECT n;
- ELSE
- LEAVE cursorloop; -- 结束循环
- END IF;
- END LOOP cursorloop;
- CLOSE c_row;
- -- DEALLOCATE c_row;
- END$$
- -- 调用
- SELECT * FROM t_cursor;
- CALL p_curosr();
--方式3:while循环
- CREATE TABLE t_cursor(num INT);
- INSERT INTO t_cursor VALUES(11),(12),(13),(14),(15);
- DELIMITER $$
- DROP PROCEDURE IF EXISTS p_curosr$$
- CREATE PROCEDURE p_curosr()
- BEGIN
- DECLARE n INT;
- DECLARE c_row CURSOR FOR SELECT num FROM t_cursor;
- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET n = NULL; -- 捕捉异常,设置null
- OPEN c_row;
- FETCH c_row INTO n; -- 初始游标
- WHILE(n IS NOT NULL) DO -- while循环
- SELECT n;
- FETCH c_row INTO n; -- 读取游标
- END WHILE; -- 结束循环
- CLOSE c_row;
- -- DEALLOCATE c_row;
- END$$
- -- 调用
- SELECT * FROM t_cursor;
- CALL p_curosr();