MySQL creation function Times error & # 039 error error 1336 (0A000): what about Dynamic SQL'
This article mainly introduces the MySQL creation function Times error 'ERROR 1336 (0A000): how to do Dynamic SQL', the article introduces in great detail, has a certain reference value, interested friends must read it!
Error creating function Times
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
Reason for reporting an error:
Dynamic functions are not allowed in MySQL functions and triggers
Can be rewritten into a stored procedure
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
The above is all the contents of the article "MySQL creation function Times error 'ERROR 1336 (0A000): what to do with Dynamic SQL'". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!
