Conditional control statements execute or skip a block of program code based on different conditions. They can be implemented using IF or CASE.
Applicability
This content applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition only provides MySQL-compatible mode.
The IF statement has the following forms:
IF THENIF THEN ELSEIF THEN ELSIF
The CASE statement selects from a series of conditions and executes the corresponding statements. The CASE statement has the following forms:
Simple. Evaluates the value of a single expression and compares it with multiple potential values.
Searched. Evaluates multiple conditions and selects the first one that is
TRUE.
The CASE statement is suitable for applications with multiple branches.
IF THEN statement
The structure of the IF THEN statement is as follows:
IF condition THEN
statements
END IF;
If condition is TRUE, then statements is 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
When designing programs, avoid overly complex IF statements like the following:
IF new_weight < minimum_weight THEN
isHealthy := TRUE;
ELSE
isHealthy := TRUE;
END IF;
A more efficient approach is to directly assign a boolean expression: isHealthy := new_weight < minimum_weight;
Boolean variables can store TRUE, FALSE, or NULL and can be directly used for conditional judgments. Therefore, avoid the following approach:
IF isHealthy = FALSE THEN
RAISE start_one_month_diet;
END IF;
A better approach is:
IF NOT isHealth THEN
RAISE start_one_month_diet;
END IF;
IF THEN ELSE statement
The structure of the IF THEN ELSE statement is as follows:
IF condition THEN
statements
ELSE
else_statements
END IF;
If condition is TRUE, then statements is executed; otherwise, else_statements is 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 structure 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
statements_3
]...
[ ELSE
else_statements
]
END IF;
If condition_1 is TRUE, then statements_1 is executed and the program ends. If not, and condition_2 is TRUE, then statements_2 is executed and the program ends. This continues until condition_n is TRUE, at which point statements_n is executed and the program ends. If none of the conditions are TRUE, then else_statements is executed and the program ends.
Since IF THEN statements can be nested, the IF THEN ELSIF and IF THEN ELSE statements are equivalent.
The IF THEN ELSIF statement is structured as follows:
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 is structured 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
The simplest CASE statement evaluates the value of a single expression and compares it with multiple potential values.
The structure of the 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 above example, selector is an expression, and each selector_value can be either a literal value or an expression. The simple CASE statement compares selector with each selector_value in sequence. As soon as it finds a match, it stops comparing. If no match is found, the CASE statement executes else_statements. If else_statements does not exist, an error is reported.
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
Here is an example where an error is reported:
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, there is no expression to evaluate after CASE, but instead, different boolean expressions follow WHEN. The searched CASE statement evaluates multiple boolean expressions and selects the first one that is TRUE.
The structure of the 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 where condition is TRUE and does not execute any other statements. If none of the condition values are TRUE, then else_statements is executed. If else_statements does not exist, an error is reported.
Here is an example of a searched CASE statement corresponding to the 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