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()
begin
set @_sql = 'select ? + ?';
set @a = 5;
set @b = 6;
PREPARE stmt from @_sql; // 预定义sql
EXECUTE 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)) 
-- 创建存储过程 命名为tests

BEGIN   -- 存储过程的开始
  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)) -- 传入一个字符串
BEGIN
drop 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');
输出结果：

image.png