A conditional control statement executes or skips a set of program codes by using the IF or CASE statement.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
The IF statement has the following forms:
IF THENIF THEN ELSEIF THEN ELSIF
The CASE statement selects from a series of conditions and then executes the corresponding statement. The CASE statement has the following forms:
Simple: A simple CASE statement evaluates the value of a single expression and compares it with multiple potential values.
Searched: A searched
CASEstatement evaluates several conditions and select the first condition that isTRUE.
The CASE statement is suitable for branched scenarios.
IF THEN statement
The syntax of the IF THEN statement is as follows:
IF condition THEN
statements
END IF;
If the value of condition is TRUE, the statements are executed.
Here is an example:
obclient> CREATE TABLE employees(
employee_id NUMBER(4,0),
name VARCHAR(10),
salary NUMBER(7,2)
);
Query OK, 0 rows affected
obclient> INSERT INTO employees (employee_id,name,salary)VALUES(105,'Adam',15000);
Query OK, 1 row affected
obclient> DECLARE
V_EMP_NAME employees.name%TYPE := 'Adam';
V_EMP_SAL employees.salary%TYPE;
V_bonus number := 0;
BEGIN
SELECT salary INTO V_EMP_SAL FROM employees
WHERE name=V_EMP_NAME;
IF V_EMP_SAL > 10000 THEN
V_bonus := V_EMP_SAL * 0.4;
END IF;
DBMS_OUTPUT.PUT_LINE(V_EMP_NAME||'''bonus: '||V_bonus);
END;
/
Query OK, 0 rows affected
Adam'bonus: 6000
The following example shows a long-winded IF statement that needs to be avoided in program design:
IF new_weight < minimum_weight THEN
isHealthy := TRUE;
ELSE
isHealthy := TRUE;
END IF;
As a better alternative, you can directly assign the value of the Boolean expression to a Boolean variable: isHealthy:= new_weight < minimum_weight;.
The Boolean variable can be TRUE, FALSE, or NULL, which directly indicates the condition. So, do not write an IF statement like this:
IF isHealthy = FALSE THEN
RAISE start_one_month_diet;
END IF;
Instead, write it like this:
IF NOT isHealth THEN
RAISE start_one_month_diet;
END IF;
IF THEN ELSE statement
The syntax of the IF THEN ELSE statement is as follows:
IF condition THEN
statements
ELSE
else_statements
END IF;
If the value of condition is TRUE, the statements are executed. Otherwise, the statements specified by else_statements are executed.
Here is an example:
obclient> CREATE TABLE employees(
employee_id NUMBER(4,0),
name VARCHAR(10),
salary NUMBER(7,2)
);
Query OK, 0 rows affected
obclient> INSERT INTO employees (employee_id,name,salary)VALUES(105,'Adam',15000);
Query OK, 1 row affected
obclient> DECLARE
V_EMP_NAME employees.name%TYPE := 'Adam';
V_EMP_SAL employees.salary%TYPE;
V_bonus number := 0;
BEGIN
SELECT salary INTO V_EMP_SAL FROM employees
WHERE name=V_EMP_NAME;
IF V_EMP_SAL < 10000 THEN
V_bonus := V_EMP_SAL * 0.4;
ELSE
V_bonus := V_EMP_SAL * 0.25;
END IF;
DBMS_OUTPUT.PUT_LINE(V_EMP_NAME||'''bonus: '||V_bonus);
END;
/
Query OK, 0 rows affected
Adam'bonus: 3750
IF THEN ELSIF statement
The syntax of the IF THEN ELSEF statement is as follows:
IF condition_1 THEN
statements_1
ELSIF condition_2 THEN
statements_2
[ ELSIF condition_3 THEN
statements_3
]...
[ ELSE
else_statements
]
END IF;
If the value of condition_1 is TRUE, the statements specified by statements_1 are executed, and the program ends. If the value of condition_2 is TRUE, the statements specified by statements_2 are executed, and the program ends. The execution goes on like this until the value of condition_n is not TRUE, and then the statements specified by else_statements are executed and the program ends.
The IF THEN statement can be nested, so the IF THEN ELSIF and IF THEN ELSE statements are equivalent.
The syntax of the IF THEN ELSIF statement is as follows:
IF condition_1 THEN statements_1;
ELSIF condition_2 THEN statements_2;
ELSIF condition_3 THEN statement_3;
END IF;
The syntax of the IF THEN ELSE statement is as follows:
IF condition_1 THEN
statements_1;
ELSE
IF condition_2 THEN
statements_2;
ELSE
IF condition_3 THEN
statements_3;
END IF;
END IF;
END IF;
Here is an example:
obclient> CREATE TABLE employees(
employee_id NUMBER(4,0),
name VARCHAR(10),
salary NUMBER(7,2)
);
Query OK, 0 rows affected
obclient> INSERT INTO employees (employee_id,name,salary)VALUES(105,'Adam',4000);
Query OK, 1 row affected
obclient>DECLARE
V_EMP_NAME employees.name%TYPE := 'Adam';
V_EMP_SAL employees.salary%TYPE;
V_bonus number := 0;
BEGIN
SELECT salary INTO V_EMP_SAL FROM employees
WHERE name=V_EMP_NAME;
IF V_EMP_SAL > 10000 THEN
V_bonus := V_EMP_SAL * 0.4;
ELSIF V_EMP_SAL > 5000 THEN
V_bonus := V_EMP_SAL * 0.25;
ELSE
V_bonus := V_EMP_SAL * 0.1;
END IF;
DBMS_OUTPUT.PUT_LINE(V_EMP_NAME||'''bonus: '||V_bonus);
END;
/
Query OK, 0 rows affected
Adam'bonus: 400
Simple CASE statement
A simple CASE statement evaluates the value of a single expression and compares the value with multiple potential values.
The syntax of a simple CASE statement is as follows:
CASE selector
WHEN selector_value_1 THEN statements_1
WHEN selector_value_2 THEN statements_2 ...
WHEN selector_value_n THEN statements_n
[ ELSE
else_statements ]
END
CASE;
In the preceding example, selector is an expression, and each selector_value can be a literal or an expression. The simple CASE statement compares the value of selector with values of selector_value one by one until it finds the first selector_value that is equal to the value of selector, and then stops the comparison. If no selector_value is equal to the value of selector, the CASE statement executes the statements specified by else_statements. In this case, if no statements are specified by else_statements, the program raises an exception.
Here is an example:
obclient> DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('PASS');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('SUSPEND');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('FAIL');
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;
END;
/
Query OK, 0 rows affected
SUSPEND
The program raises an exception in the following case:
obclient> DECLARE
grade CHAR(1);
BEGIN
grade := 'D';
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('PASS');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('SUSPEND');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('FAIL');
-- ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;
END;
/
OBE-00600: internal error code, arguments: -5571, Case not found for CASE statement
Searched CASE statement
In a searched CASE statement, CASE is not followed by any expression that needs to be evaluated, but WHEN is followed by different Boolean expressions. The searched CASE statement evaluates the values of several Boolean expressions and selects the first branch whose result is TRUE.
The syntax of a searched CASE statement is as follows:
CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2 ...
WHEN condition_n THEN statements_n
[ ELSE
else_statements ]
END CASE;
The searched CASE statement executes the first statement for which condition is TRUE. Remaining statements are not executed. If no condition is TRUE, the statements specified by else_statements are executed. In this case, if no statements are specified by else_statements, the program raises an exception.
The following example shows a searched CASE statement corresponding to a simple CASE statement:
obclient> DECLARE
grade CHAR(1);
BEGIN
grade := 'D';
CASE
WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('PASS');
WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('SUSPEND');
WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('FAIL');
ELSE DBMS_OUTPUT.PUT_LINE('UNEXPECTED');
END CASE;
END;
/
Query OK, 0 rows affected
UNEXPECTED