Active-select CASE statement
The active selective CASE statement tries to match the expression specified in the WHEN clause. When a match is found, the corresponding statement is executed.
CASE selector-expression
WHEN match-expression THEN
statements
[ WHEN match-expression THEN
statements
[ WHEN match-expression THEN
statements ] ...]
[ ELSE
statements ]
END CASE;
selector-expression returns a value that is compatible with each match-expression data type. The match-expression is evaluated in the order it appears in the CASE statement. statements are SPL statements, each statement is terminated by a semicolon. When the first match-expression is encountered that is equal to the value of the parameter selector-expression, the statement in the corresponding THEN clause is executed, and the process then runs after the END CASE keyword. If there is no match here, then the statement after ELSE is executed. If no matching expression is found and there is no ELSE clause, then an exception is thrown.
The following example assigns a department name and geographic location to a variable based on a department number using the active select CASE statement:
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_3()
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 v_deptno
WHEN 10 THEN v_dname := 'Accounting';
v_loc := 'New York';
WHEN 20 THEN v_dname := 'Research';
v_loc := 'Dallas';
WHEN 30 THEN v_dname := 'Sales';
v_loc := 'Chicago';
WHEN 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_3();
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_3
------------
(1 row)
Top comments (0)