Usage of each keyword in a simple stored procedure:

CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`(in _xnb varchar(50)) BEGIN ## 定义变量 DECLARE _num FLOAT(14,6) DEFAULT 0; ## @表示全局变量 相当于php $ ## 拼接赋值 INTO 必须要用全局变量不然语句会报错 ## //CONCAT会把'SELECT SUM('和_xnb和') INTO @tnum FROM btc_user_coin'拼接起来,CONCAT的各个参数中间以","号分割 SET @strsql = CONCAT('SELECT SUM(',_xnb,') INTO @tnum FROM btc_user_coin'); ## 预处理需要执行的动态SQL，其中stmt是一个变量 PREPARE stmt FROM @strsql; ## 执行SQL语句 EXECUTE stmt; ## 释放掉预处理段 deallocate prepare stmt; ## 赋值给定义的变量 SET _num = @tnum; SELECT _num END;;

Mysql Use dynamic sql statements in stored procedures

After Mysql 5.0, dynamic sql statements are supported. We can get the values we want by passing different parameters

Here are two types of dynamic sql in stored procedures

1.set sql = (preprocessed sql statement, you can use concat Spliced statements)

set @sql = sql

PREPAREstmt_nameFROM @sql;

EXECUTEstmt_name;

　{DEALLOCATE | DROP} PREPAREstmt_name;

过程过程示例:

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))BEGIN declare SQL_FOR_SELECT varchar(500); -- 定义预处理sql语句 set SQL_FOR_SELECT = CONCAT("select * from user where user_id = '",USER_ID,"' and user_name = '",USER_NAME,"'"); -- 拼接查询sql语句 set @sql = SQL_FOR_SELECT; PREPARE stmt FROM @sql; -- 预处理动态sql语句 EXECUTE stmt ; -- 执行sql语句 deallocate prepare stmt; -- 释放prepareEND;

上述是一个简单的查询用户表的存储过程，当我们调用此存储过程，可以根据传入不同的参数获得不同的值。

但是：上述存储过程中，我们必须在拼接sql语句之前把USER_ID,USER_NAME定义好，而且在拼接sql语句之后，我们无法改变USER_ID,USER_NAME的值，如下：

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))BEGIN declare SQL_FOR_SELECT varchar(500); -- 定义预处理sql语句 set SQL_FOR_SELECT = CONCAT("select * from user where user_id = '",USER_ID,"' and user_name = '",USER_NAME,"'"); -- 拼接查询sql语句 set @sql = SQL_FOR_SELECT; PREPARE stmt FROM @sql; -- 预处理动态sql语句 EXECUTE stmt ; -- 执行sql语句 deallocate prepare stmt; -- 释放prepare set USER_ID = '2'; -- 主动指定参数USER_ID的值 set USER_NAME = 'lisi'; set @sql = SQL_FOR_SELECT; PREPARE stmt FROM @sql; -- 预处理动态sql语句 EXECUTE stmt ; -- 执行sql语句 deallocate prepare stmt; -- 释放prepareEND;

　我们用call aa('1','zhangsan');来调用该存储过程，第一次动态执行，我们得到了‘张三’的信息，然后我们在第14,15行将USER_ID，USER_NAME改为lisi，我们希望得到李四的相关信息，可查出来的结果依旧是张三的信息，说明我们在拼接sql语句后，不能再改变参数了。

为了解决这种问题，下面介绍第二中方式：

2.set sql = （预处理的sql语句，可以是用concat拼接的语句，参数用 ？代替）

　set @sql = sql

　PREPAREstmt_nameFROM @sql;

set @var_name = xxx;

EXECUTEstmt_name USING [ USING @var_name[, @var_name] ...];

stmt_name;

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME`
varchar(36))BEGIN
SQL_FOR_SELECT VARCHAR (500);-Define the pre-processing SQL statement

stet SQL_FOR_SELECT = "Select*from user where user_id =? And user_name =?";
# set @sql = sql_FOR_SELECT;
Prepare Stmt from @sql;-Pre-processing dynamic SQL statement

stet@Parm1 = User_id; ;

EXECUTE stmt USING @parm1, @parm2; --Execute sql statement
deallocate prepare stmt; --Release prepare

set @sql = SQL_F OR_SELECT;
PREPARE stmt FROM @sql;
# EXECUTE stmt USING @parm1 , @parm2; --Execute sql statement
deallocate prepare stmt; --Release prepare
END;


In this way, we can actually use different parameters (of course You can use dynamic sql by generating different parameters through logic in the stored procedure).

A few notes:

• 　存储动态SQL的值的变量不能是自定义变量，必须是用户变量或者全局变量 如：set sql = 'xxx'; prepare stmt from sql;是错的，正确为： set @sql = 'xxx'; prepare stmt from @sql;

• 即使preparable_stmt语句中的 ? 所代表的是一个字符串，你也不需要将 ? 用引号包含起来。

• 如果动态语句中用到了 in ,正常写法应该这样：select * from table_name t where t.field1 in (1,2,3,4,...);

• 则sql语句应该这样写：set @sql = "select * from user where user_id in (?,?,?) "

select * from table_name t where find_in_set(t.field1,'1,2,3,4');

DROP PROCEDURE IF EXISTS photography.Proc_Test; CREATE PROCEDURE photography.`Proc_Test`(param1 varchar(1000)) BEGIN set @id = param1; set @sel = 'select * from access_record t where t.ID in ('; set @sel_2 = ')'; set @sentence = concat(@sel,@id,@sel_2); -- 连接字符串生成要执行的SQL语句 prepare stmt from @sentence; -- 预编释一下。 “stmt”预编释变量的名称， execute stmt; -- 执行SQL语句 deallocate prepare stmt; -- 释放资源 END;

The above is the detailed content of Use dynamic sql statements in mysql stored procedures. For more information, please follow other related articles on the PHP Chinese website!