Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.