The IF statement is used to construct basic conditional statements, allowing different code blocks to be executed based on the 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 condition expression that evaluates to True or False. |
| statement_list | A list of SQL/PL statements to be executed. Each statement is terminated with a semicolon (;). |
| ELSEIF | Optional. Used to specify additional conditional branches. Multiple ELSEIF clauses can be included. |
| ELSE | Optional. A list of statements to be executed when none of the conditions are met. |
Considerations
The IF statement can include THEN, ELSE, and ELSEIF clauses and must be terminated with END IF.
If the specified search_condition evaluates to True, the corresponding THEN or ELSEIF clause statements are executed. If none of the search_condition match, the statement_list in the ELSE clause is executed.
Each statement_list consists of one or more SQL statements. 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, the IF ... END IF block can be nested within other control statements, including IF statements. Each IF must be terminated with its own END IF;. You can indent the code to make nested flow control blocks easier to read.
Examples
Example 1: 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 statement.
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 is greater than 3. |
+----------------------+
1 row in set
Example 3: Using 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 ;