我是第一次创建游标(引用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_读取所有键并迭代
2)从jira_local表中查找特定密钥的所有记录,并计算验证、解析的次数等。
问题:
当我编译这个时,它会给出一个错误
ERROR 1193: Unknown system variable 'done1'
另外,我引用this链接在循环中创建存储过程
更新:
在声明
done/done1
之后,我的过程如下所示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
,就像您对done
所做的那样:
从
DECLARE done INT DEFAULT FALSE;
到
DECLARE done, done1 INT DEFAULT FALSE;
^^^^^^^
关于mysql - MySQL CURSOR ERROR 1193:未知的系统变量,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/15834433/