Conditional control statements execute or skip a set of program code based on different conditions. They can be implemented using IF or CASE.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
The IF statement can be used in the following forms:
IF THENIF THEN ELSEIF THEN ELSIF
The CASE statement selects from a series of conditions and runs the corresponding statements. The CASE statement can be used in the following forms:
Simple. It calculates the value of a single expression and compares it with multiple potential values.
Searched. It calculates 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, 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
In program design, avoid overly lengthy IF statements like the following:
IF new_weight < minimum_weight THEN
isHealthy := TRUE;
ELSE
isHealthy := TRUE;
END IF;
An optimized 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 writing style:
IF isHealthy = FALSE THEN
RAISE start_one_month_diet;
END IF;
An optimized 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, the statements are executed. Otherwise, the 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 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;
When condition_1 is TRUE, the statements_1 are executed, and the program ends. Otherwise, when condition_2 is TRUE, the statements_2 are executed, and the program ends. This process continues until condition_n is TRUE, at which point the else_statements are 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 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 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 calculates 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 preceding example, selector is an expression, and each selector_value can be a literal value or an expression. The simple CASE statement compares the value of selector with each selector_value in sequence. If a match is found, the comparison stops. If no match is found, the CASE statement executes the else_statements. If no else_statements are provided, 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 calculate after CASE, but different boolean expressions are provided after WHEN. The searched CASE statement calculates 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 conditions are TRUE, the else_statements are executed. If no else_statements are provided, 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