CREATE DEFINER=`root`@`%` PROCEDURE `sp_select_thresholds_test`(
mediatypeid int
)
begin
declare alarmtypename varchar(40);
declare done int default 0;
declare alarmlevelname varchar(40);
declare thresholdsvalue double(20,8);
declare cur_1 cursor for
select alarmlevel_name
from t_alarmlevel;
declare cur_2 cursor for
select alarmtype_name from temp_thresholds;
declare continue handler for not found set done = 1;
drop table if exists temp_thresholds;
create temporary table temp_thresholds(
alarmtype_name varchar(40) not null default 'N/A'
);
insert into temp_thresholds(alarmtype_name) select alarmtype_name from t_alarmtype
where mediatype_id=mediatypeid;
open cur_1;
open cur_2;
repeat
fetch cur_1 into alarmlevelname;
fetch cur_2 into alarmtypename;
if not done then
set @test = concat( "alter table temp_thresholds add ",alarmlevelname," double(20,8) not null default 0.00;");
prepare s0 from @test;
execute s0;
deallocate prepare s0;
set @test = concat("
select a.thresholds_value into @thresholdsvalue from t_video_thresholds a,
t_alarmlevel b,t_alarmtype c where a.alarmlevel_id=b.alarmlevel_id and
a.alarmtype_id=c.alarmtype_id and b.alarmlevel_name= '",
alarmlevelname,"' and c.alarmtype_name='",alarmtypename,"';");
prepare s0 from @test;
execute s0;
deallocate prepare s0;
set thresholdsvalue=@thresholdsvalue; set @test = concat( "update temp_thresholds set ",alarmlevelname,"=",thresholdsvalue," where alarmtype_name='",alarmtypename,"';");
prepare s0 from @test;
execute s0;
deallocate prepare s0;
end if;
until done end repeat;
close cur_2;
close cur_1;
select * from temp_thresholds;
drop table if exists temp_thresholds;
end;上面是一个含有2个光标的存储过程,我需要的是在光标1中(cur_1)嵌套光标2(cur_2),但上面的效果并没有实现嵌套,哪位大哥给指点下,光标的嵌套如何实现,谢谢!
http://denniswwh.itpub.net/post/15174/484623
mediatypeid int
)
begin
declare alarmtypename varchar(40);
declare done1,done2 int default 0;
declare alarmlevelname varchar(40);
declare thresholdsvalue double(20,8);
declare cur_1 cursor for
select alarmlevel_name
from t_alarmlevel;
declare continue handler for not found set done1 = 1;
drop table if exists temp_thresholds;
create temporary table temp_thresholds(
alarmtype_name varchar(40) not null default 'N/A'
);
insert into temp_thresholds(alarmtype_name) select alarmtype_name from t_alarmtype
where mediatype_id=mediatypeid;
open cur_1;
repeat
fetch cur_1 into alarmlevelname;
if not done1 then
set @test = concat( "alter table temp_thresholds add ",alarmlevelname," double(20,8) not null default 0.00;");
prepare s0 from @test;
execute s0;
deallocate prepare s0;
begin
declare cur_2 cursor for
select alarmtype_name from temp_thresholds;
declare continue handler for not found set done2 = 1;
open cur_2;
repeat
fetch cur_2 into alarmtypename;
if not done2 then
set @test = concat("
select a.thresholds_value into @thresholdsvalue from t_video_thresholds a,
t_alarmlevel b,t_alarmtype c where a.alarmlevel_id=b.alarmlevel_id and
a.alarmtype_id=c.alarmtype_id and b.alarmlevel_name= '",
alarmlevelname,"' and c.alarmtype_name='",alarmtypename,"';");
prepare s0 from @test;
execute s0;
deallocate prepare s0;
set thresholdsvalue=@thresholdsvalue;
set @test = concat( "update temp_thresholds set ",
alarmlevelname,"=",thresholdsvalue," where
alarmtype_name='",alarmtypename,"';");
prepare s0 from @test;
execute s0;
deallocate prepare s0;
end if;
until done2 end repeat;
close cur_2;
set done2=0;
end;
end if;
until done1 end repeat;
close cur_1;
select * from temp_thresholds;
drop table if exists temp_thresholds;
end;