MYSQL存储过程实现动态执行SQL

列子动态查询数据库一共几个表，一共多少条记录

create PROCEDURE pc_test(in p_intdex int,in str varchar(100),OUT p_count int)
begin
declare p_index int default 0;
declare d_cnt int ;
declare p_ms varchar(500);
declare stmt varchar(500);
declare d_tab_name varchar(500);
declare _err int default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND set _err=1;    –错误处理语句
set p_count:=0;
    while p_index <= p_intdex do
    SET d_tab_name := concat(str, p_index);

—连接字符串构成完整SQL语句，动态SQL执行后的结果记录集，在MySQL中无法获取，因此需要转变思路将其放置到一个临时表中（注意代码中的写法）。一般写法如下：

set p_ms:=CONCAT(“select count(*) into @p_cnt from “,d_tab_name);
set @p_mysql=p_ms;  —注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
prepare stmt from @p_mysql; —预处理需要执行的动态SQL，其中stmt是一个变量
EXECUTE stmt;       —执行SQL语句
deallocate prepare stmt;          —释放掉预处理段
if _err=0 then
        set d_cnt :=@p_cnt;
SET p_count := p_count+d_cnt;
end if;
set p_index := p_index+1;
end while;
end;

加参数查询的存储过程（改进版）

create PROCEDURE pc_test(in p_intdex int,in str varchar(100),OUT p_count int)  
BEGIN
    DECLARE d_i int default 0;
    DECLARE d_cnt int ;
    DECLARE d_tab_name varchar(12);
    declare my_sqll varchar(500);
    declare my_sql varchar(500);
    declare _var,_err int default 0;  
    declare continue handler for sqlexception, sqlwarning, not found set _err=1;    /*执行异常信息*/
        
    SET p_count := 0;
      while d_i < p_num do
      SET d_tab_name := concat(p_strs, d_i);
          
      set my_sqll := concat(‘select count(*) into @cnt from ‘, d_tab_name);    
      set my_sql:=concat(my_sqll,’ where shipment_isnot=20′);
      set @ms := my_sql;   
      PREPARE s1 from @ms;   /*执行动态的sql*/
      EXECUTE s1;   
      deallocate prepare s1;   
    
        if _err=0 then  
        SET d_cnt := @cnt;
                if d_cnt is not null then
                    SET p_count := p_count+d_cnt;
                end if;
                
                SET d_i := d_i+1;
        else
            SET d_i := p_num+1;
    end if;   
    end while;
END