定义
IF-THEN-ELSE语句通常用在根据不同条件执行不同SQL的场景。
语法
IF condition1 THEN
{...statements to execute when condition1 is TRUE...}
[ ELSEIF condition2 THEN
{...statements to execute when condition2 is TRUE...} ]
[ ELSE
{...statements to execute when both condition1 and condition2 are FALSE...} ]
END IF;
参数
参数 | 描述 |
---|---|
ELSEIF | 可选,按顺序执行判断条件,当结果为true时,执行其中的子句 |
ELSE | 可选,当前面的条件都不为true时,执行其中的子句 |
例子
DELIMITER //
CREATE FUNCTION IncomeLevel ( monthly_value INT )
RETURNS varchar(20)
BEGIN
DECLARE income_level varchar(20);
IF monthly_value <= 4000 THEN
SET income_level = 'Low Income';
ELSEIF monthly_value > 4000 AND monthly_value <= 7000 THEN
SET income_level = 'Avg Income';
ELSE
SET income_level = 'High Income';
END IF;
RETURN income_level;
END; //
DELIMITER ;