The simple CASE statement attempts to match an expression (known as the selector) to another expression that is specified in one or more WHEN clauses. A match results in the execution of one or more corresponding statements.
>>-CASE--selector-expression------------------------------------> .----------------------------------------------. | .------------. | V V | | >----WHEN--match-expression--THEN----statements-+-+-------------> >--+----------------------+--END CASE-------------------------->< | .------------. | | V | | '-ELSE----statements-+-'
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_deptno emp.deptno%TYPE;
v_dname dept.dname%TYPE;
v_loc dept.loc%TYPE;
CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp;
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;
EMPNO ENAME DEPTNO DNAME LOC
----- ------- ------ ---------- ---------
7369 SMITH 20 Research Dallas
7499 ALLEN 30 Sales Chicago
7521 WARD 30 Sales Chicago
7566 JONES 20 Research Dallas
7654 MARTIN 30 Sales Chicago
7698 BLAKE 30 Sales Chicago
7782 CLARK 10 Accounting New York
7788 SCOTT 20 Research Dallas
7839 KING 10 Accounting New York
7844 TURNER 30 Sales Chicago
7876 ADAMS 20 Research Dallas
7900 JAMES 30 Sales Chicago
7902 FORD 20 Research Dallas
7934 MILLER 10 Accounting New York