mysql if then语句_MySQL：存储过程中的IF / THEN语句

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.

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

Any ideas? I know it has to be something simple, so I would greatly appreciate anybody's input.

解决方案

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 a LIMIT 1; If you've got an aggregate function like min() then only one value can come out.

If you writing the procedure using the CREATE PROCEDURE ... syntax, don't forget to set DELIMITER $$ before the CREATE PROCEDURE ... END $$ body and a DELIMITER ; after.

If you have multiple statements inside your IF THEN ... END IF block, it's a good idea to put them inside a BEGIN ... END; block.

If you have a return value, like agency here, why not make it a FUNCTION name (arg1: INTEGER) RETURNS INTEGER instead of a PROCEDURE name (IN arg1 INTEGER, OUT agency INTEGER). The function is much more versatile.

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 ;