The CASE statement executes a set of one or more statements when a specified search condition is TRUE. The CASE statement is a standalone statement in itself while the previously discussed CASE expression must appear as part of an expression.
The CASE statement is available in two formats. One is called a searched CASE and the other uses a selector.
Selector CASE statement
The selector CASE statement attempts to match an expression called selector to the expression specified in one or more WHEN clauses. When a match is found, one or more corresponding statements are executed.
CASE selector-expression
WHEN match-expression THEN
statements
[ WHEN match-expression THEN
statements
[ WHEN match-expression THEN
statements ] ...]
[ ELSE
statements ]
END CASE;
selector-expression returns a value that is type-compatible with each match-expression. match-expression is evaluated in the order in which it appears within the CASE statement. statements indicates one or more SPL statements, each of which is terminated by a semicolon. When the value of selector-expression equals the first match-expression, the statements in the corresponding THEN clause are executed and control continues following the END CASE keywords. If no match is found, the statements following ELSE are executed. If no match is found and no ELSE clause exists, an exception is thrown.
The following example uses a selector CASE statement to assign a department name and location to a variable based on the department number:
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_deptno emp.deptno%TYPE;
v_dname dept.dname%TYPE;
v_loc dept.loc%TYPE;
CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp;
BEGIN
OPEN emp_cursor;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME '
|| ' LOC');
DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------'
|| ' ---------');
LOOP
FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
EXIT WHEN emp_cursor%NOTFOUND;
CASE v_deptno
WHEN 10 THEN v_dname := 'Accounting';
v_loc := 'New York';
WHEN 20 THEN v_dname := 'Research';
v_loc := 'Dallas';
WHEN 30 THEN v_dname := 'Sales';
v_loc := 'Chicago';
WHEN 40 THEN v_dname := 'Operations';
v_loc := 'Boston';
ELSE v_dname := 'unknown';
v_loc := '';
END CASE;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||
' ' || v_deptno || ' ' || RPAD(v_dname, 14) || ' ' ||
v_loc);
END LOOP;
CLOSE emp_cursor;
END;
The following output is generated from this program:
EMPNO ENAME DEPTNO DNAME LOC
----- ------- ------ ---------- ---------
7369 SMITH 20 Research Dallas
7499 ALLEN 30 Sales Chicago
7521 WARD 30 Sales Chicago
7566 JONES 20 Research Dallas
7654 MARTIN 30 Sales Chicago
7698 BLAKE 30 Sales Chicago
7782 CLARK 10 Accounting New York
7788 SCOTT 20 Research Dallas
7839 KING 10 Accounting New York
7844 TURNER 30 Sales Chicago
7876 ADAMS 20 Research Dallas
7900 JAMES 30 Sales Chicago
7902 FORD 20 Research Dallas
7934 MILLER 10 Accounting New York
Searched CASE statement
A searched CASE statement uses one or more Boolean expressions to determine the resulting set of statements to execute.
CASE WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements ] ...]
[ ELSE
statements ]
END CASE;
boolean-expression is evaluated in the order in which it appears within the CASE statement. When the first boolean-expression that evaluates to TRUE is encountered, the statements in the corresponding THEN clause are executed and control continues following the END CASE keywords. If none of boolean-expression evaluates to TRUE, the statements following ELSE are executed. If none of boolean-expression evaluates to TRUE and no ELSE clause exists, an exception is thrown.
The following example uses a searched CASE statement to assign a department name and location to a variable based on the department number:
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_deptno emp.deptno%TYPE;
v_dname dept.dname%TYPE;
v_loc dept.loc%TYPE;
CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp;
BEGIN
OPEN emp_cursor;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME '
|| ' LOC');
DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------'
|| ' ---------');
LOOP
FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
EXIT WHEN emp_cursor%NOTFOUND;
CASE
WHEN v_deptno = 10 THEN v_dname := 'Accounting';
v_loc := 'New York';
WHEN v_deptno = 20 THEN v_dname := 'Research';
v_loc := 'Dallas';
WHEN v_deptno = 30 THEN v_dname := 'Sales';
v_loc := 'Chicago';
WHEN v_deptno = 40 THEN v_dname := 'Operations';
v_loc := 'Boston';
ELSE v_dname := 'unknown';
v_loc := '';
END CASE;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||
' ' || v_deptno || ' ' || RPAD(v_dname, 14) || ' ' ||
v_loc);
END LOOP;
CLOSE emp_cursor;
END;
The following output is generated from this program:
EMPNO ENAME DEPTNO DNAME LOC
----- ------- ------ ---------- ---------
7369 SMITH 20 Research Dallas
7499 ALLEN 30 Sales Chicago
7521 WARD 30 Sales Chicago
7566 JONES 20 Research Dallas
7654 MARTIN 30 Sales Chicago
7698 BLAKE 30 Sales Chicago
7782 CLARK 10 Accounting New York
7788 SCOTT 20 Research Dallas
7839 KING 10 Accounting New York
7844 TURNER 30 Sales Chicago
7876 ADAMS 20 Research Dallas
7900 JAMES 30 Sales Chicago
7902 FORD 20 Research Dallas
7934 MILLER 10 Accounting New York