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;