1.业务说明：t_user表中存储着原始数据表，业务需要将这些表中存储的数据转移到ibms_equipmentbasedata_minute表中。

2.表结构：

　　2.1存储表名称

1 CREATE TABLE `t_user` (2 `id` varchar(40) NOT NULL,3 PRIMARY KEY (`id`)4 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      2.2目标表

1 CREATE TABLE `ibms_equipmentbasedata_minute` ( 2 `id` bigint(20) NOT NULL AUTO_INCREMENT, 3 `created_by` varchar(255) DEFAULT NULL, 4 `created_date` datetime DEFAULT NULL, 5 `data_damage` varchar(255) DEFAULT NULL, 6 `energysid` int(11) DEFAULT NULL, 7 `is_health` int(11) DEFAULT NULL, 8 `meter_type` int(11) DEFAULT NULL, 9 `posid` int(11) DEFAULT NULL,10 `pro_code` varchar(255) DEFAULT NULL,11 `timestamp` datetime DEFAULT NULL,12 `value` double DEFAULT NULL,13 `equid` varchar(255) DEFAULT NULL,14 PRIMARY KEY (`id`)15 ) ENGINE=InnoDB AUTO_INCREMENT=2730 DEFAULT CHARSET=utf8;

       2.3 t_user存储的表

1 CREATE TABLE `sgly_11011500010010010_01` ( 2 `ID` int(11) NOT NULL AUTO_INCREMENT, 3 `timestamp` datetime DEFAULT NULL, 4 `trendFlags` int(11) DEFAULT NULL, 5 `status` int(11) DEFAULT NULL, 6 `value` double DEFAULT NULL, 7 `TRENDFLAGS_TAG` varchar(500) DEFAULT NULL, 8 `STATUS_TAG` varchar(500) DEFAULT NULL, 9 `created_by` varchar(30) DEFAULT NULL,10 `created_date` datetime DEFAULT NULL,11 `trend_flags` int(11) DEFAULT NULL,12 PRIMARY KEY (`ID`)13 ) ENGINE=InnoDB AUTO_INCREMENT=1810 DEFAULT CHARSET=utf8;

3.存储过程

1 delimiter $$ 2 drop procedure if exists p_simulate_dynamic_cursor; 3 create procedure p_simulate_dynamic_cursor() 4 begin 5 declare v_sql varchar(4000); 6  7 declare v_field varchar(4000); 8  9 declare v_result varchar(4000) default '';10 11 declare cur_temp cursor for 12 select v.* from view_temp_20150701 v;13 declare continue handler for not found set v_field is null;14 set v_sql = 'create view view_temp_20150701 as select t.id from t_user t';15 set @v_sql = v_sql;16 prepare statement from @v_sql;17 execute statement;18 deallocate prepare statement;19 open cur_temp;20 fetch cur_temp into v_field;21 22 while(v_field is not null) do23 -- declare cur_table_data cursor for select d.* from view_temp_data d;24 -- set v_result = concat(v_result, v_field, ',');25 CALL p2_simulate_dynamic_cursor(v_field);26 fetch cur_temp into v_field;27 end while;28 close cur_temp;29 30 drop view if exists view_temp_20150701;31 end;32 $$33 delimiter ;34 -- call p_simulate_dynamic_cursor();35 36 -- ----------------------------另一个存储过程动态游标-------------------------------------37 delimiter $$38 drop procedure if exists p2_simulate_dynamic_cursor;39 40 create procedure p2_simulate_dynamic_cursor(IN tableName varchar(4000))41 begin42 DECLARE done INT DEFAULT 0;43 DECLARE equiName VARCHAR(400);44 declare v_sql varchar(4000);45 declare v_time TIMESTAMP;46 declare v_value DOUBLE;47 declare v_result varchar(4000) default '';48 declare cur_temp cursor for select timestamp,value from view_temp_data;49 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;50 SET equiName=SUBSTR(tableName,6,17);51 set v_sql = concat('create view view_temp_data as select timestamp,value from ' ,tableName);52 53 54 set @v_sql = v_sql;55 prepare statement from @v_sql;56 57 execute statement;58 deallocate prepare statement;59 60 open cur_temp;61 fetch cur_temp into v_time,v_value;62 while (done=0) do63 INSERT INTO ibms_equipmentbasedata_minute(timestamp,value,equid) VALUES(v_time,v_value,equiName);64 set v_result = concat(v_result,v_time, v_value, ',');65 fetch cur_temp into v_time,v_value;66 end while;67 close cur_temp;68 select v_result;69 70 drop view if exists view_temp_data;71 end;72 $$73 delimiter ;

4.注意事项：解决方式主要是用另一个存储过程来存储另一个嵌套的游标。