动态嵌套游标解决方案
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) do 23 -- 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 begin 42 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) do 63 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.注意事项：解决方式主要是用另一个存储过程来存储另一个嵌套的游标。
