Utiliser des instructions SQL dynamiques dans les procédures stockées MySQL

Utilisation de chaque mot-clé dans une procédure stockée simple :

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;;

Les instructions SQL dynamiques sont utilisées dans les procédures stockées MySQL

Mysql 5.0 et versions ultérieures prennent en charge les instructions SQL dynamiques. Nous pouvons obtenir les valeurs souhaitées en passant différents paramètres

Voici deux types de SQL dynamique dans les procédures stockées

1.set sql = (instruction sql prétraitée, vous pouvez utiliser des instructions concat Spliced ​​​​)

set @sql = sql

PREPARE

stmt_name FROM @sql;

EXECUTE stmt_name;

　{DEALLOCATE | DROP} PREPARE stmt_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

　PREPARE stmt_name FROM @sql;

　set @var_name = xxx;

　EXECUTE stmt_name USING [USING @var_name [, @var_name] ...];

{DEALLOCATE | DROP} PREPARE stmt_name;

Ci-dessus Nous pouvons changer le code en :

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME`
varchar(36))BEGIN
 
 déclarer SQL_FOR_SELECT varchar(500); -- Définir l'instruction SQL de prétraitement                                                                                                                                                                                                                                                                                                                                            - - Instruction SQL de requête d'épissage

set @sql = SQL_FOR_SELECT;
PREPARE stmt FROM @sql; -- Instruction SQL dynamique de prétraitement

set @parm1 = USER_ID ; -- Passer les paramètres dynamiques SQL
set @parm2 = USER_NAME ;

EXECUTE stmt USING @parm1, @parm2 ; --Execute l'instruction SQL
deallocate prepare stmt ; -Relâchez prepare

set @sql = SQL_FOR_SELECT;
PREPARE stmt FROM @sql ;

EXECUTE stmt USING @parm1 , @parm2 -- Exécuter l'instruction SQL
deallocate prepare stmt; -- Release prepare
END;


De cette façon, nous pouvons réellement utiliser différents paramètres (bien sûr, vous pouvez également utiliser SQL dynamique en générant différents paramètres via la logique dans le fichier stocké procédure).

Quelques 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 (?,?,?) "   

因为有可能我不确定in语句里有几个参数，所以我试过这么写 

set @sql = "select * from user where user_id in (?) "  

然后参数我传的是  "'1','2','3'"  我以为程序会将我的动态sql解析出来（select * from user where user_id in ('1','2','3')） 但是并没有解析出来，在写存储过程in里面的列表用个传入参数代入的时候，就需要用到如下方式：

1.使用find_in_set函数

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

2.还可以比较笨实的方法，就是组装字符串，然后执行

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;

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!