mysql - 存储过程sql语法错误

Question

我的数据库中有 5 个表

ApartamentClass(idclass,descript)
Room(idroom,idclass,beds,isfree)
ItemPrice(iditem,idclass,description,price)
Client(idclient,fio)
Active(idcontract,idclient,idroom,days,price,idclass)

我需要创建一个存储过程来检查是否存在具有特定类别(param1)和床位数量(param2)的空闲房间，并为此房间创建租赁契约(Contract)和客户端(fio)(param3) 几天(param4)。价格取决于房间的等级(较高等级 -> 一张床一日的价格较高)。

create procedure UserRequest(
    in param1 int,
    in param2 int,
    in param3 varchar(100),
    in param4 int
)
begin   
    declare iroom int default 0;
    select idroom into iroom from room
    where beds = param2 and idclass = param1 and isfree = 0
    limit 1;


    if not (iroom=0) then
        update room set isfree = 1
        where idroom = iroom;
    end if;


    declare iclient int default 0;
    select idclient into iclient from client
    where fio = param3
    limit 1;


    declare bedprice decimal default 0.0;
    select (param2 * price) into bedprice from itemprice
    where description = "bed" and idclass = param1;

    declare dayprice decimal default 0.0;
    select (param4 * price) into dayprice from itemprice
    where description = "day" and idclass = param1;

    declare price decimal default 0.0;
    set price = bedprice + dayprice;


    insert into active(idclient,idroom,days,price,idclass)
    values(iclient,iroom,param4,price,param1);
end

但我总是遇到 SQL 语法错误。我不知道问题出在哪里。
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'declare iclient int default 0; 附近使用的正确语法。
从客户端选择idclient进入iclient
第 20 行 wh'

Answer 1

所有 DECLARE 语句必须位于 BEGIN...END block 的开头，如 MySQL 文档中所述: http://dev.mysql.com/doc/refman/5.0/en/declare.html

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

所以，您可能想尝试以下代码:

create procedure UserRequest(
    in param1 int,
    in param2 int,
    in param3 varchar(100),
    in param4 int
)
begin   
    declare iroom int default 0;
    declare iclient int default 0;
    declare bedprice decimal default 0.0;
    declare dayprice decimal default 0.0;
    declare price decimal default 0.0;

    select idroom into iroom from room
    where beds = param2 and idclass = param1 and isfree = 0
    limit 1;


    if not (iroom=0) then
        update room set isfree = 1
        where idroom = iroom;
    end if;


    select idclient into iclient from client
    where fio = param3
    limit 1;

    select (param2 * price) into bedprice from itemprice
    where description = "bed" and idclass = param1;

    select (param4 * price) into dayprice from itemprice
    where description = "day" and idclass = param1;

    set price = bedprice + dayprice;


    insert into active(idclient,idroom,days,price,idclass)
    values(iclient,iroom,param4,price,param1);
end