认真学习MySQL中游标与实例分析

【1】游标简介

游标，又称光标。实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理

游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。游标初始位置在before first，使用的时候是先移到下一行，然后再去数据。

概括来讲，SQL的游标是一种临时的数据库对象，即可以用来存放在数据库表中的数据行副本，也可以指向存储在数据库中的数据行的指针。游标提供了在逐行的基础上操作表中数据的方法。

游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由SELECT语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。

大部分程序数据设计语言都能使用游标来检索SQL数据库中的数据，在程序中嵌入游标和在程序中嵌入SQL语句相同

游标(cursor)的特性：

• ① 只读的，不能更新的。
• ② 不滚动的
• ③ 不敏感的，不敏感意为服务器可以或不可以复制它的结果表

游标(cursor)必须在声明处理程序之前被声明，并且变量和条件必须在声明游标或处理程序之前被声明。

游标实例

以下为一个存储过程实例，用到了游标和事务及异常处理。

CREATE PROCEDURE `my_cursor1`(out o_result int)

# begin 标号 zero_back
zero_back:BEGIN 
	#Routine body goes here...
	DECLARE p_age int; # 声明变量
	DECLARE p_count int;
	
	-- 声明游标结束判断变量，默认值为FALSE;
	DECLARE fetchSeqOk boolean DEFAULT FALSE;
	DECLARE my_cursor CURSOR for select age FROM t_user where id < 4; -- 定义游标
	
	//游标执行结束时将会set fetchSeqOk = true
	DECLARE CONTINUE HANDLER FOR NOT FOUND 
	set fetchSeqOk = true;
	-- 在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误，
	-- 所以在上面代码中指定了当引发not found错误时定义一个continue 的事件 
	
	# 定义sql异常执行语句
	DECLARE EXIT HANDLER FOR SQLEXCEPTION 
	BEGIN
		ROLLBACK; -- 如果有异常，进行事务回滚；如果调用存储过程的service，已经进行了事务处理，那么该存储过程不需要再重复开启事务
		set o_result = -500;
	end;
	
	# 如果表中无数据，直接跳出函数并设置o_result为-1; 
	select count(*) into p_count from t_user where id <4;
	IF p_count < 1 THEN
		SET o_result = -1;
		LEAVE zero_back; 
	END IF;

	# 打开游标
	OPEN my_cursor;
	   START TRANSACTION;-- 开启事务，如果service里面已经开启了事务，那么存储过程中不需要再开事务；事务要在循环外面开启！！！

		 loop_cursor:LOOP
				fetch my_cursor into p_age; -- 游标改变位置指向下一行，取下一行数据，初始位置在before first row
				
				IF fetchSeqOk THEN
					LEAVE loop_cursor;
				END IF;
		   	-- fetch my_cursor into p_age; 此处位置不对，将会重复插入游标最后一个值		
				
				insert into t_user (age) VALUES(p_age);
				-- 现在游标行位置未变
				
		END LOOP loop_cursor;
		-- 如果给loop增加了标号，那么请使用END LOOP loop_cursor;进行循环关闭。否则直接END LOOP

		COMMIT; -- 正常执行完，commit

		set o_result = 0;

	CLOSE my_cursor;	-- 关闭游标

END

---

【2】实例分析

上面示例已经演示了游标的定义，打开，读取值和关闭游标，其中用到了loop循环和事务以及异常处理。这里说明上面示例中需要注意事项。

事务开启关闭相对应

缺一不可。如果调用存储过程的service使用了事务，那么该存储过程中不用开启事务。事务尽量尽量在循环外面。

---

① 定义异常处理

DECLARE EXIT HANDLER FOR SQLEXCEPTION -- 定义sql异常
	BEGIN
		ROLLBACK; -- 如果有异常，进行事务回滚；如果调用存储过程的service，已经进行了事务处理，那么该存储过程不需要再重复开启事务
		set o_result = -500;
	end;

② 标号应用

标号可以用在begin，repeat，while或者loop语句前，语句标号只能在合法的语句前面使用。

可以跳出循环，使运行指令达到复合语句的最后一步。

loop_cursor:LOOP
LEAVE loop_cursor;

③ loop的关闭两种形式

loop

...///

end loop;

# or 采用标号

loop_LABLES:loop

...//

end loop loop_LABLES;

④ 声明游标

DECLARE cursor_name CURSOR FOR select_statement ;

# select_statement 指一个select语句

这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。SELECT语句不能有INTO子句。

⑤ 游标打开和关闭

# 打开游标
OPEN my_cursor;

# 关闭游标
CLOSE my_cursor;	

CLOSE my_cursor;这个语句关闭先前打开的光标。 如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。

⑥ fetch是获取游标当前指向的数据行

fetch是获取游标当前指向的数据行，并将指针指向下一行，当游标已经指向最后一行时继续执行会造成游标溢出。

游标改变位置指向下一行，取下一行数据，初始位置在before first row 。

fetch my_cursor into p_age; 

在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误，所以在代码中指定了当引发not found错误时定义一个continue 的事件

DECLARE CONTINUE HANDLER FOR NOT FOUND  set fetchSeqOk = true;

游标获取多个列

# 获取属性表的所有code、category_code
DECLARE my_cursor CURSOR for select  code,category_code FROM sys_attribute;

# 取出两个列
fetch my_cursor into curr_code,curr_category; 

【3】游标与动态SQL结合

如下是一个删除某个数据库某个表的索引存储过程，其使用用户变量组装动态SQL。

DELIMITER //
CREATE  PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
       DECLARE done INT DEFAULT 0;
       DECLARE ct INT DEFAULT 0;
       DECLARE _index VARCHAR(200) DEFAULT '';
       DECLARE _cur CURSOR FOR  SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND    index_name <>'PRIMARY'  ;
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
       DECLARE  CONTINUE HANDLER FOR NOT FOUND SET done=2 ;      
#若没有数据返回,程序继续,并将变量done设为2
        OPEN _cur;
        FETCH _cur INTO _index;
        WHILE  _index<>'' DO 
               SET @str = CONCAT("drop index " , _index , " on " , tablename ); 
               PREPARE sql_str FROM @str ;
               EXECUTE  sql_str;
               DEALLOCATE PREPARE sql_str;
               SET _index=''; 
               FETCH _cur INTO _index; 
        END WHILE;
   CLOSE _cur;
END //
DELIMITER ;