MySQL存储过程---游标 什么是游标 游标的操作过程 cursor应用举例
保存select语句的数据集，主要用于对数据集逐行进行处理。
游标的操作过程
1、定义游标
DECLARE cur_name CURSOR FOR SELECT COLUMN FROM TABLE WHERE CONDITION;
2、打开游标
open cur_name;
3、从游标提取数据
FETCH cur_name INTO var1,var2,...;
4、游标的异常处理
declare done int default 1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 0;
5、关闭游标
close cur_name；
cursor应用举例
1、创建存储过程
DELIMITER $$ USE `world`$$ DROP PROCEDURE IF EXISTS `p_cur1`$$ CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_cur1`() BEGIN DECLARE u_id,u_a INT; DECLARE u_n,u_g VARCHAR(64); DECLARE cur1 CURSOR FOR SELECT id,NAME,age,gender FROM t4 WHERE id <5; OPEN cur1; FETCH cur1 INTO u_id,u_n,u_a,u_g; SELECT u_id,u_n,u_a,u_g; FETCH cur1 INTO u_id,u_n,u_a,u_g; SELECT u_id,u_n,u_a,u_g; FETCH cur1 INTO u_id,u_n,u_a,u_g; SELECT u_id,u_n,u_a,u_g; FETCH cur1 INTO u_id,u_n,u_a,u_g; SELECT u_id,u_n,u_a,u_g; CLOSE cur1; END$$ DELIMITER ;
调用
mysql> call p_cur1(); +------+----------+------+------+ | u_id | u_n | u_a | u_g | +------+----------+------+------+ | 1 | a4182e_3 | 27 | F | +------+----------+------+------+ 1 row in set (0.01 sec) +------+----------+------+------+ | u_id | u_n | u_a | u_g | +------+----------+------+------+ | 2 | a4187f_5 | 33 | M | +------+----------+------+------+ 1 row in set (0.01 sec) +------+----------+------+------+ | u_id | u_n | u_a | u_g | +------+----------+------+------+ | 3 | a418ba_7 | 34 | M | +------+----------+------+------+ 1 row in set (0.01 sec) +------+----------+------+------+ | u_id | u_n | u_a | u_g | +------+----------+------+------+ | 4 | a418dc_9 | 30 | F | +------+----------+------+------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec)
异常处理
当给定的条件不满足时，会抛出异常，比如上面的存储过程p_cur1，cursor获取的数据超过了表的行数，就会报错：
mysql> call p_cur1(); +------+----------+------+------+ | u_id | u_n | u_a | u_g | +------+----------+------+------+ | 1 | a4182e_3 | 27 | F | +------+----------+------+------+ 1 row in set (0.00 sec) +------+----------+------+------+ | u_id | u_n | u_a | u_g | +------+----------+------+------+ | 2 | a4187f_5 | 33 | M | +------+----------+------+------+ 1 row in set (0.00 sec) +------+----------+------+------+ | u_id | u_n | u_a | u_g | +------+----------+------+------+ | 3 | a418ba_7 | 34 | M | +------+----------+------+------+ 1 row in set (0.00 sec) +------+----------+------+------+ | u_id | u_n | u_a | u_g | +------+----------+------+------+ | 4 | a418dc_9 | 30 | F | +------+----------+------+------+ 1 row in set (0.00 sec) ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
改写存储过程，添加异常处理
DELIMITER $$ USE `world`$$ DROP PROCEDURE IF EXISTS `p_cur1`$$ CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_cur1`() BEGIN DECLARE u_id,u_a INT; DECLARE u_n,u_g VARCHAR(64); DECLARE done INT DEFAULT 1; DECLARE cur1 CURSOR FOR SELECT id,NAME,age,gender FROM t4 WHERE id <5; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=0; OPEN cur1; FETCH cur1 INTO u_id,u_n,u_a,u_g; ## 处理重复最后一条数据的问题 WHILE done=1 DO SELECT u_id,u_n,u_a,u_g; FETCH cur1 INTO u_id,u_n,u_a,u_g; END WHILE; CLOSE cur1; END$$ DELIMITER ;
调用
mysql> call p_cur1(); +------+----------+------+------+ | u_id | u_n | u_a | u_g | +------+----------+------+------+ | 1 | a4182e_3 | 27 | F | +------+----------+------+------+ 1 row in set (0.01 sec) +------+----------+------+------+ | u_id | u_n | u_a | u_g | +------+----------+------+------+ | 2 | a4187f_5 | 33 | M | +------+----------+------+------+ 1 row in set (0.01 sec) +------+----------+------+------+ | u_id | u_n | u_a | u_g | +------+----------+------+------+ | 3 | a418ba_7 | 34 | M | +------+----------+------+------+ 1 row in set (0.01 sec) +------+----------+------+------+ | u_id | u_n | u_a | u_g | +------+----------+------+------+ | 4 | a418dc_9 | 30 | F | +------+----------+------+------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec)