mysql存储过程跟任务调度器

-- 创建月表和日表的存储过程
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 ;