重庆小潘seo博客

当前位置:首页 > 重庆网络营销 > 小潘杂谈 >

小潘杂谈

mysql 存储过程中使用动态sql语句

时间:2020-09-22 14:40:09 作者:重庆seo小潘 来源:
简单的存储过程各个关键字的用法:CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`(in _xnb varchar(50))BEGIN## 定义变量DECLARE _num FLOAT(14,6) DEFAULT 0;## @表示全局变量 相当于php $## 拼接赋值 INTO 必须要用全局变量不然语句会报错## //CONCAT

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 _numEND;;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))BEGINdeclare SQL_FOR_SELECT varchar(500); -- 定义预处理sql语句set SQL_FOR_SELECT = CONCAT("select * fromuserwhere 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))BEGINdeclare 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;-- 释放prepareset 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))BEGINdeclare 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;-- 释放prepareset @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;-- 释放prepareEND;

这样,我们就可以真正的使用不同的参数(当然也可以在存储过程中通过逻辑生成不同的参数)来使用动态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))BEGINset @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语句的详细内容,更多请关注小潘博客其它相关文章!