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 CASE statement evaluates several conditions and select the first condition that is
TURE.
The CASE statement is suitable for branched scenarios.
IF THEN statement
The IF THEN statement has the following syntax:
IF condition THEN
statements
END IF;
If the value of condition is TRUE, the specified 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 IF THEN ELSE statement has the following syntax:
IF condition THEN
statements
ELSE
else_statements
END IF;
If the value of condition is TRUE, the statements are executed. Otherwise, the statements specified for 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 IF THEN ELSIF statement has the following syntax:
IF condition_1 THEN
statements_1
ELSIF condition_2 THEN
statements_2
[ ELSIF condition_3 THEN
statements_3
]...
[ ELSE
else_statements
]
END IF;
When the value of condition_1 is TRUE, the database executes the statement specified for statements_1 and ends the program. When the value of condition_2 is TRUE, the database executes the statement specified for statements_2 and ends the program. The database repeats this process until the value of condition_n is not TRUE, and executes the statement specified for else_statements and ends the program.
The IF THEN statement can be nested, so the IF THEN ELSIF and IF THEN ELSE statements are equivalent.
The IF THEN ELSIF statement has the following syntax:
IF condition_1 THEN statements_1;
ELSIF condition_2 THEN statements_2;
ELSIF condition_3 THEN statement_3;
END IF;
The IF THEN ELSE statement has the following syntax:
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
The simplest CASE statement evaluates the value of a single expression and compares the value with multiple potential values.
The simple CASE statement has the following syntax:
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 for else_statements. In this case, if no statements have been specified for 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;
/
ORA-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 searched CASE statement has the following syntax:
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 for else_statements are executed. In this case, if no statements have been specified for 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