MySQL存储过程---动态的表名

drop procedure if exists pr_multi;  

create PROCEDURE pr_multi() 
begin 
declare areanum varchar(32); -- 区域编码 
DECLARE v_tablename varchar(50);
DECLARE v_sysbn INT;
DECLARE v_monthbn INT;
DECLARE v_bnrate VARCHAR(32);
DECLARE v_zzounum INT;
DECLARE v_pubouzznum INT;
DECLARE v_totalpubnum INT;
DECLARE v_monpubnum INT;
DECLARE v_zzrate VARCHAR(32);
DECLARE v_ounum INT;
DECLARE outasknum INT;
DECLARE v_outasknum INT;
DECLARE done INT DEFAULT FALSE; -- 遍历数据结束标志 
DECLARE myCursor CURSOR FOR(    -- 定义游标并输入结果集
	select SUBSTR(oucode,1,6) as area from frame_ou GROUP BY area having LENGTH(area)>0 limit 10);
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 绑定控制变量到游标,游标循环结束自动转true  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  
CREATE TEMPORARY TABLE IF NOT EXISTS tmpTable 
	(
		rowguid VARCHAR(50) PRIMARY KEY,
    areaname VARCHAR(50),
		sysbn INT,
		monthbn INT, 
		bnrate VARCHAR(50),
		zzounum int, 
		pubouzznum INT,	
		totalpubnum INT,	
		monpubnum INT,	
		zzrate VARCHAR(50)	
	);
	TRUNCATE TABLE tmpTable; 
  OPEN myCursor; -- 打开游标  
		myLoop: LOOP -- 开始循环体,myLoop为自定义循环名
		 FETCH myCursor into areanum; -- 将游标当前读取行的数据,顺序赋予自定义变量  
			 IF done=1 THEN -- 判断是否继续循环  
			 LEAVE myLoop; -- 结束循环  
			 END IF;
      set v_tablename=CONCAT('audit_project_',areanum);
      set @preparable_stmt1=CONCAT("select COUNT(1) FROM ",v_tablename,' into @v_sysbn;');
      set @preparable_stmt2=CONCAT("select COUNT(1) FROM ",v_tablename,' where date_format(applydate,\'%Y-%M\')=date_format(now(),\'%Y-%M\') into @v_monthbn;');
      set @preparable_stmt3=CONCAT("select count(1) from ( select ouguid from ",v_tablename,' group by ouguid) c into @ounum;');
      select count(1) into v_ounum from frame_ou where oucode like CONCAT('%',areanum,'%');
      select count(1) into v_zzounum from (select count(*) from zhengzhaotopten where oucode like CONCAT('%',areanum,'%') and zznum>0 GROUP BY oucode) c;
      select count(1) into v_pubouzznum from(select oucode from mongdb_zztj GROUP BY oucode) c where c.oucode like CONCAT('%',areanum,'%');
      select sum(entryNum) INTO v_totalpubnum from mongdb_zztj where oucode like CONCAT('%',areanum,'%');
      select sum(entryNum) INTO v_monpubnum from mongdb_zztj where oucode like CONCAT('%',areanum,'%') and DATE_FORMAT(EntryDate,'%Y-%M')=DATE_FORMAT(NOW(),'%Y-%M');
      select count(1) from (select count(1) from audit_task where areacode=areanum and LENGTH(ouname)>0 GROUP BY ouguid) c into @outasknum;
      select count(1) from (select oucode from mongdb_zztj where LENGTH(oucode)>0 and oucode like CONCAT('%',areanum,'%') GROUP BY oucode) c into @v_outasknum;
      prepare stmt1 from @preparable_stmt1; 
      prepare stmt2 from @preparable_stmt2; 
      prepare stmt3 from @preparable_stmt3; 
      EXECUTE stmt1; 
      EXECUTE stmt2;
      EXECUTE stmt3; 
      set v_sysbn=@v_sysbn;
      set v_monthbn=@v_monthbn;
      set v_bnrate=CONCAT(TRUNCATE((@ounum/v_ounum)*100,1),'%');
      set v_zzrate=CONCAT(TRUNCATE((@v_outasknum*1.0)/(@outasknum*1.0)*100,1),'%');
     INSERT INTO tmpTable(rowguid,areaname,sysbn,monthbn,bnrate,zzounum,pubouzznum,totalpubnum,monpubnum,zzrate) values(UUID(),areanum,v_sysbn,v_monthbn,v_bnrate,v_zzounum,v_pubouzznum,v_totalpubnum,v_monpubnum,v_zzrate);
     END LOOP myLoop; -- 结束自定义循环体 
  CLOSE myCursor; -- 关闭游标  
 select * from tmpTable; 
end; 



call pr_multi;

目标:当我们使用触发器的时候，如果我们数据库里面采用的是分表的形式，就是我们信息存在project_aaaa,project_bbbb,project_cccc,project_dddd,里面的数据表结构都是一样的，那么我们肯定是想如果能够在存储结构中能够根据表的后缀的不同，实现对不同的表的遍历，于是我们就不得不去想着在存储结构中使用的是动态的表明。

于是我们在上面遍历的其实是后缀，不断的改变表的后缀实现在不同的表中进行数据的遍历。

  set v_tablename=CONCAT('audit_project_',areanum);

这个可以认为是我们不断的改变表的名称，设置预处理字符串:

 set @preparable_stmt1=CONCAT("select COUNT(1) FROM ",v_tablename,' into @v_sysbn;');

 prepare stmt1 from @preparable_stmt1; 

 EXECUTE stmt1;

上面 这几个语句可以说是我们使用动态的表名的核心的部分

希望上面的对你有所帮助