Selector CASE expression v15
The selector CASE
expression attempts to match an expression, called the selector, to the expression specified in one or more WHEN
clauses. result
is an expression that is type-compatible in the context where the CASE
expression is used. If a match is found, the value given in the corresponding THEN
clause is returned by the CASE
expression. If there are no matches, the value following ELSE
is returned. If ELSE
is omitted, the CASE
expression returns null.
Syntax
CASE <selector-expression> WHEN <match-expression> THEN <result> [ WHEN <match-expression> THEN <result> [ WHEN <match-expression> THEN <result> ] ...] [ ELSE <result> ] END;
match-expression
is evaluated in the order in which it appears in theCASE
expression.result
is an expression that is type-compatible in the context where theCASE
expression is used.- When the first
match-expression
is encountered that equalsselector-expression
,result
in the correspondingTHEN
clause is returned as the value of theCASE
expression. - If none of
match-expression
equalsselector-expression
, thenresult
followingELSE
is returned. - If no
ELSE
is specified, theCASE
expression returns null.
Example
This example uses a selector CASE
expression to assign the department name 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; CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp; BEGIN OPEN emp_cursor; DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME'); DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------'); LOOP FETCH emp_cursor INTO v_empno, v_ename, v_deptno; EXIT WHEN emp_cursor%NOTFOUND; v_dname := CASE v_deptno WHEN 10 THEN 'Accounting' WHEN 20 THEN 'Research' WHEN 30 THEN 'Sales' WHEN 40 THEN 'Operations' ELSE 'unknown' END; DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) || ' ' || v_deptno || ' ' || v_dname); END LOOP; CLOSE emp_cursor; END;
The following is the output from this program:
Output
EMPNO ENAME DEPTNO DNAME ----- ------- ------ ---------- 7369 SMITH 20 Research 7499 ALLEN 30 Sales 7521 WARD 30 Sales 7566 JONES 20 Research 7654 MARTIN 30 Sales 7698 BLAKE 30 Sales 7782 CLARK 10 Accounting 7788 SCOTT 20 Research 7839 KING 10 Accounting 7844 TURNER 30 Sales 7876 ADAMS 20 Research 7900 JAMES 30 Sales 7902 FORD 20 Research 7934 MILLER 10 Accounting