Passive search CASE statement
Passive search CASE statements use one or more Boolean expressions to determine which statement to execute.
CASE WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements ] ...]
[ ELSE
statements ]
END CASE;
The boolean-expression is computed in the order it appears in the CASE statement. When the first boolean-expression that evaluates to true is encountered, then the statement in the corresponding THEN clause is executed, and the process runs after the keyword END CASE. If no boolean-expression is evaluated as true, then the statement after ELSE is executed. If there is no boolean-expression that evaluates to true and no ELSE clause, an exception is thrown.
The following example assigns department names and locations to variables based on department numbers using a passive search CASE statement.
\set PLSQL_MODE on CREATE OR REPLACE PROCEDURE emp_case_4() IS v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; v_deptno emp.deptno%TYPE; v_dname VARCHAR2(20); v_loc VARCHAR2(20); CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp ORDER BY 1; 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; / \set PLSQL_MODE off The following is the output of this program.
postgres=# select emp_case_4(); NOTICE: EMPNO ENAME DEPTNO DNAME LOC NOTICE: ----- ------- ------ ---------- --------- NOTICE: 1001 SMITH 10 Accounting New York NOTICE: 1002 ALLEN 30 Sales Chicago NOTICE: 1003 SMITH 20 Research Dallas NOTICE: 1004 JACK 20 Research Dallas NOTICE: 1005 JANE 40 Operations Boston NOTICE: 1006 MILLER 20 Research Dallas NOTICE: 1007 ADAMS 30 Sales Chicago NOTICE: 1008 JONES 10 Accounting New York NOTICE: 1009 FORD 30 Sales Chicago EMP_CASE_4 ------------ (1 row) 
Top comments (0)