AntDB-Oracle Compatibility Developer’s Manual P4–27
Published: 2023-09-28 08:21:54

#### CASE expression

The CASE expression returns a numeric value that is used in place of the CASE expression in an expression.

CASE expressions come in two formats. One is called a passive search CASE expression, and the other is an active selection CASE expression.

Prepare test data.

```
CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,10);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (1002,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (1003,'SMITH','CLERK',7902,'17-DEC-80',800,20);
INSERT INTO emp VALUES (1004,'JACK','CLERK',7922,'18-DEC-80',800,null,20);
INSERT INTO emp VALUES (1005,'JANE','CLERK',7912,'19-DEC-80',800,null,40);
INSERT INTO emp VALUES (1006,'MILLER','CLERK',7912,'19-DEC-80',800,null,20);
INSERT INTO emp VALUES (1007,'ADAMS','CLERK',7912,'19-DEC-80',800,452,30);
INSERT INTO emp VALUES (1008,'JONES','CLERK',7912,'19-DEC-80',800,2500,10);
INSERT INTO emp VALUES (1009,'FORD','CLERK',7912,'19-DEC-80',800,1500,30);
```

##### Active-selective CASE expressions

Active-select CASE expressions use an expression called a selector to match one or more expressions specified in the WHEN clause. The result is an expression that is compatible with the CASE expression type. If there is a match, then the CASE expression returns the value in the corresponding THEN clause. If there is no match here, then the value following the ELSE clause is returned. If the ELSE clause is omitted, then the CASE expression returns the null value.

CASE selector-expression

WHEN match-expression THEN

result

\[ WHEN match-expression THEN

result

\[ WHEN match-expression THEN

result ] ...]

\[ ELSE

result ]

END;

match-expression is computed based on the order in which it appears in CASE expressions. The result is an expression compatible with the CASE expression type. When the first match-expression equal to the selector-expression is encountered, the result corresponding to the THEN clause is returned as the value of the CASE expression. If no match-expression is equal to selector-expression, then the result of the argument following ELSE is returned. If ELSE is not specified, then the CASE expression returns the null value.

The following example uses an active-selector CASE expression to assign a department name to a variable based on the department number.

```
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_1()
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_deptno emp.deptno%TYPE;
	v_dname 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');
	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;
/
\set PLSQL_MODE off
```

The following is the output of the program:

```
postgres=# select emp_case_1();
NOTICE:  EMPNO ENAME DEPTNO DNAME
NOTICE:  ----- ------- ------ ----------
NOTICE:  1001 SMITH      10 Accounting
NOTICE:  1002 ALLEN      30 Sales
NOTICE:  1003 SMITH      20 Research
NOTICE:  1004 JACK       20 Research
NOTICE:  1005 JANE       40 Operations
NOTICE:  1006 MILLER     20 Research
NOTICE:  1007 ADAMS      30 Sales
NOTICE:  1008 JONES      10 Accounting
NOTICE:  1009 FORD       30 Sales
 EMP_CASE_1
------------

(1 row)
```

##### Passive search CASE expression

A passive-search CASE expression is one or more Boolean expressions to confirm the result value and then return it.

CASE WHEN boolean-expression THEN

result

\[ WHEN boolean-expression THEN

result

\[ WHEN boolean-expression THEN

result ] ...]

\[ ELSE

result ]

END;

The boolean-expression is computed according to the order in which it appears in the CASE expression. The result is the type-compatible expression in the CASE expression. When the first boolean-expression that evaluates to true is encountered, then result is returned as the value of the CASE expression in the corresponding THEN clause. If no true boolean-expression is encountered, then the value after ELSE is returned. If the ELSE clause is not specified, then the CASE expression returns null.

In the following example, a passive search CASE expression is used to assign a department name to a variable based on the department number.

```
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_2()
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_deptno emp.deptno%TYPE;
	v_dname 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');
	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;
/
\set PLSQL_MODE off
```

The following is the output of the program:

```
postgres=# select emp_case_2();
NOTICE:  EMPNO ENAME DEPTNO DNAME
NOTICE:  ----- ------- ------ ----------
NOTICE:  1001 SMITH      10 Accounting
NOTICE:  1002 ALLEN      30 Sales
NOTICE:  1003 SMITH      20 Research
NOTICE:  1004 JACK       20 Research
NOTICE:  1005 JANE       40 Operations
NOTICE:  1006 MILLER     20 Research
NOTICE:  1007 ADAMS      30 Sales
NOTICE:  1008 JONES      10 Accounting
NOTICE:  1009 FORD       30 Sales
 EMP_CASE_2
------------

(1 row)
```

#### CASE statement

The CASE statement executes one or more sets of statements when the specified search condition is true. The CASE statement is a separate statement by itself, and the CASE expressions discussed earlier appear in the CASE statement as part of the overall expression.

CASE statements come in two formats. One is called the passive search CASE statement and the other is called the active select CASE statement.