对了 @ruleWord 查询的结果应该是 “like '$EMS'”.目的是一个判断标准 所以@sqlIn=concat(@sqlin1,@ruleWord,@sqlin2,@ruleWord);的结果:update inidate,overinfo set inidate.alarmID=overinfo.alarmID where inidate.content like '$EMS' and inidate.classID=overinfo.classID and overinfo.rule=“like '$EMS'”是不是最后应该有个双引号需要转义一下啊
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 ,后面为什么就没了?
mysql> DELIMITER ; mysql> call updataAlarmID(); ERROR 1054 (42S22): Unknown column 'ruleWord' in 'field list' mysql> drop procedure updataAlarmID; Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER // mysql> CREATE PROCEDURE updataAlarmID() -> BEGIN -> DECLARE i int; -> DECLARE rlenght int; -> DECLARE sqlRule1 varchar(150) default "select overinfo.rule into @ruleWor d from overinfo,inidate where overinfo.classID=inidate.classID and inidate.alarm ID is null limit "; -> DECLARE sqlin1 varchar(150) default "update inidate,overinfo set inidate. alarmID=overinfo.alarmID where inidate.content "; -> DECLARE sqlin2 varchar(150) default ' and inidate.classID=overinfo.classI D and overinfo.rule="'; -> set @ruleWord="0"; -> set i=0; -> select count(overinfo.rule) into rlenght from overinfo,inidate where over info.classID=inidate.classID and inidate.alarmID is null; -> while i<rlenght do -> set @sqlRule=concat(sqlRule1,i,",1"); -> PREPARE proRule FROM @sqlRule; -> EXECUTE proRule; -> set @sqlIn=concat(sqlin1,@ruleWord,sqlin2,@ruleWord,'"'); -> PREPARE proIn FROM @sqlIn; -> EXECUTE proIn; -> set i=i+1; -> end while; -> END -> // Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ; mysql> call updataAlarmID(); Query OK, 0 rows affected, 2 warnings (0.00 sec)在各位的帮助下,随略有坎坷,还是搞出来了, 执行结果updata成功了,但是为什么2warnings呢?怎么看啊,太放心啊?
@ruleWord 查询的结果应该是 “like '$EMS'”.目的是一个判断标准
所以@sqlIn=concat(@sqlin1,@ruleWord,@sqlin2,@ruleWord);的结果:update inidate,overinfo
set inidate.alarmID=overinfo.alarmID
where inidate.content like '$EMS'
and inidate.classID=overinfo.classID
and overinfo.rule=“like '$EMS'”是不是最后应该有个双引号需要转义一下啊
到while 得时候过不去,说这句和下面一句是一行了。
是说while必须在存储过程中使用吗?
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;
就可以了
-> PREPARE proRule FROM @sqlRule;
-> EXECUTE proRule;
-> @sqlIn=concat(@sqlin1,@ruleWord,@sqlin2,@ruleWord);你前面还知道要用 set ,后面为什么就没了?
mysql> call updataAlarmID();
ERROR 1054 (42S22): Unknown column 'ruleWord' in 'field list'
mysql> drop procedure updataAlarmID;
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //
mysql> CREATE PROCEDURE updataAlarmID()
-> BEGIN
-> DECLARE i int;
-> DECLARE rlenght int;
-> DECLARE sqlRule1 varchar(150) default "select overinfo.rule into @ruleWor
d from overinfo,inidate where overinfo.classID=inidate.classID and inidate.alarm
ID is null limit ";
-> DECLARE sqlin1 varchar(150) default "update inidate,overinfo set inidate.
alarmID=overinfo.alarmID where inidate.content ";
-> DECLARE sqlin2 varchar(150) default ' and inidate.classID=overinfo.classI
D and overinfo.rule="';
-> set @ruleWord="0";
-> set i=0;
-> select count(overinfo.rule) into rlenght from overinfo,inidate where over
info.classID=inidate.classID and inidate.alarmID is null;
-> while i<rlenght do
-> set @sqlRule=concat(sqlRule1,i,",1");
-> PREPARE proRule FROM @sqlRule;
-> EXECUTE proRule;
-> set @sqlIn=concat(sqlin1,@ruleWord,sqlin2,@ruleWord,'"');
-> PREPARE proIn FROM @sqlIn;
-> EXECUTE proIn;
-> set i=i+1;
-> end while;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;
mysql> call updataAlarmID();
Query OK, 0 rows affected, 2 warnings (0.00 sec)在各位的帮助下,随略有坎坷,还是搞出来了,
执行结果updata成功了,但是为什么2warnings呢?怎么看啊,太放心啊?