DROP PROCEDURE IF EXISTS aa;
CREATE PROCEDURE aa()
BEGIN
DECLARE ksum int DEFAULT 0;
DECLARE done int default 0;
DECLARE attndate VARCHAR(16);#定义光标
DECLARE _Cur CURSOR FOR
SELECT attndate,ksum from cs_code where 1=1;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;#打开光标
OPEN _Cur;
#循环执行
REPEAT
FETCH _Cur INTO attndate,ksum;
IF NOT done THEN
SELECT done;
SELECT ksum;
SELECT attndate;
END IF;
UNTIL done END REPEAT; #当done=1时退出被循
#关闭光标
CLOSE _Cur;
#只有定义为0,新的循环才能继续。
SET done = 0;
END;CALL aa();cs_code 有31条记录,为什么就执行了一次就退出了呢?跟mysql版本有关系吗?5.1.46-community
一个cs_code表测试,ksum int,attndate varchar(20).
然后执行DROP PROCEDURE IF EXISTS CirculationStatistics;
CREATE PROCEDURE CirculationStatistics()
BEGINDECLARE j int;
DECLARE i int default 0;
DECLARE k int DEFAULT 0;
DECLARE ksum int DEFAULT 0;
DECLARE _done int default 0;
DECLARE statetime VARCHAR(20);
DECLARE endtime VARCHAR(20);
DECLARE attndate VARCHAR(16);
DECLARE YearMonth VARCHAR(16);#获取本月的天数
SELECT DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) INTO j from dual;
#select j;#获取本月第一天
select DATE_ADD(curdate(),interval -day(curdate())+1 day) into statetime;
#select statetime;#获取下个月的第一天
select DATE_ADD(curdate()-day(curdate())+1,interval 1 month ) into endtime;
#select endtime;#截取当前年月
select LEFT(curdate(),7) into YearMonth;
#select YearMonth;WHILE i < j DO
IF i < 9 THEN
INSERT INTO cs_code VALUES (concat(YearMonth, '-0',(i + 1)),0);
ELSE
INSERT INTO cs_code VALUES (concat(YearMonth, '-',(i + 1)),0);
END IF;
SET i = i + 1;
END WHILE;
END
;
CALL CirculationStatistics();
然后再弄一个存储过程,测试就是不行DROP PROCEDURE IF EXISTS aa;
CREATE PROCEDURE aa()
BEGIN
DECLARE ksum int DEFAULT 0;
DECLARE done int default 0;
DECLARE attndate VARCHAR(16);#定义光标
DECLARE _Cur CURSOR FOR
SELECT attndate,ksum from cs_code where 1=1;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;#打开光标
OPEN _Cur;
#循环执行
REPEAT
FETCH _Cur INTO attndate,ksum;
IF NOT done THEN
SELECT done;
SELECT ksum;
SELECT attndate;
END IF;
UNTIL done END REPEAT; #当done=1时退出被循
#关闭光标
CLOSE _Cur;
#只有定义为0,新的循环才能继续。
SET done = 0;
END;CALL aa();
大侠看下 我版本换5.6,执行3次就退出。
恩 变量与字段不重复 值就有了。结果集是navicat的问题。