如何在 mysql 存储过程中使用 case-when 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5167985/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use a case-when statement in a mysql stored procedure?
提问by watkib
I want to set the session_id automatically using the request_time parameter so i opted for a mysql stored procedure that contains a case statement.Here goes.
我想使用 request_time 参数自动设置 session_id，所以我选择了一个包含 case 语句的 mysql 存储过程。在这里。
create procedure upd_userinput(in request_time timestamp, out user_session_id int)
begin
update user_input;
case request_time
when time(request_time) < '9:15:00' && time(request_time) > '8:15:00'
then set user_session_id = 1;
when time(request_time)< '10:15:00' && time(request_time) > '11:15:00'
then set user_session_id =2;
end case;
end
//
However i get a 1064 error on enter after //. I have checked the mysql documentation i think the case syntax is correct.
但是，在 // 之后输入时出现 1064 错误。我检查了 mysql 文档，我认为 case 语法是正确的。
Help please.
请帮忙。
回答by WillDud
delimiter //
create procedure upd_userinput(in request_time TIMESTAMP,out time TIME, out user_session_id INT)
begin
set time = time(request_time);
case
when time < '09:15:00' && time > '08:15:00' then
set user_session_id = 1;
when time < '10:15:00' && time > '11:15:00' then
set user_session_id = 2;
else set user_session_id =3;
end case;
end //
回答by Sher Shah
the case must match at least one of the given statements, otherwise you will get this error. To avoid the error include an else in it.
case 必须至少匹配给定语句之一，否则您将收到此错误。为了避免错误，在其中包含一个 else。
create procedure upd_userinput(in request_time timestamp, out user_session_id int)
begin
update user_input;
case request_time
when time(request_time) < '9:15:00' && time(request_time) > '8:15:00' then
set user_session_id = 1;
when time(request_time)< '10:15:00' && time(request_time) > '11:15:00' then
set user_session_id =2;
else set user_session_id =0;
end case;
end
