The IF statement is used to construct basic conditional statements, executing different code blocks based on a condition.
Syntax
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
Parameters
Parameter |
Description |
|---|---|
| search_condition | A conditional expression that evaluates to True or False. |
| statement_list | The list of SQL/PL statements to be executed. Each statement must end with a semicolon (;). |
| ELSEIF | Optional. Specifies additional conditional branches. Multiple ELSEIF clause. |
| ELSE | Optional. A list of statements to execute when none of the preceding conditions is met. |
Usage instructions
An IF statement can contain THEN, ELSE, and ELSEIF clauses, and is terminated by END IF.
If the specified search_condition evaluates to True, the corresponding THEN or ELSEIF clause's statement list is executed. If no search_condition matches, the statement_list in the ELSE clause is executed.
Each statement_list consists of one or more SQL statements; an empty statement_list is not allowed.
In stored programs, the IF ... END IF block must be terminated with a semicolon (;). Like other flow control constructs, IF ... END IF blocks can be nested within other control statements, including other IF statements. Each IF must be terminated with its own END IF;. You can indent your code to make nested flow control blocks easier to read.
Examples
Example 1: A basic IF statement.
obclient> DELIMITER //
obclient> CREATE FUNCTION get_discount_level(order_amount DECIMAL(10,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE level VARCHAR(20);
IF order_amount >= 1000 THEN
SET level = 'VIP';
ELSEIF order_amount >= 500 THEN
SET level = 'Gold';
ELSE
SET level = 'Standard';
END IF;
RETURN level;
END //
obclient> DELIMITER ;
obclient> SELECT get_discount_level(1500);
The query result is as follows:
+------------------------+
| get_discount_level(1500) |
+------------------------+
| VIP |
+------------------------+
1 row in set
Example 2: Nested IF statements.
obclient> DELIMITER //
obclient> CREATE FUNCTION compare_numbers(n INT, m INT)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
DECLARE result VARCHAR(50);
IF n = m THEN
SET result = 'is equal to';
ELSE
IF n > m THEN
SET result = 'is greater than';
ELSE
SET result = 'is less than';
END IF;
END IF;
SET result = CONCAT(n, ' ', result, ' ', m, '.');
RETURN result;
END //
obclient> DELIMITER ;
obclient> SELECT compare_numbers(5, 3);
The query result is as follows:
+----------------------+
| compare_numbers(5, 3) |
+----------------------+
| 5 greater than 3. |
+----------------------+
1 row in set
Example 3: Use the IF statement in a stored procedure.
obclient> DELIMITER //
obclient> CREATE PROCEDURE update_employee_salary(IN emp_id_param INT, IN new_salary DECIMAL(10,2))
BEGIN
DECLARE current_salary DECIMAL(10,2);
SELECT salary INTO current_salary FROM employee_info WHERE emp_id = emp_id_param;
IF new_salary > current_salary * 1.2 THEN
UPDATE employee_info SET salary = current_salary * 1.2 WHERE emp_id = emp_id_param;
ELSEIF new_salary < current_salary * 0.8 THEN
UPDATE employee_info SET salary = current_salary * 0.8 WHERE emp_id = emp_id_param;
ELSE
UPDATE employee_info SET salary = new_salary WHERE emp_id = emp_id_param;
END IF;
END //
obclient> DELIMITER ;