mysql存过例子

[code]CREATE DEFINER=`root`@`%` PROCEDURE `shardingsphere_m_point_new`(OUT count_num int )READS SQL DATAp:BEGIN-- 定义变量DECLARE i INT DEFAULT 0;DECLARE st datetime ;DECLARE nt date;DECLARE et date ;--可以重跑存储过程，从发表结果来取最小值SELECT MAX(Transtime), date_add(current_date(), interval 1 day) INTO st, etFROM (SELECT MAX(Transtime) AS Transtime FROM wehotel_point.m_point_0 unionSELECT MAX(Transtime) AS Transtime FROM wehotel_point.m_point_1 unionSELECT MAX(Transtime) AS Transtime FROM wehotel_point.m_point_2 unionSELECT MAX(Transtime) AS Transtime FROM wehotel_point.m_point_3 unionSELECT MAX(Transtime) AS Transtime FROM wehotel_point.m_point_4 unionSELECT MAX(Transtime) AS Transtime FROM wehotel_point.m_point_5 unionSELECT MAX(Transtime) AS Transtime FROM wehotel_point.m_point_6 unionSELECT MAX(Transtime) AS Transtime FROM wehotel_point.m_point_7 unionSELECT MAX(Transtime) AS Transtime FROM wehotel_point.m_point_8 unionSELECT MAX(Transtime) AS Transtime FROM wehotel_point.m_point_9 ) a;--分表结果为空，则从原始表wehotel_point.m_point里开始取if st is null  thenSELECT CAST(MIN(TransTime) AS DATE), date_add(current_date(), interval 1 day) INTO st, etFROM wehotel_point.m_point;end if ;-- 然后从开始日期开始，每天的数据写入分表中。WHILE st<etDOSET i=0;SET nt=CAST(DATE_ADD(st,INTERVAL 1 DAY) AS DATE);WHILE i<10DOSET @sqlstr = concat(\"insert into m_point_\",i,\" select * from m_point where \'\", st,\"\' <TransTime and TransTime<= \'\",nt,\"\' and mebId%10=\",i);PREPARE p_sqlstr FROM @sqlstr;EXECUTE p_sqlstr;SET i=i+1;END WHILE;SET st=nt;SELECT st;END WHILE;SET count_num=1;END