The CASE expression returns a value that is substituted where the CASE expression is located within an expression.
The CASE expression is available in two formats. One is called a searched CASE and the other uses a selector.
Selector CASE expression
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 no match is found, the value following ELSE is returned. If ELSE is omitted, the CASE expression returns null.
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 within the CASE expression. result is an expression that is type-compatible in the context where the CASE expression is used. When the first match-expression that equals selector-expression is encountered, result in the corresponding THEN clause is returned as the value of the CASE expression. If none of match-expression equals selector-expression, result following ELSE is returned. If no ELSE is specified, the CASE expression returns null.
The following 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 output is generated from this program:
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
Searched CASE expression
A searched CASE expression uses one or more Boolean expressions to determine the resulting value to return.
CASE WHEN boolean-expression THEN
result
[ WHEN boolean-expression THEN
result
[ WHEN boolean-expression THEN
result ] ...]
[ ELSE
result ]
END;
boolean-expression is evaluated in the order in which it appears within the CASE expression. result is an expression that is type-compatible in the context where the CASE expression is used. When the first boolean-expression that evaluates to TRUE is encountered, result in the corresponding THEN clause is returned as the value of the CASE expression. If none of boolean-expression evaluates to TRUE, result following ELSE is returned. If no ELSE is specified, the CASE expression returns null.
The following example uses a searched 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
WHEN v_deptno = 10 THEN 'Accounting'
WHEN v_deptno = 20 THEN 'Research'
WHEN v_deptno = 30 THEN 'Sales'
WHEN v_deptno = 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 output is generated from this program:
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