DEV Community

AntDB
AntDB

Posted on

AntDB-Oracle Compatibility Developer's Manual P4–29

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Top comments (0)