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;mysql视图和存储过程
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      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)      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')=DAT      select count(1) from (se
lect 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      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_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      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;
上⾯ 这⼏个语句可以说是我们使⽤动态的表名的核⼼的部分
希望上⾯的对你有所帮助

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。