mysql存储历程中运用动态sql语句-百分百源码网

mysql存储历程中运用动态sql语句

发布时间：09/01 来源：未知浏览：关键词：

Mysql5.0今后，支撑了动态sql语句，我们可以通过通报不一样的参数得到我们想要的值，本文介绍两种在存储历程中的动态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语句，我们可以通过通报不一样的参数得到我们想要的值

这里介绍两种在存储历程中的动态sql

1.set sql = （预处置的sql语句，可以是用concat拼接的语句）

　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;

上述的代码我们就可以改成：

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 = "select * from user where user_id = ? and user_name = ? ";
-- 拼接查询sql语句

set @sql = SQL_FOR_SELECT;
PREPARE stmt FROM @sql; -- 预处置动态sql语句

set @parm1 = USER_ID; -- 通报sql动态参数
set @parm2 = USER_NAME;

EXECUTE stmt USING @parm1 , @parm2; -- 施行sql语句
deallocate prepare stmt; -- 开释prepare

set @sql = SQL_FOR_SELECT;
PREPARE stmt FROM @sql; -- 预处置动态sql语句

set @parm1 = '2'; -- 通报sql动态参数
set @parm2 = 'lisi';

EXECUTE stmt USING @parm1 , @parm2; -- 施行sql语句
deallocate prepare stmt; -- 开释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语句的细致内容，更多请关注百分百源码网其它相干文章！

打赏