You have learned to write PL/SQL blocks containing declarative and executable sections. You have also learned to include expressions and SQL statements in the executable block. In this post, you will learn how to use control structures such as CASE statements in a PL/SQL block.
A CASE expression returns a result based on one or more alternatives. To return the result, the CASE expression uses a selector, which is an expression whose value is used to return one of several alternatives. The selector is followed by one or more WHEN clauses that are checked sequentially. The value of the selector determines which result is returned. If the value of the selector equals the value of a WHEN clause expression, that WHEN clause is executed and that result is returned.
CASE selector WHEN expression1 THEN result1 [WHEN expression2 THEN result2 ... WHEN expressionN THEN resultN] [ELSE resultN+1] END;
PL/SQL also provides a searched CASE expression, which has the form:
CASE WHEN search_condition1 THEN result1 [WHEN search_condition2 THEN result2 ... WHEN search_conditionN THEN resultN] [ELSE resultN+1] END;
A searched CASE expression has no selector. Furthermore, the WHEN clauses in CASE expressions contain search conditions that yield a Boolean value rather than expressions that can yield a value of any type.
CASE Expressions: Example
Consider the example shown below:
SET VERIFY OFF DECLARE v_grade CHAR(1) := UPPER('&grade'); v_appraisal VARCHAR2(20); BEGIN v_appraisal := CASE v_grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' ELSE 'No such grade' END; DBMS_OUTPUT.PUT_LINE ('Grade: '|| v_grade || 'Appraisal' || v_appraisal); END; /
In the example above, the CASE expression uses the value in the v_grade variable as the expression. This value is accepted from the user by using a substitution variable. Based on the value entered by the user, the CASE expression returns the value of the v_appraisal variable based on the value of the v_grade value.
When you enter a or A for v_grade, as shown in the Substitution Variable window, the output of the example is as follows:
anonymous block completed Grade: A Appraisal Excellent
Searched CASE Expressions
Consider the example shown below:
DECLARE v_grade CHAR(1) := UPPER('&grade'); v_appraisal VARCHAR2(20); BEGIN v_appraisal := CASE WHEN v_grade = 'A' THEN 'Excellent' WHEN v_grade IN ('B','C') THEN 'Good' ELSE 'No such grade' END; DBMS_OUTPUT.PUT_LINE ('Grade: '|| v_grade || ‘ Appraisal ' || v_appraisal); END; /
In the previous example, you saw a single test expression, the v_grade variable. The WHEN clause compares a value against this test expression. In searched CASE statements, you do not have a test expression. Instead, the WHEN clause contains an expression that results in a Boolean value. The same example is rewritten above to show searched CASE statements.
The output of the example is as follows when you enter b or B for v_grade:
anonymous block completed Grade: B Appraisal Good
Conside the example shown below:
DECLARE v_deptid NUMBER; v_deptname VARCHAR2(20); v_emps NUMBER; v_mngid NUMBER:= 108; BEGIN CASE v_mngid WHEN 108 THEN SELECT department_id, department_name INTO v_deptid, v_deptname FROM departments WHERE manager_id=108; SELECT count(*) INTO v_emps FROM employees WHERE department_id=v_deptid; WHEN 200 THEN ... END CASE; DBMS_OUTPUT.PUT_LINE ('You are working in the '|| v_deptname|| ' department. There are '||v_emps ||' employees in this department'); END; /
Recall the use of the IF statement. You may include n number of PL/SQL statements in the THEN clause and also in the ELSE clause. Similarly, you can include statements in the CASE statement, which is more readable compared to multiple IF and ELSIF statements.
How a CASE Expression Differs from a CASE Statement
A CASE expression evaluates the condition and returns a value, whereas a CASE statement evaluates the condition and performs an action. A CASE statement can be a complete PL/SQL block.
– CASE statements end with END CASE;
– CASE expressions end with END;
The output of the example code above is as follows:
anonymous block completed You are working in the Finance department. There are 6 employees in this department.