mysql done_MySQL 获取游标结果报错：1193-Unknown system variable ‘done’

MySQL执行以下存储过程

DROP PROCEDURE IF EXISTS pro_empIndepart;

CREATE PROCEDURE pro_empIndepart (IN empid INT)

READS SQL DATA

BEGIN

DECLARE c_id INT;

DECLARE c_empname VARCHAR(50);

DECLARE c_job VARCHAR(50);

DECLARE c_salary INT;

DECLARE cur CURSOR for select e.id,e.yuangongname,e.word,e.wage from emp e where e.id = empid;

declare continue handler for not found set done = 1;

OPEN cur;

FETCH cur INTO c_id,c_empname,c_job,c_salary;

SELECT c_id,c_empname,c_job,c_salary;

CLOSE cur;

END;

报错：

image.png

错误原因：存储过程中使用的变量done未声明。

解决方案：

先对done变量进行声明，后使用

修改上述存储过程如下：

DROP PROCEDURE IF EXISTS pro_empIndepart;

CREATE PROCEDURE pro_empIndepart (IN empid INT)

READS SQL DATA

BEGIN

DECLARE c_id INT;

DECLARE c_empname VARCHAR(50);

DECLARE c_job VARCHAR(50);

DECLARE c_salary INT;

DECLARE done INT DEFAULT 0;

DECLARE cur CURSOR for select e.id,e.yuangongname,e.word,e.wage from emp e where e.id = empid;

declare continue handler for not found set done = 1;

OPEN cur;

FETCH cur INTO c_id,c_empname,c_job,c_salary;

SELECT c_id,c_empname,c_job,c_salary;

CLOSE cur;

END;

调用存储过程：

CALL pro_empIndepart(5)

返回结果：

image.png