请教请教:
以下写的存储过程,看了半天没明白哪里错了,但是执行的时候总报载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;
解决方案 »
- 关于mysql函数 count与sum过滤行异常行为的问题
- InnoDb什么情况会运行运行着突然崩溃?或者调节什么参数能避免这种情况发生?
- 怎么查询某个字段最后修改的时间
- 批量修改数据
- 求一mysql的sql语句
- 通过odbc连接局域网内mysql服务器出错
- Mysql 存储结构求助 200分赠送
- 求救了!请高手指点!Warning: Can't connect to MySQL server on 'localhost' (10013)
- 数据库的索引,书上说:当数据量超大的时候,B-Tree索引就无法起作用了,如何理解?
- 按类型分页查询
- 视图,合并字段
- Could not start the service 安装MySQL的时候出错啦!哪位大虾帮忙看看啊!
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)