CREATE DEFINER=`root`@`%` PROCEDURE `sp_select_thresholds`(
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 thresholds_temp;
create temporary table thresholds_temp(
alarmtype_name varchar(40) not null default 'N/A'
);
insert into thresholds_temp(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 thresholds_temp 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 thresholds_temp;
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_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 @test1 = concat( "update thresholds_temp set ",
alarmlevelname,"=",thresholdsvalue," where alarmtype_name='",
alarmtypename,"';");
prepare s1 from @test1;
execute s1;
deallocate prepare s1;
end if;
until done2 end repeat;
close cur_2;
set done2=0;
end;
end if;
until done1 end repeat;
close cur_1;
select * from thresholds_temp;
drop table if exists thresholds_temp;
end;SQL执行错误,#1064,从数据库响应:
You have an error in your SQL syntax;check the....to use near 'update thr where alarmtype_name='df' an line 1。
从这个报出的error来看,问题应该出在
: set @test1 = concat( "update thresholds_temp set ",
alarmlevelname,"=",thresholdsvalue," where alarmtype_name='",
alarmtypename,"';");
prepare s1 from @test1;
execute s1;
deallocate prepare s1;
可是这我的update语句明显和报的错误的不一样啊,并且调用的时候,第一次调用会出现这个error,而后再call则出现期望结果,高手们给指点下,这是什么诡异事情,谢谢!
select @test1;
select 'alarmlevelname',alarmlevelname;
select 'thresholdsvalue',thresholdsvalue;
select concat( "update thresholds_temp set ",
alarmlevelname,"=",thresholdsvalue," where alarmtype_name='",
alarmtypename,"';");看看这些结果是什么?如果一切正常,估计就是时序上的问题了。