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语句,我们可以通过传递不一样的参数得到我们想要的值
这里介绍两种在储备历程中的动态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语句的具体内容,更多请关注百分百源码网其它相关文章!