当我试图理解mysql中的过程和游标是如何工作的时候,我的代码遇到了问题。我想让游标使用和/或更新代码中声明的变量,但唯一正确更新的变量是var1和var2。
cursor2应该只返回1个结果,但是它只返回空行,var3和var4没有更新-它们的值显然仍然是默认值。
游标3和变量var4也是一样。
真正的代码与此类似:
DELIMITER //
CREATE PROCEDURE nameProcedure()
proc_label:BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE var1 VARCHAR(20) DEFAULT "";
DECLARE var2 VARCHAR(30) DEFAULT "";
DECLARE var3 INT(3) DEFAULT 0;
DECLARE var4 int(3) DEFAULT 0;
// first cursor, gives multiple rows as results
DECLARE cursor1 CURSOR FOR
SELECT column1, column2
FROM table1;
// second cursor, should give only 1 row as result
DECLARE cursor2 CURSOR FOR
SELECT column1, column2
FROM table2
WHERE column1=var1
AND column2=var2;
// third cursor, should give only 1 row as result
DECLARE cursor3 CURSOR FOR
SELECT count(column3)
FROM table3
WHERE column1=var3;
// HANDLER
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN cursor1;
OPEN cursor2;
OPEN cursor3;
label: LOOP
FETCH cursor1 INTO var1, var2;
IF finished=1 THEN LEAVE loop;
END IF;
FETCH cursor2 INTO var3, var4;
IF finished=1 THEN LEAVE proc_label;
END IF;
FETCH cursor3 INTO var5;
IF finished=1 THEN LEAVE proc_label;
END IF;
/* UPDATE some rows in tables using the values from var1, var2, var3, var4, var5 in WHERE clauses */
END LOOP loop;
CLOSE cursor1;
CLOSE cursor2;
CLOSE cursor3;
END//
我很抱歉在这篇文章的写作,英语不是我的母语,我从来没有在论坛上写过一篇文章,所以我不知道如何格式化它,使人们更容易理解的问题。建议、改正等是非常受欢迎的。
暂无答案!
目前还没有任何答案,快来回答吧!