MySQL 带参数的存储过程(动态执行SQL语句)

MySQL5.0 以后，支持动态sql语句。

当SQL语句中 字段名，表名，数据库名等 要作为变量时，必须要使用动态SQL。
MySQL动态SQL语法如下：

set sql = （预处理的sql语句，可以是用concat拼接的语句）set @sql = sql //你的sql语句PREPARE stmt FROM @sql; EXECUTE stmt (如果sql有参数的话, USING xxx,xxx); // 这里USING的只能是会话变量;DEALLOCATE PREPARE stmt;

1、 定义要执行的sql变量，并为其赋值

2、预定义好要使用的sql.

3、执行预定义的sql
4、释放掉数据库连接

实例1：

delimiter //create procedure pro_test()beginset @_sql = 'select ? + ?';set @a = 5;set @b = 6;PREPARE stmt from @_sql; // 预定义sqlEXECUTE stmt USING @a,@b;// 传入两个会话变量来填充sql中的 ?DEALLOCATE PREPARE stmt; // 释放连接end //

调用：
call pro_test();
返回结果：11

实例2：

delimiter //CREATE PROCEDURE pro_stu(in order_param VARCHAR(50),in startindex int ,in size int)BEGIN     set @v_sql = "select * from student s ORDER BY ?  LIMIT ?,?";
     PREPARE stmt from @v_sql;
      set @a = order_param;
      set @b = startindex;
      set @c = size;
      
      EXECUTE stmt using @a,@b,@c;
      DEALLOCATE PREPARE stmt;end//delimiter;

调用：
call pro_stu('s.s_no desc',0,20);
输出结果：

image.png

注意：MySQL 在存储过程中是不支持直接使用变量名作为表名或者是列名的，而在实际的应用中确实会用到变表名或者变量名的情况。以下实例简单说明动态表名、列名的查询。

实例3：

DROP PROCEDURE IF EXISTS select_test;delimiter //create PROCEDURE select_test(tableName varchar(20)) -- 创建存储过程 命名为testsBEGIN   -- 存储过程的开始  set @tableNames = CONCAT(tableName); -- @先在用户变量中保存值然后在以后引用它  set @v_sql = CONCAT('select * from ',@tableNames);-- 拼接查询总记录的SQL语句 
  prepare stmt from @v_sql; -- 预定义一个语句，并将它赋给 stmt
  execute stmt ; -- 执行语句
  deallocate prepare stmt;-- 要释放一个预定义语句的资源
end//-- 存储过程的结束delimiter;

调用：
call select_test('student');

实例4：

DROP PROCEDURE IF EXISTS myTest1;delimiter //create procedure myTest1(in columnName varchar(50)) -- 传入一个字符串BEGINdrop table if exists tmpTable; -- 如果临时表存在先删除掉set @_sql = concat('create temporary table if not exists tmpTable( ', columnName, ' varchar(50), id int(15), name varchar(50));'); -- 创建临时表的语法，我们把传入的参数拼接进来PREPARE stmt from @_sql;    EXECUTE stmt;DEALLOCATE PREPARE stmt;  -- 执行
desc tmpTable;end //

调用：
call myTest1('password');
输出结果：

作者：乘风破浪的姐姐
链接：https://www.jianshu.com/p/d070abffa5fe
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。