Selector CASE statement v15
The selector CASE
statement attempts to match an expression called the selector to the expression specified in one or more WHEN
clauses. When a match is found, one or more corresponding statements are executed.
Syntax
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 type-compatible with eachmatch-expression
.match-expression
is evaluated in the order in which it appears in theCASE
statement.statements
are one or more SPL statements, each terminated by a semi-colon.- When the value of
selector-expression
equals the firstmatch-expression
, the statements in the correspondingTHEN
clause are executed, and control continues following theEND CASE
keywords. - If there are no matches, the statements following
ELSE
are executed. - If there are no matches and there is no
ELSE
clause, an exception is thrown.
Example
This 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 is the output from this program:
Output
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