使用“if”和“else”存储过程 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6326082/
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
using "if" and "else" Stored Procedures MySQL
提问by Raúl Nú?ez Cuevas
I'm having some difficulties when trying to create this stored procedure, any kind of help is welcome:
我在尝试创建此存储过程时遇到了一些困难，欢迎提供任何帮助：
create procedure checando(in nombrecillo varchar(30), in contrilla varchar(30), out resultado int)
begin
if exists (select * from compas where nombre = nombrecillo and contrasenia = contrilla) then
set resultado = 0;
else if exists (select * from compas where nombre = nombrecillo) then
set resultado = -1;
else
set resultado = -2;
end if;
end;
The table I'm working on is:
我正在处理的表是：
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| Nombre | varchar(30) | YES | | NULL | |
| contrasenia | varchar(30) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
回答by Bohemian
The problem is you either haven't closed your ifor you need an elseif:
问题是你要么没有关闭你的，if要么你需要一个elseif：
create procedure checando(
in nombrecillo varchar(30),
in contrilla varchar(30),
out resultado int)
begin
if exists (select * from compas where nombre = nombrecillo and contrasenia = contrilla) then
set resultado = 0;
elseif exists (select * from compas where nombre = nombrecillo) then
set resultado = -1;
else
set resultado = -2;
end if;
end;
回答by Octavian Vladu
I think that this construct: if exists (select...is specific for MS SQL. In MySQL EXISTSpredicate tells you whether the subquery finds any rows and it's used like this: SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
我认为这个构造：if exists (select...是特定于 MS SQL 的。在 MySQL 中，EXISTS谓词告诉您子查询是否找到任何行，它的使用方式如下：SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
You can rewrite the above lines of code like this:
您可以像这样重写上面的代码行：
DELIMITER $$
CREATE PROCEDURE `checando`(in nombrecillo varchar(30), in contrilla varchar(30), out resultado int)
BEGIN
DECLARE count_prim INT;
DECLARE count_sec INT;
SELECT COUNT(*) INTO count_prim FROM compas WHERE nombre = nombrecillo AND contrasenia = contrilla;
SELECT COUNT(*) INTO count_sec FROM FROM compas WHERE nombre = nombrecillo;
if (count_prim > 0) then
set resultado = 0;
elseif (count_sec > 0) then
set resultado = -1;
else
set resultado = -2;
end if;
SELECT resultado;
END
回答by Extreme
you can use CASEWHENas follow as achieve the as IFELSE.
您可以使用CASEWHEN如下实现 as IFELSE。
SELECT FROM A a
LEFT JOIN B b
ON a.col1 = b.col1
AND (CASE
WHEN a.col2 like '0%' then TRIM(LEADING '0' FROM a.col2)
ELSE substring(a.col2,1,2)
END
)=b.col2;
p.s:just in case somebody needs this way.
ps：以防万一有人需要这种方式。
