MySQL 创建函数时报错'ERROR 1336 (0A000): Dynamic SQL'

ゝ一纸荒年。 2024-04-18 02:09 63阅读 0赞

创建函数时报错  
mysql> delimiter $$  
mysql> CREATE FUNCTION is\_SQLScriptReferenceExist (THE\_REFERENCE VARCHAR(75), THE\_VERSION\_LEVEL\_TABLE\_NAME VARCHAR(75)) RETURNS INTEGER DETERMINISTIC  
    ->     BEGIN  
    ->     DECLARE THE\_REFERENCE\_LABEL VARCHAR(80);  
    ->     DECLARE CONTINUE HANDLER FOR NOT FOUND  
    ->     BEGIN  
    ->     RETURN 0;  
    ->     END;  
    ->         SELECT concat('select c\_reference\_label INTO THE\_REFERENCE\_LABEL from ', THE\_VERSION\_LEVEL\_TABLE\_NAME, ' where C\_REFERENCE\_LABEL = ''', THE\_REFERENCE, '''') INTO @stmt;  
    ->         PREPARE STMT FROM @stmt;  
    ->         EXECUTE STMT;  
    ->         /\* select c\_reference\_label into THE\_REFERENCE\_LABEL from THE\_VERSION\_LEVEL\_TABLE\_NAME where C\_REFERENCE\_LABEL = THE\_REFERENCE; \*/  
    ->         RETURN 1;  
    ->     END$$  
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger  
mysql> delimiter ;  
  
报错原因：  
MySQL函数和触发器中不允许使用动态函数  
  
可以改写成存储过程  
mysql> delimiter $$  
mysql> CREATE PROCEDURE is\_SQLScriptReferenceExist (IN THE\_REFERENCE VARCHAR(75), IN THE\_VERSION\_LEVEL\_TABLE\_NAME VARCHAR(75), OUT V\_RET INT(2))  
    ->     BEGIN  
    ->     DECLARE THE\_REFERENCE\_LABEL VARCHAR(80);  
    ->     DECLARE CONTINUE HANDLER FOR NOT FOUND  
    ->     BEGIN  
    ->     SET V\_RET = 0;  
    ->     END;  
    ->         SELECT concat('select c\_reference\_label INTO THE\_REFERENCE\_LABEL from ', THE\_VERSION\_LEVEL\_TABLE\_NAME, ' where C\_REFERENCE\_LABEL = ''', THE\_REFERENCE, '''') INTO @stmt;  
    ->         PREPARE STMT FROM @stmt;  
    ->         EXECUTE STMT;  
    ->         /\* select c\_reference\_label into THE\_REFERENCE\_LABEL from THE\_VERSION\_LEVEL\_TABLE\_NAME where C\_REFERENCE\_LABEL = THE\_REFERENCE; \*/  
    ->         SET V\_RET = 1;  
    ->     END$$  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> delimiter ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2124763/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2124763/