在存储过程中未找到Mysql CASE进行CASE STATEMENT [英] Mysql CASE NOT FOUND for CASE STATEMENT on a Stored Procedure
本文介绍了在存储过程中未找到Mysql CASE进行CASE STATEMENT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
im试图创建具有多个CASE STATEMENTS的存储过程 我有以下存储过程:
im trying to create a stored procedure that have multiples CASE STATEMENTS I have the following stored procedure:
BEGIN
CASE @olds
WHEN 'emp' THEN
CASE @news
WHEN 'loc' THEN
UPDATE equipos SET pe=pe-1,pg=pg+1 WHERE id=@eqloc;
UPDATE equipos SET pe=pe-1,pp=pp+1 WHERE id=@eqvis;
UPDATE partidos SET `eqgan`=@news WHERE id=@mst;
UPDATE log_partidos SET `status`=@news WHERE `match`=@mst;
WHEN 'vis' THEN
UPDATE equipos SET pe=pe-1,pg=pg+1 WHERE id=@eqvis;
UPDATE equipos SET pe=pe-1,pp=pp+1 WHERE id=@eqloc;
UPDATE partidos SET `eqgan`=@news WHERE id=@mst;
UPDATE log_partidos SET `status`=@news WHERE `match`=@mst;
END CASE;
WHEN 'loc' THEN
CASE @news
WHEN 'emp' THEN
UPDATE equipos SET pe=pe+1,pg=pg-1 WHERE id=@eqloc;
UPDATE equipos SET pe=pe+1,pp=pp-1 WHERE id=@eqvis;
UPDATE partidos SET `eqgan`=@news WHERE id=@mst;
UPDATE log_partidos SET `status`=@news WHERE `match`=@mst;
WHEN 'vis' THEN
UPDATE equipos SET pp=pp-1,pg=pg+1 WHERE id=@eqvis;
UPDATE equipos SET pg=pg-1,pp=pp+1 WHERE id=@eqloc;
UPDATE partidos SET `eqgan`=@news WHERE id=@mst;
UPDATE log_partidos SET `status`=@news WHERE `match`=@mst;
END CASE;
WHEN 'vis' THEN
CASE @news
WHEN 'emp' THEN
UPDATE equipos SET pe=pe+1,pg=pg-1 WHERE id=@eqvis;
UPDATE equipos SET pe=pe+1,pp=pp-1 WHERE id=@eqloc;
UPDATE partidos SET `eqgan`=@news WHERE id=@mst;
UPDATE log_partidos SET `status`=@news WHERE `match`=@mst;
WHEN 'loc' THEN
UPDATE equipos SET pp=pp-1,pg=pg+1 WHERE id=@eqloc;
UPDATE equipos SET pg=pg-1,pp=pp+1 WHERE id=@eqvis;
UPDATE partidos SET `eqgan`=@news WHERE id=@mst;
UPDATE log_partidos SET `status`=@news WHERE `match`=@mst;
END CASE;
END CASE;
END
每次执行此过程时,我都会遇到以下错误:"CASE语句未找到案例" 我在做什么错了?
Everytime im executing this procedure i got the following error is: "Case not found for CASE statement" What im doing wrong?
推荐答案
这是MySQL特有的问题.
在MySQL中,大小写必须始终具有有效的匹配项,因此必须具有ELSE子句.
并且由于匹配后的语句不能为空,因此可以像下面这样用空块填充它:
This is a MySQL specific problem.
In MySQL, a case must always have a valid match, thus an ELSE clause.
And as the statement after the match cannot be empty, you can fill it up with an empty block like so:
ELSE BEGIN END;
例如,一个有效的情况是:
So a valid case would be, for example:
CASE @news
WHEN 'emp' THEN
UPDATE equipos SET pe=pe+1,pg=pg-1 WHERE id=@eqvis;
WHEN 'loc' THEN
UPDATE equipos SET pp=pp-1,pg=pg+1 WHERE id=@eqloc;
ELSE BEGIN END;
END CASE;
这篇关于在存储过程中未找到Mysql CASE进行CASE STATEMENT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文