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.
Top comments (0)