一个存储过程如何也改不好，求围观

一个存储过程怎么也改不好，求围观

SQL code

mysql> DELIMITER //mysql> CREATE PROCEDURE updataAlarmID()    -> BEGIN    -> set @i=0;    -> set @sqlcot="select count(overinfo.rule) into @rlenght from overinfo,inidate where overinfo.classID=inidate.classID and inidate.alarmID is null";    -> PREPARE prod FROM @sqlcot;    -> EXECUTE prod;    ->    -> set @sqlRule1="select overinfo.rule into @ruleWord from overinfo,inidatewhere overinfo.classID=inidate.classID and inidate.alarmID is null limit ";    -> set @sqlin1="update inidate,overinfo set inidate.alarmID=overinfo.alarmID where inidate.content ";    -> set @sqlin2=" and inidate.classID=overinfo.classID and overinfo.rule= ";    ->    -> while @i<@rlenght do    ->     set @sqlRule=concat(@sqlRule1,@i,",1");    ->     PREPARE proRule FROM @sqlRule;    ->     EXECUTE proRule;    ->     @sqlIn=concat(@sqlin1,@ruleWord,@sqlin2,@ruleWord);    ->     PREPARE proIn FROM @sqlIn;    ->     EXECUTE proIn;    ->     set @i=@i+1;    -> end while;    ->    -> END    -> //ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near '@sqlIn=concat(@sqlin1,@ruleWord,@sqlin2,@ruleWord);    PREPARE proIn FROM ' at line 14

写了一个存储过程 ，这个报错error是和“@”使用有关吗，谁能帮我看看？
好像是不能加@，但是我把@全去掉，还是报错，说未知系统函数i

SQL code

ERROR 1193 (HY000): Unknown system variable 'i'

这个也贴上，我纯新手，实在想不通了，大家帮忙下，谢谢。

[解决办法]
set @sqlcot="select count(overinfo.rule) into @rlenght from overinfo,inid
ate where overinfo.classID=inidate.classID and inidate.alarmID is null";
 PREPARE prod FROM @sqlcot;
 EXECUTE prod;

这个也不需要这样吧，直接 select count(overinfo.rule) into rlengh_int from overinfo,inid
ate where overinfo.classID=inidate.classID and inidate.alarmID is null;
就可以了


[解决办法]
-> set @sqlRule=concat(@sqlRule1,@i,",1");
-> PREPARE proRule FROM @sqlRule;
-> EXECUTE proRule;
-> @sqlIn=concat(@sqlin1,@ruleWord,@sqlin2,@ruleWord);

你前面还知道要用 set ，后面为什么就没了？

查看更多 下一篇