MySQL 创建存储过程报错

╰半橙微兮° 2024-04-18 02:07 112阅读 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/