56,687
社区成员
发帖
与我相关
我的任务
分享
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();
DROP PROCEDURE IF EXISTS CirculationStatistics;
CREATE PROCEDURE CirculationStatistics()
BEGIN
DECLARE 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次就退出。