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)