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

这篇文章主要介绍MySQL创建函数时报错'ERROR 1336 (0A000): Dynamic SQL'怎么办，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

创建函数时报错

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 ;

以上是“MySQL创建函数时报错'ERROR 1336 (0A000): Dynamic SQL'怎么办”这篇文章的所有内容，感谢各位的阅读！

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!