mysql 程序不允许我声明变量

Question

我此过程的目标是使用一周中的某一天来确定服务的调整后价格。例如，周末价格上涨10%。周一和周五价格相同。周二到周四则减少 5%。最终的消息应该是新的调整后的价格。

我认为我这样做是正确的。但我在声明值时遇到了重大错误。

任何帮助将不胜感激。谢谢。

Create procedure DayOfWeek( inout p_price decimal(7,2), inout p_date date, out v_msg varchar(10))
begin
    declare increase10 double;
    set increase10 = p_price * (1.1);

    declare decrease5 double;   
    set decrease5 = p_price * (0.95);

    declare increase10cast varchar(10); 
    set increase10cast := cast(increase10 as char);

    declare decrease5cast varchar(10); 
    set decrease5cast := cast(decrease10 as char);

    declare regular varchar(10);
    set regular := cast(p_price as char );

    case 
    when p_price is null then
        set v_msg := 'null';
    when p_date is null then
        set v_msg := 'null';
    when weekday(p_date) in (0, 6) then
        set v_msg := increase10cast;
    when weekday(p_date) in (1, 5) then
        set v_msg := regular;   
    when weekday(p_date) in (2, 2, 4) then
        set v_msg := decrease5cast;
    end case;   

end;
#

Answer 1

首先声明所有变量，然后使用 set 语句 -

Create procedure a05_AdjustPrice_ByDayOfWeek( in p_price decimal(7,2), in p_date date, out v_msg varchar(10))
begin
    declare increase10 double;
    declare decrease5 double;   
    declare increase10cast varchar(10); 
    declare decrease5cast varchar(10); 
    declare regular varchar(10);

    set increase10 = p_price * (1.1);    
    set decrease5 = p_price * (0.95);    
    set increase10cast := cast(increase10 as char);    
    set decrease5cast := cast(decrease5 as char);    
    set regular := cast(p_price as char );

    case 
    when p_price is null then
        set v_msg := 'null';
    when p_date is null then
        set v_msg := 'null';
    when weekday(p_date) in (0, 6) then
        set v_msg := increase10cast;
    when weekday(p_date) in (1, 5) then
        set v_msg := regular;   
    when weekday(p_date) in (2, 2, 4) then
        set v_msg := decrease5cast;
    end case;   

end;

Declare 语句必须位于 Begin...End block 的开头。请参阅Docs

将 INOUT 参数更改为 IN 参数。你可以像这样执行它 -

call a05_AdjustPrice_ByDayOfWeek(100, '2012-09-09', @msg);
select @msg;