DEV Community

AntDB
AntDB

Posted on

AntDB-Oracle Compatibility Developer's Manual P4–28

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

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

Top comments (0)