-- 创建月表和日表的存储过程
delimiter $$
use `devicitydb1`$$
drop procedure if exists `createTable`$$
create definer=`devicityusr01`@`%` procedure `createTable`()
begin
declare sql_str varchar(20000);
declare tname varchar(200);
declare dataStr varchar(2000);
declare i int;
set i=0;
while i<=7 do
-- 创建日表
set sql_str=' create table if not exists tableName ( MGMT_INFO_VALUE_ID VARCHAR(60) NOT NULL,MGMT_ENTITY_ID VARCHAR(512) DEFAULT NULL,MGMT_INFO_ID VARCHAR(255) DEFAULT NULL, FROM_DATE DATETIME DEFAULT NULL, THRU_DATE DATETIME DEFAULT NULL, ITEM_VALUE0 VARCHAR(255) DEFAULT NULL, ITEM_VALUE1 VARCHAR(255) DEFAULT NULL, ITEM_VALUE2 VARCHAR(255) DEFAULT NULL, ITEM_VALUE3 VARCHAR(255) DEFAULT NULL, ITEM_VALUE4 VARCHAR(255) DEFAULT NULL, ITEM_VALUE5 VARCHAR(255) DEFAULT NULL, ITEM_VALUE6 VARCHAR(255) DEFAULT NULL, ITEM_VALUE7 VARCHAR(255) DEFAULT NULL, ITEM_VALUE8 VARCHAR(255) DEFAULT NULL, ITEM_VALUE9 VARCHAR(255) DEFAULT NULL, ITEM_DIM0 VARCHAR(255) DEFAULT NULL, ITEM_DIM1 VARCHAR(255) DEFAULT NULL, ITEM_DIM2 VARCHAR(255) DEFAULT NULL, ITEM_DIM3 VARCHAR(255) DEFAULT NULL, ITEM_DIM4 VARCHAR(255) DEFAULT NULL, ITEM_DIM5 VARCHAR(255) DEFAULT NULL, ITEM_DIM6 VARCHAR(255) DEFAULT NULL, ITEM_DIM7 VARCHAR(255) DEFAULT NULL, ITEM_DIM8 VARCHAR(255) DEFAULT NULL, ITEM_DIM9 VARCHAR(255) DEFAULT NULL, STATUS_ID VARCHAR(20) DEFAULT NULL, PERIOD_DATE DATE DEFAULT NULL, PERIOD_TIME DATE DEFAULT NULL, MEDIATION_OBJECT_ID VARCHAR(20) DEFAULT NULL, MEDIATION_DATE DATETIME DEFAULT NULL, EVENT_LEVEL VARCHAR(10) DEFAULT NULL, INFO_COUNT DECIMAL(19,2) DEFAULT NULL, MEDIATION_DELAY DECIMAL(19,2) DEFAULT NULL, DATA_BASE_DELAY DECIMAL(19,2) DEFAULT NULL, SUM_DELAY DECIMAL(19,2) DEFAULT NULL, COMMENTS VARCHAR(400) DEFAULT NULL, INFO_VALUE_UID VARCHAR(255) DEFAULT NULL, LAST_UPDATED_STAMP DATETIME DEFAULT NULL, LAST_UPDATED_TX_STAMP DATETIME DEFAULT NULL, CREATED_STAMP DATETIME DEFAULT NULL, CREATED_TX_STAMP DATETIME DEFAULT NULL, VALUE_DESC VARCHAR(255) DEFAULT NULL, NATIVE_CODE VARCHAR(255) DEFAULT NULL, RESOURCE_ID VARCHAR(20) DEFAULT NULL, SERVICE_ID VARCHAR(20) DEFAULT NULL, BUSINESS_ID VARCHAR(20) DEFAULT NULL, PARTY_ID VARCHAR(20) DEFAULT NULL, FACILITY_ID VARCHAR(20) DEFAULT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8';
-- set dataStr=curdate();
set dataStr=date_sub(curdate(),interval -i day);
set dataStr=replace(dataStr, '-','');
if i=7 then
-- 创建月表
set dataStr=substring(dataStr,1,length(dataStr)-2); -- 精确到月 创建月表
end if;
set tname=concat('mgmt_info_value_',dataStr) ;
set sql_str=replace(sql_str, 'tableName',tname) ;
-- select sql_str from dual;
set @delSql = sql_str;
prepare stmt from @delSql;
execute stmt;
select sql_str from dual;
set i=i+1;
end while;
end$$
delimiter ;
-- 事件调度器-----------------------------
delimiter $$
-- SET GLOBAL event_scheduler = ON$$ -- required for event to execute but not create
create EVENT `devicitydb1`.`event_call_procedure`
on schedule
every 1 day
-- EVERY 1 MINUTE
-- STARTS '2014-05-26 16:11:00'
-- AT TIMESTAMP '2014-05-26 16:27:00'
on completion preserve
enable
do
begin
call createTable();
end$$
delimiter ;