Searched CASE statement v17
A searched CASE
statement uses one or more Boolean expressions to determine the resulting set of statements to execute.
Syntax
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 in theCASE
statement.- When the first
boolean-expression
is encountered that evaluates toTRUE
, the statements in the correspondingTHEN
clause are executed and control continues following theEND CASE
keywords. - If none of
boolean-expression
evaluates toTRUE
, the statements followingELSE
are executed. - If none of
boolean-expression
evaluates toTRUE
and there is noELSE
clause, an exception is thrown.
Example
This 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 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