PolarDB:CASE expression

Selector CASE expression

The selector form compares a single selector expression against each WHEN value in order. When the first matching WHEN value is found, the expression returns the corresponding THEN result.

CASE <selector-expression>
  WHEN <match-expression> THEN
    <result>
[ WHEN <match-expression> THEN
    <result>
[ WHEN <match-expression> THEN
    <result> ] ...]
[ ELSE
    <result> ]
END;

• <selector-expression> is evaluated once and compared to each <match-expression> in the order they appear.

• <match-expression> must be type-compatible with <selector-expression>.

• <result> must be type-compatible in the context where the CASE expression is used.

• The first <match-expression> that equals <selector-expression> determines the returned <result>.

• If no <match-expression> matches, the ELSE result is returned.

• If ELSE is omitted and no match is found, the CASE expression returns null.

The following example uses a selector CASE expression to assign a department name to the variable v_dname based on the department number v_deptno.

DECLARE
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_deptno        emp.deptno%TYPE;
    v_dname         dept.dname%TYPE;
    CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp;
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME     DEPTNO    DNAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------   ------    ----------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
        EXIT WHEN emp_cursor%NOTFOUND;
        v_dname :=
            CASE v_deptno
                WHEN 10 THEN 'Accounting'
                WHEN 20 THEN 'Research'
                WHEN 30 THEN 'Sales'
                WHEN 40 THEN 'Operations'
                ELSE 'unknown'
            END;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || RPAD(v_ename, 10) ||
            '  ' || v_deptno || '      ' || v_dname);
    END LOOP;
    CLOSE emp_cursor;
END;

Output:

EMPNO    ENAME     DEPTNO    DNAME
-----    -------   ------    ----------
7369     SMITH       20      Research
7499     ALLEN       30      Sales
7521     WARD        30      Sales
7566     JONES       20      Research
7654     MARTIN      30      Sales
7698     BLAKE       30      Sales
7782     CLARK       10      Accounting
7788     SCOTT       20      Research
7839     KING        10      Accounting
7844     TURNER      30      Sales
7876     ADAMS       20      Research
7900     JAMES       30      Sales
7902     FORD        20      Research
7934     MILLER      10      Accounting

Searched CASE expression

The searched form evaluates each WHEN condition as an independent boolean expression. It is equivalent to an IF-THEN-ELSIF chain, making it more flexible than the selector form and better suited for conditions that involve different columns or complex predicates.

CASE WHEN <boolean-expression> THEN
    <result>
[ WHEN <boolean-expression> THEN
    <result>
[ WHEN <boolean-expression> THEN
    <result> ] ...]
[ ELSE
    <result> ]
END;

• Each <boolean-expression> is evaluated in the order it appears.

• <result> must be type-compatible in the context where the CASE expression is used.

• The first <boolean-expression> that evaluates to TRUE determines the returned <result>.

• If no <boolean-expression> evaluates to TRUE, the ELSE result is returned.

• If ELSE is omitted and no condition is TRUE, the CASE expression returns null.

The following example produces the same output as the selector form by using boolean conditions instead of a single selector.

DECLARE
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_deptno        emp.deptno%TYPE;
    v_dname         dept.dname%TYPE;
    CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp;
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME     DEPTNO    DNAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------   ------    ----------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
        EXIT WHEN emp_cursor%NOTFOUND;
        v_dname :=
            CASE
                WHEN v_deptno = 10 THEN 'Accounting'
                WHEN v_deptno = 20 THEN 'Research'
                WHEN v_deptno = 30 THEN 'Sales'
                WHEN v_deptno = 40 THEN 'Operations'
                ELSE 'unknown'
            END;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || RPAD(v_ename, 10) ||
            '  ' || v_deptno || '      ' || v_dname);
    END LOOP;
    CLOSE emp_cursor;
END;

Output:

EMPNO    ENAME     DEPTNO    DNAME
-----    -------   ------    ----------
7369     SMITH       20      Research
7499     ALLEN       30      Sales
7521     WARD        30      Sales
7566     JONES       20      Research
7654     MARTIN      30      Sales
7698     BLAKE       30      Sales
7782     CLARK       10      Accounting
7788     SCOTT       20      Research
7839     KING        10      Accounting
7844     TURNER      30      Sales
7876     ADAMS       20      Research
7900     JAMES       30      Sales
7902     FORD        20      Research
7934     MILLER      10      Accounting

Usage notes

• NULL handling: In the selector form, a null <selector-expression> never matches any <match-expression>, even a null one. Use the searched form with WHEN <column> IS NULL THEN ... to handle null values explicitly.

• Short-circuit evaluation: Matching stops at the first true condition; subsequent conditions are not evaluated.

• Choosing between the two forms: Use the selector form when all conditions compare the same expression to different constant values. Use the searched form for conditions involving different columns, ranges, or complex predicates.