动态嵌套游标解决方案

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

2.表结构：

　　2.1存储表名称

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

　　2.2目标表

CREATE TABLE `ibms_equipmentbasedata_minute` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`created_by` varchar(255) DEFAULT NULL,
`created_date` datetime DEFAULT NULL,
`data_damage` varchar(255) DEFAULT NULL,
`energysid` int(11) DEFAULT NULL,
`is_health` int(11) DEFAULT NULL,
`meter_type` int(11) DEFAULT NULL,
`posid` int(11) DEFAULT NULL,
`pro_code` varchar(255) DEFAULT NULL,
`timestamp` datetime DEFAULT NULL,
`value` double DEFAULT NULL,
`equid` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2730 DEFAULT CHARSET=utf8;

2.3 t_user存储的表

CREATE TABLE `sgly_11011500010010010_01` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`timestamp` datetime DEFAULT NULL,
`trendFlags` int(11) DEFAULT NULL,
`status` int(11) DEFAULT NULL,
`value` double DEFAULT NULL,
`TRENDFLAGS_TAG` varchar(500) DEFAULT NULL,
`STATUS_TAG` varchar(500) DEFAULT NULL,
`created_by` varchar(30) DEFAULT NULL,
`created_date` datetime DEFAULT NULL,
`trend_flags` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1810 DEFAULT CHARSET=utf8;

3.存储过程

delimiter $$
drop procedure if exists p_simulate_dynamic_cursor;
create procedure p_simulate_dynamic_cursor()
begin
declare v_sql varchar(4000);

declare v_field varchar(4000);

declare v_result varchar(4000) default '';

declare cur_temp cursor for
select v.* from view_temp_20150701 v;
declare continue handler for not found set v_field = null;

set v_sql = 'create view view_temp_20150701 as select t.id from t_user t';
set @v_sql = v_sql;
prepare statement from @v_sql;
execute statement;
deallocate prepare statement;
open cur_temp;
fetch cur_temp into v_field;

while(v_field is not null) do
-- declare cur_table_data cursor for select d.* from view_temp_data d;
-- set v_result = concat(v_result, v_field, ',');
CALL p2_simulate_dynamic_cursor(v_field);
fetch cur_temp into v_field;
end while;
close cur_temp;

drop view if exists view_temp_20150701;
end;
$$
delimiter ;
-- call p_simulate_dynamic_cursor();

-- ----------------------------另一个存储过程动态游标-------------------------------------
delimiter $$
drop procedure if exists p2_simulate_dynamic_cursor;

create procedure p2_simulate_dynamic_cursor(IN tableName varchar(4000))
begin
DECLARE done INT DEFAULT 0;
DECLARE equiName VARCHAR(400);
declare v_sql varchar(4000);
declare v_time TIMESTAMP;
declare v_value DOUBLE;
declare v_result varchar(4000) default '';
declare cur_temp cursor for select timestamp,value from view_temp_data;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET equiName=SUBSTR(tableName,6,17);
set v_sql = concat('create view view_temp_data as select timestamp,value from ' ,tableName);

set @v_sql = v_sql;
prepare statement from @v_sql;

execute statement;
deallocate prepare statement;

open cur_temp;
fetch cur_temp into v_time,v_value;
while (done=0) do
INSERT INTO ibms_equipmentbasedata_minute(timestamp,value,equid) VALUES(v_time,v_value,equiName);
set v_result = concat(v_result,v_time, v_value, ',');
fetch cur_temp into v_time,v_value;
end while;
close cur_temp;
select v_result;

drop view if exists view_temp_data;
end;
$$
delimiter ;

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