mysql 1193_MySQL CURSOR ERROR 1193：未知的系统变量

我是第一次创建游标.(参考this site)

到目前为止我做了这个(

CREATE PROCEDURE `abc`.`cursordemo` (IN start_date DATETIME,IN end_date DATETIME)

BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE k1,k2,g,s,last_status VARCHAR(45);

DECLARE b, c INT;

DECLARE cur1 CURSOR FOR SELECT `key` FROM `abc`.`temp_weekly`;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;

read_loop: LOOP

FETCH cur1 INTO k1;

IF done THEN

LEAVE read_loop;

END IF;

block_cursor:BEGIN

DECLARE cur2 CURSOR FOR SELECT `key`,`group`,`status` FROM `abc`.`jira_local` WHERE `key` = k1 AND updateddate < end_date;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;

OPEN cur2;

REPEAT

FETCH cur2 INTO k2,g,s;

IF NOT done1 THEN

IF s != last_status THEN

CASE

WHEN s = 'verified' THEN

SET c = c 1;

WHEN s = 'closed' THEN

SET c = c 1;

WHEN s = 'to be scheduled' THEN

SET c = c 1;

WHEN s = 'deferred' THEN

SET c = c 1;

/*'resolved','closed','to be scheduled','deferred','validated','assigned','l3 need more info','l2 need more info','need more info'*/

WHEN s = 'resolved' THEN

SET c = c 1;

WHEN s = 'validated' THEN

SET c = c 1;

WHEN s = 'assigned' THEN

SET c = c 1;

WHEN s = 'l3 need more info' THEN

SET c = c 1;

WHEN s = 'l2 need more info' THEN

SET c = c 1;

WHEN s = 'need more info' THEN

SET c = c 1;

END CASE;

SET last_status = s;

END IF;

END IF;

UNTIL NOT done1 END REPEAT;

INSERT INTO ticketsResolvedCount values(k2,g,s,c);

END block_cursor;

END LOOP;

CLOSE cur1;

CLOSE cur2;

END$$

我在做什么

1)从temp_weekly读取所有键并迭代

2)从jira_local表中查找特定键的所有记录,并计算其验证,解析等的次数.

问题：

当我编译它时会出错

ERROR 1193: Unknown system variable 'done1'

另外,我提到this链接在循环内创建存储过程

更新：

声明完成/完成后,我的程序看起来像这样

BEGIN

DECLARE k1,k2,g,s,last_status VARCHAR(45);

DECLARE b, c INT;

DECLARE cur1 CURSOR FOR SELECT `key` FROM `abc`.`temp_weekly`;

DECLARE done1 BOOLEAN DEFAULT FALSE;

DECLARE done BOOLEAN DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

.

.

.

block_cursor:BEGIN

DECLARE cur2 CURSOR FOR SELECT `key`,`group`,`status` FROM `abc`.`jira_local` WHERE `key` = k1 AND updateddate < end_date;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;

.

.

.

这给了我

ERROR 1337: Variable or condition declaration after cursor or handler declaration

解决方法:

你需要像完成一样声明done1：

从

DECLARE done INT DEFAULT FALSE;

至

DECLARE done, done1 INT DEFAULT FALSE;

^^^^^^^

来源：https://www.icode9.com/content-2-365801.html