PL/SQL Decision Making
Decision making in PL/SQL can be done using control structures such as ‘IF’ and ‘CASE-WHEN’ statements. They both allow for a decision to be made based on certain criteria and actions to be carried out depending on the criteria.
‘IF’ Statements
In its most basic form an ‘IF’ statement executes a group of statements if an expression evaluates to true. Its basic syntax is as follows.
IF expression THEN -- Statement(s) will execute if the expression is true. END IF;
The following example checks whether the values of two variables are the same and displays a message if they are.
DECLARE a NUMBER(2) := 10; b NUMBER(2) := 10; BEGIN IF a = b THEN DBMS_OUTPUT.PUT_LINE('a is equal to b.'); END IF; END;
This can be extended to execute a statement or statements if the expression is false as follows.
IF expression THEN -- Statement(s) will execute if the expression is true. ELSE -- Statement(s) will execute if the expression is false. END IF;
The ‘IF’ statement can be further extended with the use of ‘ELSIF’. Any number of ‘ELSIF’ statements can be used to extend the decision making process.
IF expression THEN -- Statement(s) will execute if the expression is true. ELSIF expression THEN /* Statement(s) will execute if the first expression is false and the second expression is true. */ ELSE -- Statement(s) will execute if both the expressions are false. END IF;
‘IF’ statements can also be nested one inside another.
IF expression THEN -- Statement(s) will execute if the expression is true. IF expression THEN -- Statement(s) will execute if the expression is true. END IF; END IF;
Below are examples of ‘IF’ statements using some of the operators discussed in the previous section on Operators.
DECLARE -- Variables to use in expressions. v1 NUMBER(2) := 10; v2 NUMBER(2) := 20; v3 BOOLEAN := FALSE; v4 BOOLEAN := TRUE; BEGIN IF v1 <= v2 THEN -- Less than or equal to comparison operator. DBMS_OUTPUT.PUT_LINE ('v1 is less than or equal to v2.'); ELSE DBMS_OUTPUT.PUT_LINE ('v1 is greater than v2.'); END IF; IF v1 <> v2 THEN -- Not equal to comparison operator. DBMS_OUTPUT.PUT_LINE ('v1 is not equal to v2.'); ELSE DBMS_OUTPUT.PUT_LINE ('v1 is equal to v2.'); END IF; IF v3 AND v4 THEN -- Logical AND operator. DBMS_OUTPUT.PUT_LINE ('Both v3 and v4 are True.'); ELSE DBMS_OUTPUT.PUT_LINE ('Either v3 and/or v4 are False.'); END IF; IF v1 BETWEEN 5 AND 15 THEN -- Between comparison operator. -- Output to the screen using the string concatenation operator. DBMS_OUTPUT.PUT_LINE ('v1 equals ' || v1 || ', which is between 5 and 15.'); ELSE DBMS_OUTPUT.PUT_LINE ('v1 is not between 5 and 15.'); END IF; END;
The resulting output from the above will look as follows.
v1 is less than or equal to v2. v1 is not equal to v2. Either v3 and/or v4 are False. v1 equals 10, which is between 5 and 15.
‘CASE-WHEN’ Statements
Where there are more than two options in a decision, a ‘CASE-WHEN’ statement can be more efficient than using multiple ‘ELSIF’ statements.
CASE expression WHEN expression-result-option1 THEN -- Statement(s) to execute. WHEN expression-result-option2 THEN -- Statement(s) to execute. WHEN expression-result-option3 THEN -- Statement(s) to execute. ELSE -- Statement(s) to execute. END CASE;
The ‘ELSE’ at the end is a catch all where the expression does not equal any of the options.
A simple example of a ‘CASE-WHEN’ statement would be to display a message on screen depending on the value of a variable. In this case, the message ‘a equals 3’ would be displayed on screen.
DECLARE a NUMBER(1) := 3; BEGIN CASE a WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('a equals 1.'); WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('a equals 2.'); WHEN 3 THEN DBMS_OUTPUT.PUT_LINE('a equals 3.'); ELSE DBMS_OUTPUT.PUT_LINE('a is not equal to 1, 2 or 3.'); END CASE; END;