请教请教:
以下写的存储过程,看了半天没明白哪里错了,但是执行的时候总报载end while;附件有错,请教什么问题
create procedure setRoomCurrentPrice() begin
declare stop int default 0;
declare roomId int ;
declare price decimal(11,0);
declare specialPrice decimal(11,0) ;
declare cur cursor for select id, etprice from room; declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
open cur;
fetch cur into roomId,price;
set specialPrice = 0;
while stop<>1 do
select price from specialprice where rid=roomId and now() between starttime and endtime into specialPrice;
if(specialPrice<>0) then
update room set currentprice=specialPrice where id = roomId;
else if(specialPrice=0) then
update room set currentprice=etprice where id = roomId;
end if;
fetch cur into roomId,price;
end while;
close cur; end;
以下写的存储过程,看了半天没明白哪里错了,但是执行的时候总报载end while;附件有错,请教什么问题
create procedure setRoomCurrentPrice() begin
declare stop int default 0;
declare roomId int ;
declare price decimal(11,0);
declare specialPrice decimal(11,0) ;
declare cur cursor for select id, etprice from room; declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
open cur;
fetch cur into roomId,price;
set specialPrice = 0;
while stop<>1 do
select price from specialprice where rid=roomId and now() between starttime and endtime into specialPrice;
if(specialPrice<>0) then
update room set currentprice=specialPrice where id = roomId;
else if(specialPrice=0) then
update room set currentprice=etprice where id = roomId;
end if;
fetch cur into roomId,price;
end while;
close cur; end;
declare stop int default 0;
declare roomId int ;
declare price decimal(11,0);
declare specialPrice decimal(11,0) ;
declare cur cursor for select id, etprice from room;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
open cur;
fetch cur into roomId,price;
set specialPrice = 0;
while stop <>1 do
select price from specialprice where rid=roomId and now() between starttime and endtime into specialPrice;
if(specialPrice <>0) then
update room set currentprice=specialPrice where id = roomId;
else
if (specialPrice=0) then
update room set currentprice=etprice where id = roomId;
end if;
end if;
fetch cur into roomId,price;
end while;
close cur;
end;注意修改红色部分即可
declare stop int default 0;
declare roomId int ;
declare price decimal(11,0);
declare specialPrice decimal(11,0) ;
declare cur cursor for select id, etprice from room; declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
open cur;
fetch cur into roomId,price;
set specialPrice = 0;
while stop <>1 do
select price from specialprice where rid=roomId and now() between starttime and endtime into specialPrice;
if(specialPrice <>0) then
update room set currentprice=specialPrice where id = roomId;
ELSEIF (specialPrice=0) then -- else if -->> ELSEIF , changed by ACMAIN
update room set currentprice=etprice where id = roomId;
end if;
fetch cur into roomId,price; end while; close cur; end
//
if (specialPrice=0) then
update room set currentprice=etprice where id = roomId;
end if; -------->
else
update room set currentprice=etprice where id = roomId;
end if;
begin
declare stop int default 0;
declare roomId int ;
declare price decimal(11,0);
declare specialPrice decimal(11,0) ;
declare cur cursor for select id, etprice from room;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
open cur;
fetch cur into roomId,price;
set specialPrice = 0;
while stop <>1 do
select price from specialprice where rid=roomId and now() between starttime and endtime into specialPrice;
if(specialPrice <>0) then
update room set currentprice=specialPrice where id = roomId;
else
update room set currentprice=etprice where id = roomId;
end if;
fetch cur into roomId,price;
end while;
close cur;
end;
begin
DECLARE `stop` INT DEFAULT 0;
DECLARE roomId INT;
DECLARE price DECIMAL(11,0);
DECLARE specialPrice DECIMAL(11,0);
DECLARE cur CURSOR FOR SELECT `id`,etprice FROM room;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET `stop`=1;
OPEN cur;
FETCH cur INTO roomId,price;
SET specialPrice=0;
WHILE `stop`<>1 DO
CASE WHEN specialPrice=0 THEN
UPDATE room SET currentprice=etprice WHERE `id`=roomId;
ELSE
UPDATE room SET currentprice=etprice WHERE `id`=roomId;
END CASE;
FETCH cur INTO roomId,price;
END WHILE;CLOSE cur;
END$$
select rid,price
from specialprice
where now() between starttime and endtime
) b on room.id=b.rid
set room.currentprice=COALESCE(b.price, etprice)