MySQL 创建存储过程报错 ╰半橙微兮° 2024-04-18 02:07 15阅读 0赞 mysql> delimiter $$ mysql> CREATE FUNCTION check\_rollbackPatch(THE\_REFERNCE\_LABEL VARCHAR(75), THE\_COLUMN\_NAME VARCHAR(75), THE\_INSTALL\_VERSION VARCHAR(75), THE\_VERSION\_LEVEL\_TABLE\_NAME VARCHAR(75)) -> RETURNS INTEGER DETERMINISTIC -> BEGIN -> DECLARE INSTALL\_VERSION VARCHAR(80) DEFAULT NULL; -> DECLARE REFERENCE\_LABEL VARCHAR(80) DEFAULT NULL; -> DECLARE THE\_QUERY VARCHAR(500) DEFAULT NULL; -> DECLARE too\_many\_rows CONDITION FOR 1172; -> DECLARE CONTINUE HANDLER FOR too\_many\_rows -> BEGIN -> SELECT concat('\[INFO\] Several ', THE\_REFERNCE\_LABEL, ' references found.'); -> RETURN 0; -> END; -> DECLARE CONTINUE HANDLER FOR NOT FOUND -> BEGIN -> /\*SELECT concat('\[INFO\] ', THE\_REFERNCE\_LABEL, ' reference not found.');\*/ -> RETURN 1; -> END; -> SELECT concat('', THE\_COLUMN\_NAME, ', C\_INSTALL\_VERSION') INTO @V\_PART1; -> SELECT concat(THE\_COLUMN\_NAME, '=''', THE\_REFERNCE\_LABEL, ''' and C\_INSTALL\_VERSION=''', THE\_INSTALL\_VERSION, '''') INTO @V\_PART2; -> SELECT format\_selectQuery(THE\_VERSION\_LEVEL\_TABLE\_NAME, @V\_PART1, @V\_PART2) INTO THE\_QUERY; -> /\*SELECT concat('\[INFO\] The query to execute is \[', THE\_QUERY, '\]');\*/ -> /\*EXECUTE IMMEDIATE THE\_QUERY into REFERENCE\_LABEL, INSTALL\_VERSION;\*/ -> /\*SELECT concat('\[INFO\] ', THE\_REFERNCE\_LABEL, ' reference found');\*/ -> RETURN 0; -> END $$ ERROR 1415 (0A000): Not allowed to return a result set from a function mysql> delimiter ; 报错原因: 在MySQL的function里,不能使用SELECT语句来返回结果集,会报错。 注释掉后,报错消失 mysql> delimiter $$ mysql> CREATE FUNCTION check\_rollbackPatch(THE\_REFERNCE\_LABEL VARCHAR(75), THE\_COLUMN\_NAME VARCHAR(75), THE\_INSTALL\_VERSION VARCHAR(75), THE\_VERSION\_LEVEL\_TABLE\_NAME VARCHAR(75)) -> RETURNS INTEGER DETERMINISTIC -> BEGIN -> DECLARE INSTALL\_VERSION VARCHAR(80) DEFAULT NULL; -> DECLARE REFERENCE\_LABEL VARCHAR(80) DEFAULT NULL; -> DECLARE THE\_QUERY VARCHAR(500) DEFAULT NULL; -> DECLARE too\_many\_rows CONDITION FOR 1172; -> DECLARE CONTINUE HANDLER FOR too\_many\_rows -> BEGIN -> /\*SELECT concat('\[INFO\] Several ', THE\_REFERNCE\_LABEL, ' references found.');\*/ -> RETURN 0; -> END; -> DECLARE CONTINUE HANDLER FOR NOT FOUND -> BEGIN -> /\*SELECT concat('\[INFO\] ', THE\_REFERNCE\_LABEL, ' reference not found.');\*/ -> RETURN 1; -> END; -> SELECT concat('', THE\_COLUMN\_NAME, ', C\_INSTALL\_VERSION') INTO @V\_PART1; -> SELECT concat(THE\_COLUMN\_NAME, '=''', THE\_REFERNCE\_LABEL, ''' and C\_INSTALL\_VERSION=''', THE\_INSTALL\_VERSION, '''') INTO @V\_PART2; -> SELECT format\_selectQuery(THE\_VERSION\_LEVEL\_TABLE\_NAME, @V\_PART1, @V\_PART2) INTO THE\_QUERY; -> /\*SELECT concat('\[INFO\] The query to execute is \[', THE\_QUERY, '\]');\*/ -> /\*EXECUTE IMMEDIATE THE\_QUERY into REFERENCE\_LABEL, INSTALL\_VERSION;\*/ -> /\*SELECT concat('\[INFO\] ', THE\_REFERNCE\_LABEL, ' reference found');\*/ -> RETURN 0; -> END $$ Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; 来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26506993/viewspace-2124732/,如需转载,请注明出处,否则将追究法律责任。 转载于:http://blog.itpub.net/26506993/viewspace-2124732/
还没有评论,来说两句吧...