DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `city`.`PROCEDURE_THREE`(IN id INT,OUT truthname VARCHAR(30))
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
SET @id=id;
SELECT NAME INTO truthname FROM city WHERE id=@id;
END$$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `city`.`PROCEDURE_THREE`(IN id INT,OUT truthname VARCHAR(30))
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
SET @id=id;
SELECT NAME INTO truthname FROM city WHERE id=@id;
END$$
DELIMITER ;
上面红色标记的语句会出现如标题的错误;
更正方法就是添加唯一一行来确定:LIMIT 1条件
修改代码:
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `city`.`PROCEDURE_THREE`(IN id INT,OUT truthname VARCHAR(30))
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
SET @id=id;
SELECT NAME INTO truthname FROM city WHERE id=@id LIMIT 1;
END$$
DELIMITER ;
这种赋值语句还有个规矩就是参数名和字段名不能冲突,不然能够执行过去,但是却没有给参数赋值,这是个很隐性的错误,可以参考手册上的:
重要: SQL变量名不能和列名一样。如果SELECT ... INTO这样的SQL语句包含一个对列的参考,并包含一个与列相同名字的局部变量,MySQL当前把参考解释为一个变量的名字。