存储过程创建:
CREATE PROCEDURE `proc_cursor_test`()
BEGIN
#声明接收变量
declare v_id int(11);
declare v_unit_name varchar(30);
#游标变量
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT t.id,t.unit_name from t_camp_unit t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur; #打开游标
read_loop: LOOP
FETCH cur INTO v_id,
v_unit_name;
IF done THEN
LEAVE read_loop;
END IF;
-- 做你想做的事情
select v_unit_name;
END LOOP;
CLOSE cur;#关闭游标
END
注意
1、DECLARE done INT DEFAULT FALSE 不能放在游标变量之后,要放在游标变量之前,否则会报错
1337 - Variable or condition declaration after cursor or handler declaration
2、DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE 不能放在游标声明之前,要放在游标变量之后,否则会报错
1338 - Cursor declaration after handler declaration
3、一定要加上IF done THEN LEAVE read_loop; END IF;跳出循环,否则会死循环!