mysql ストアドプロシージャで動的 SQL ステートメントを使用する

步履不停

リリース： 2019-06-24 18:24:48

オリジナル

3271 人が閲覧しました

mysql ストアドプロシージャで動的 SQL ステートメントを使用する単純なストアドプロシージャでの各キーワードの使用:

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 ストアドプロシージャで動的 SQL ステートメントを使用する

Mysql 5.0 以降では、動的 SQL ステートメントがサポートされ、さまざまなパラメーターを渡すことで必要な値を取得できます

ストアドプロシージャ内の 2 種類の動的 SQL を次に示します。

1.set sql = (前処理された SQL ステートメント。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] ...];

{割り当て解除 | ドロップ} 準備

stmt _name;

上記のコードを次のように変更できます:

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME`
varchar(36))BEGIN
SQL_FOR_SELECT VARCHAR (500 );-前処理 SQL ステートメントを定義します

stet SQL_FOR_SELECT = "Select*from user where user_id =? And user_name =?";
# set @sql = sql_FOR_SELECT;
Prepare Stmt from @sql;-動的 SQL ステートメントの前処理

stet@Parm1 = User_id; ;

EXECUTE stmt USING @parm1, @parm2; --SQL ステートメントの実行
deallocate prepare stmt; --リリース prepare

set @sql = SQL_F OR_SELECT;
PREPARE stmt FROM @sql;
# @parm1 、@parm2 を使用して stmt を実行する; --実行SQL ステートメント
割り当て解除準備 stmt ; --Release prepare
END;

このようにして、実際にさまざまなパラメーターを使用できます (もちろん、ストアドプロシージャのロジックを通じてさまざまなパラメーターを生成することで、動的 SQL を使用することもできます)。

いくつかのメモ:

　存储动态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;
ログイン後にコピー

以上がmysql ストアドプロシージャで動的 SQL ステートメントを使用するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。