这篇文章主要介绍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;
->
-> 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;
->
-> SET V_RET = 1;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
以上是“MySQL创建函数时报错'ERROR 1336 (0A000): Dynamic SQL'怎么办”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注编程网数据库频道!
--结束END--
本文标题: MySQL创建函数时报错'ERROR 1336 (0A000): Dynamic SQL'怎么办
本文链接: https://www.lsjlt.com/news/63320.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0