1.業(yè)務說明：t_user表中存儲著原始數(shù)據(jù)表，業(yè)務需要將這些表中存儲的數(shù)據(jù)轉(zhuǎn)移到ibms_equipmentbasedata_minute表中。

2.表結(jié)構(gòu)：

　　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 -- ----------------------------另一個存儲過程動態(tài)游標------------------------------------- 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.注意事項：解決方式主要是用另一個存儲過程來存儲另一個嵌套的游標。

轉(zhuǎn)載于:https://www.cnblogs.com/kevin-kw/p/6401754.html

總結(jié)

以上是生活随笔為你收集整理的动态嵌套游标解决方案的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。