MySQL：存储过程中的 IF / THEN 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5983600/
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
MySQL: IF / THEN statements in stored procedures
提问by tchaymore
I'm writing a stored procedure that uses multiple IF / THEN statements that also need to execute multiple queries if they evaluate to true. Problem is, I can't seem to find any examples of the appropriate syntax. From the MySQL dev handbook, it seems like I could have multiple queries in the "statement_list," but so far I can't get it to work.
我正在编写一个使用多个 IF / THEN 语句的存储过程，如果它们评估为真，也需要执行多个查询。问题是，我似乎找不到任何适当语法的示例。从MySQL 开发手册来看，似乎我可以在“statement_list”中有多个查询，但到目前为止我无法让它工作。
Here's what I'm trying to do:
这是我想要做的：
SET agency =
COALESCE((SELECT org_agency_o_id
FROM orgs_agencies
WHERE org_agency_code = maj_agency_cat)
,(SELECT min(org_id)
FROM orgs
WHERE org_name LIKE CONCAT('U.S.',SUBSTRING(maj_agency_cat,5))))
IF agency IS NULL THEN
-- execute multiple queries
INSERT INTO orgs (org_name
,org_name_length
,org_type
,org_sub_types)
VALUES (CONCAT('U.S. ',SUBSTRING(maj_agency_cat,5))
,LENGTH(CONCAT('U.S. ',SUBSTRING(maj_agency_cat,5)))
,'org','Org,GovernmentEntity,Federal,Agency');
SET agency = LAST_INSERT_ID();
END IF;
The error:
错误：
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
'IF agency IS NULL THEN INSERT INTO orgs (org_name,org_name_length,org_type,'at line 53
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取
'IF agency IS NULL THEN INSERT INTO orgs (org_name,org_name_length,org_type,'在第 53 行附近使用的正确语法
Any ideas? I know it has to be something simple, so I would greatly appreciate anybody's input.
有任何想法吗？我知道它必须很简单，所以我非常感谢任何人的意见。
回答by Johan
You got a few issues as far as I can see:
据我所知，您遇到了一些问题：
- As David pointed out, each and every statement needs to be terminated by a
; - If you do a
SELECT, better make sure it can only select one value by doing aLIMIT 1; If you've got an aggregate function likemin()then only one value can come out. - If you writing the procedure using the
CREATE PROCEDURE ...syntax, don't forget to setDELIMITER $$before theCREATE PROCEDURE ... END $$body and aDELIMITER ;after. - If you have multiple statements inside your
IF THEN ... END IFblock, it's a good idea to put them inside aBEGIN ... END;block. - If you have a return value, like agency here, why not make it a
FUNCTION name (arg1: INTEGER) RETURNS INTEGERinstead of aPROCEDURE name (IN arg1 INTEGER, OUT agency INTEGER). The function is much more versatile.
- 正如大卫所指出的，每一条语句都需要以
; - 如果你做 a
SELECT，最好确保它只能通过做 a 来选择一个值LIMIT 1；如果你有一个聚合函数，min()那么只有一个值可以出来。 - 如果您使用
CREATE PROCEDURE ...语法编写过程，请不要忘记DELIMITER $$在CREATE PROCEDURE ... END $$正文之前和DELIMITER ;之后设置。 - 如果
IF THEN ... END IF块中有多个语句，最好将它们放在一个BEGIN ... END;块中。 - 如果你有一个返回值，比如这里的代理，为什么不把它变成 a
FUNCTION name (arg1: INTEGER) RETURNS INTEGER而不是 aPROCEDURE name (IN arg1 INTEGER, OUT agency INTEGER)。该功能更加通用。
DELIMITER $$
CREATE PROCEDURE name(arg1 INTEGER, arg2 INTEGER, ...)
BEGIN
SELECT SET agency =
COALESCE((SELECT org_agency_o_id
FROM orgs_agencies
WHERE org_agency_code = maj_agency_cat) LIMIT 1,
(SELECT min(org_id) FROM orgs
WHERE org_name LIKE CONCAT('U.S.',SUBSTRING(maj_agency_cat,5))));
IF agency IS NULL THEN BEGIN
-- execute multiple queries
INSERT INTO orgs (org_name
,org_name_length
,org_type
,org_sub_types)
VALUES (CONCAT('U.S. ',SUBSTRING(maj_agency_cat,5))
,LENGTH(CONCAT('U.S. ',SUBSTRING(maj_agency_cat,5)))
,'org','Org,GovernmentEntity,Federal,Agency');
SET agency = LAST_INSERT_ID();
END; END IF;
END $$
DELIMITER ;
回答by David Fells
No semicolon after your first SET statement.
第一个 SET 语句后没有分号。
