• Simple CASE statement evaluates a expression and executes the statement or sequence of statements associated with the first condition that evaluates to TRUE.
• Searched CASE statement evaluates multiple Boolean expressions and executes the statement or the sequence of statements associated with the first condition that evaluates to TRUE.
• ELSE is optional.If not used,make sure one of the WHEN matches,if not then exception CASE_NOT_FOUND is raised.
• WHEN clauses are evaluated in order, from top to bottom.
• Execution ends once a sequence of statements has been executed. If more than one expression evaluates to TRUE, only the statements associated with the first such expression are executed.
SQL> DECLARE
2 emprec1 emp%ROWTYPE;
3 emprec2 emp%ROWTYPE;
4 emprec3 emp%ROWTYPE;
5 emprec4 emp%ROWTYPE;
6 emprec5 emp%ROWTYPE;
7 emprec6 emp%ROWTYPE;
8 BEGIN
9 BEGIN
10 SELECT *
11 INTO emprec1
12 FROM emp
13 WHERE empno = 7788;
14
15 SELECT *
16 INTO emprec2
17 FROM emp
18 WHERE empno = 7788;
19
20 SELECT *
21 INTO emprec3
22 FROM emp
23 WHERE empno = 7788;
24
25 SELECT *
26 INTO emprec4
27 FROM emp
28 WHERE empno = 7788;
29
30 SELECT *
31 INTO emprec5
32 FROM emp
33 WHERE empno = 7788;
34
35 SELECT *
36 INTO emprec6
37 FROM emp
38 WHERE empno = 7788;
39
40 DBMS_OUTPUT.PUT_LINE('SIMPLE CASE STATEMENT - 1');
41
42 CASE emprec1.ename
43 WHEN 'SMITH' THEN
44 DBMS_OUTPUT.PUT_LINE('Employee1');
45 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
46 WHEN 'SCOTT' THEN
47 DBMS_OUTPUT.PUT_LINE('Employee2');
48 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
49 WHEN 'SCOTT' THEN
50 DBMS_OUTPUT.PUT_LINE('Employee3');
51 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
52 ELSE
53 DBMS_OUTPUT.PUT_LINE('Default Employee');
54 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
55 END CASE;
56
57 DBMS_OUTPUT.PUT_LINE('SIMPLE CASE STATEMENT - 2');
58
59 CASE TRUE
60 WHEN emprec3.ename = 'SMITH' THEN
61 DBMS_OUTPUT.PUT_LINE('Employee1');
62 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
63 WHEN emprec3.ename = 'KING' THEN
64 DBMS_OUTPUT.PUT_LINE('Employee2');
65 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
66 ELSE
67 DBMS_OUTPUT.PUT_LINE('Default Employee');
68 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
69 END CASE;
70
71 DBMS_OUTPUT.PUT_LINE('SIMPLE CASE STATEMENT - 3');
72
73 CASE
74 WHEN emprec2.ename = 'SMITH' THEN
75 DBMS_OUTPUT.PUT_LINE('Employee1');
76 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
77 WHEN emprec2.ename = 'KING' THEN
78 DBMS_OUTPUT.PUT_LINE('Employee2');
79 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
80 END CASE;
81 EXCEPTION
82 WHEN CASE_NOT_FOUND THEN
83 DBMS_OUTPUT.PUT_LINE('Simple CASE Statement Exception ::: '||emprec2.ename||' - '||SQLCODE||' - '||SQLERRM);
84 END;
85
86 BEGIN
87 DBMS_OUTPUT.PUT_LINE('SEARCHED CASE STATEMENT - 1');
88
89 CASE WHEN emprec4.ename = 'SCOTT' AND emprec4.job = 'ANALYST' THEN
90 DBMS_OUTPUT.PUT_LINE('Employee1');
91 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
92 WHEN emprec4.ename = 'SCOTT' AND emprec4.job = 'ANALYST' THEN
93 DBMS_OUTPUT.PUT_LINE('Employee2');
94 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
95 WHEN emprec4.ename = 'ALLEN' AND emprec4.job = 'CLERK' THEN
96 DBMS_OUTPUT.PUT_LINE('Employee3');
97 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
98 ELSE
99 DBMS_OUTPUT.PUT_LINE('Default Employee');
100 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
101 END CASE;
102
103 DBMS_OUTPUT.PUT_LINE('SEARCHED CASE STATEMENT - 2');
104
105 CASE
106 WHEN emprec6.ename = 'KING' AND emprec6.job = 'PRESIDENT' THEN
107 DBMS_OUTPUT.PUT_LINE('Employee1');
108 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
109 WHEN emprec6.ename = 'BLAKE' AND emprec6.job = 'MANAGER' THEN
110 DBMS_OUTPUT.PUT_LINE('Employee2');
111 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
112 ELSE
113 DBMS_OUTPUT.PUT_LINE('Default Employee');
114 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
115 END CASE;
116
117 DBMS_OUTPUT.PUT_LINE('SEARCHED CASE STATEMENT - 3');
118
119 CASE TRUE
120 WHEN emprec5.ename = 'KING' AND emprec5.job = 'PRESIDENT' THEN
121 DBMS_OUTPUT.PUT_LINE('Employee1');
122 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
123 WHEN emprec5.ename = 'BLAKE' AND emprec5.job = 'MANAGER' THEN
124 DBMS_OUTPUT.PUT_LINE('Employee2');
125 DBMS_OUTPUT.PUT_LINE('Multiple Statement Execution');
126 END CASE;
127 EXCEPTION
128 WHEN CASE_NOT_FOUND THEN
129 DBMS_OUTPUT.PUT_LINE('Searched CASE Statement Exception ::: '||emprec5.ename||' - '||SQLCODE||' - '||SQLERRM);
130 END;
131 END;
132 /
--OUTPUT :::
SIMPLE CASE STATEMENT - 1
Employee2
Multiple Statement Execution
SIMPLE CASE STATEMENT - 2
Default Employee
Multiple Statement Execution
SIMPLE CASE STATEMENT - 3
Simple CASE Statement Exception ::: SCOTT - -6592 - ORA-06592: CASE not found while executing CASE statement
SEARCHED CASE STATEMENT - 1
Employee1
Multiple Statement Execution
SEARCHED CASE STATEMENT - 2
Default Employee
Multiple Statement Execution
SEARCHED CASE STATEMENT - 3
Searched CASE Statement Exception ::: SCOTT - -6592 - ORA-06592: CASE not found while executing CASE statement• Simple CASE expression evaluates a expression and returns a SINGLE value associated with the first condition that evaluates to TRUE.
• Searched CASE expression evaluates multiple Boolean expressions and returns a SINGLE value associated with the first condition that evaluates to TRUE.
• ELSE is optional.CASE_NOT_FOUND exception will NOT be raised if no match is found,instead NULL will be returned.
• WHEN clauses are evaluated in order, from top to bottom.
• Execution ends once a value has been returned. If more than one expression/condition evaluates to TRUE, only the 1st SINGLE value associated with the first such expression/condition will be returned.
SQL> DECLARE
2 emprec1 emp%ROWTYPE;
3 emprec2 emp%ROWTYPE;
4 emprec3 emp%ROWTYPE;
5 emprec4 emp%ROWTYPE;
6 emprec5 emp%ROWTYPE;
7 emprec6 emp%ROWTYPE;
8 var1 VARCHAR2(100 byte);
9 var2 VARCHAR2(100 byte);
10 var3 VARCHAR2(100 byte);
11 var4 VARCHAR2(100 byte);
12 var5 VARCHAR2(100 byte);
13 var6 VARCHAR2(100 byte);
14 BEGIN
15 BEGIN
16 SELECT *
17 INTO emprec1
18 FROM emp
19 WHERE empno = 7788;
20
21 SELECT *
22 INTO emprec2
23 FROM emp
24 WHERE empno = 7788;
25
26 SELECT *
27 INTO emprec3
28 FROM emp
29 WHERE empno = 7788;
30
31 SELECT *
32 INTO emprec4
33 FROM emp
34 WHERE empno = 7788;
35
36 SELECT *
37 INTO emprec5
38 FROM emp
39 WHERE empno = 7788;
40
41 SELECT *
42 INTO emprec6
43 FROM emp
44 WHERE empno = 7788;
45
46 DBMS_OUTPUT.PUT_LINE('SIMPLE CASE EXPRESSION - 1');
47
48 var1 := CASE emprec1.ename
49 WHEN 'KING' THEN 'KING'
50 WHEN 'SCOTT' THEN 'SCOTT1'
51 WHEN 'SCOTT' THEN 'SCOTT2'
52 ELSE 'DEFAULT'
53 END;
54
55 DBMS_OUTPUT.PUT_LINE('var1 : '||var1);
56
57 DBMS_OUTPUT.PUT_LINE('SIMPLE CASE EXPRESSION - 2');
58
59 var2 := CASE
60 WHEN emprec3.ename = 'KING' THEN 'KING'
61 WHEN emprec3.ename = 'BLAKE' THEN 'BLAKE'
62 ELSE 'DEFAULT'
63 END;
64
65 DBMS_OUTPUT.PUT_LINE('var2 : '||var2);
66
67 DBMS_OUTPUT.PUT_LINE('SIMPLE CASE EXPRESSION - 3');
68
69 var3 := CASE TRUE
70 WHEN emprec2.ename = 'BLAKE' THEN 'BLAKE'
71 WHEN emprec2.ename = 'ALLEN' THEN 'ALLEN'
72 END;
73
74 DBMS_OUTPUT.PUT_LINE('var3 : '||var3);
75 EXCEPTION
76 WHEN CASE_NOT_FOUND THEN
77 DBMS_OUTPUT.PUT_LINE('Simple CASE Expression Exception ::: '||emprec2.ename||' - '||SQLCODE||' - '||SQLERRM);
78 END;
79
80 BEGIN
81 DBMS_OUTPUT.PUT_LINE('SEARCHED CASE EXPRESSION - 1');
82
83 var4 := CASE TRUE
84 WHEN emprec4.ename = 'SCOTT' AND emprec4.job = 'ANALYST' THEN 'SCOTT/ANALYST'
85 WHEN emprec4.ename = 'ALLEN' AND emprec4.job = 'CLERK' THEN 'ALLEN/CLERK'
86 ELSE 'DEFAULT'
87 END;
88
89 DBMS_OUTPUT.PUT_LINE('var4 : '||var4);
90
91 DBMS_OUTPUT.PUT_LINE('SEARCHED CASE EXPRESSION - 2');
92 var5 := CASE
93 WHEN emprec6.ename = 'KING' AND emprec6.job = 'PRESIDENT' THEN 'KING/PRESIDENT'
94 WHEN emprec6.ename = 'ALLEN' AND emprec6.job = 'CLERK' THEN 'ALLEN/CLERK'
95 ELSE 'DEFAULT'
96 END;
97
98 DBMS_OUTPUT.PUT_LINE('var5 : '||var5);
99
100 DBMS_OUTPUT.PUT_LINE('SEARCHED CASE EXPRESSION - 3');
101
102 var6 := CASE
103 WHEN emprec5.ename = 'KING' AND emprec5.job = 'PRESIDENT' THEN 'KING/PRESIDENT'
104 WHEN emprec5.ename = 'ALLEN' AND emprec5.job = 'CLERK' THEN 'ALLEN/CLERK'
105 END;
106
107 DBMS_OUTPUT.PUT_LINE('var6 : '||var6);
108 EXCEPTION
109 WHEN CASE_NOT_FOUND THEN
110 DBMS_OUTPUT.PUT_LINE('Searched CASE Expression Exception ::: '||emprec5.ename||' - '||SQLCODE||' - '||SQLERRM);
111 END;
112 END;
113 /
--OUTPUT :::
SIMPLE CASE EXPRESSION - 1
var1 : SCOTT1
SIMPLE CASE EXPRESSION - 2
var2 : DEFAULT
SIMPLE CASE EXPRESSION - 3
var3 :
SEARCHED CASE EXPRESSION - 1
var4 : SCOTT/ANALYST
SEARCHED CASE EXPRESSION - 2
var5 : DEFAULT
SEARCHED CASE EXPRESSION - 3
var6 : CASE EXPRESSION used in RETURN clause inside function body.
CASE EXPRESSION used in DBMS_OUTPUT.PUT_LINE
CASE EXPRESSION used in procedure call.
CASE EXPRESSION used in variable assignment.
SQL> DECLARE
2 boolean_true BOOLEAN := TRUE;
3 boolean_false BOOLEAN := FALSE;
4 boolean_null BOOLEAN;
5 salary1 NUMBER := 10000;
6 salary2 NUMBER := 20000;
7 employee_id NUMBER := 100;
8 bonus_amount NUMBER;
9 emprec emp%ROWTYPE;
10 emprec1 emp%ROWTYPE;
11 emprec2 emp%ROWTYPE;
12
13 FUNCTION Boolean_to_varchar2 (flag IN BOOLEAN)
14 RETURN VARCHAR2
15 IS
16 BEGIN
17 --Simple CASE Expression
18 RETURN CASE flag
19 WHEN TRUE THEN 'True'
20 WHEN FALSE THEN 'False'
21 ELSE 'NULL'
22 END;
23 END;
24
25 PROCEDURE Give_bonus (emp_id IN NUMBER,
26 bonus_amt IN NUMBER)
27 IS
28 BEGIN
29 DBMS_OUTPUT.PUT_LINE('Inside Give_bonus Procedure :::');
30 DBMS_OUTPUT.PUT_LINE(emp_id);
31 DBMS_OUTPUT.PUT_LINE(bonus_amt);
32 END;
33
34 BEGIN
35 SELECT *
36 INTO emprec1
37 FROM EMP
38 WHERE EMPNO = 7788;
39
40 SELECT *
41 INTO emprec2
42 FROM EMP
43 WHERE EMPNO = 7788;
44
45 --Simple CASE Expression
46 DBMS_OUTPUT.PUT_LINE('Result 1 : '
47 || CASE emprec1.ename
48 WHEN 'KING' THEN 'KING'
49 WHEN 'SCOTT' THEN 'SCOTT'
50 ELSE 'DEFAULT'
51 END);
52
53 --Simple CASE Expression
54 DBMS_OUTPUT.PUT_LINE('Result 2 : '
55 || CASE
56 WHEN emprec1.ename = 'KING' THEN 'KING'
57 WHEN emprec1.ename = 'SCOTT' THEN 'SCOTT'
58 ELSE 'DEFAULT'
59 END);
60
61 DBMS_OUTPUT.PUT_LINE(Boolean_to_varchar2(boolean_true));
62 DBMS_OUTPUT.PUT_LINE(Boolean_to_varchar2(boolean_false));
63 DBMS_OUTPUT.PUT_LINE(Boolean_to_varchar2(boolean_null));
64
65 --Searched CASE Expression
66 Give_bonus(employee_id, CASE
67 WHEN salary1 >= 10000 AND salary1 <= 20000 THEN 1500
68 WHEN salary1 > 20000 AND salary1 <= 40000 THEN 1000
69 WHEN salary1 > 40000 THEN 500
70 ELSE 0
71 END);
72
73 --Searched CASE Expression
74 bonus_amount := CASE
75 WHEN salary2 >= 10000 AND salary2 <= 20000 THEN 1500
76 WHEN salary2 > 20000 AND salary2 <= 40000 THEN 1000
77 WHEN salary2 > 40000 THEN 500
78 ELSE 0
79 END * 10;
80
81 DBMS_OUTPUT.PUT_LINE('Bonus Amount : '||bonus_amount);
82
83 SELECT *
84 INTO emprec
85 FROM emp
86 WHERE empno = 7369;
87
88 --Nested CASE Statement
89 CASE
90 WHEN emprec.ename = 'SMITH' THEN
91 CASE
92 WHEN emprec.sal > 100 THEN
93 DBMS_OUTPUT.PUT_LINE('Salary1 : '||emprec.sal);
94
95 IF emprec.mgr = 7902 THEN
96 DBMS_OUTPUT.PUT_LINE('Correct Mgr.');
97
98 CASE
99 WHEN emprec.comm IS NULL THEN
100 DBMS_OUTPUT.PUT_LINE('Commission Is NULL.');
101 END CASE;
102
103 END IF;
104
105 WHEN emprec.sal > 500 THEN
106 DBMS_OUTPUT.PUT_LINE('Salary2 : '||emprec.sal);
107
108 WHEN emprec.sal > 1000 THEN
109 DBMS_OUTPUT.PUT_LINE('Salary3 : '||emprec.sal);
110 END CASE;
111
112 WHEN emprec.ename = 'ALLEN' THEN
113 DBMS_OUTPUT.PUT_LINE('Salary4 : '||emprec.sal);
114 END CASE;
115
116 DBMS_OUTPUT.PUT_LINE('ALTERNATIVE|||');
117
118 CASE emprec.ename
119 WHEN 'SMITH' THEN
120 CASE
121 WHEN emprec.sal > 100 THEN
122 DBMS_OUTPUT.PUT_LINE('Salary1 : '||emprec.sal);
123
124 IF emprec.mgr = 7902 THEN
125 DBMS_OUTPUT.PUT_LINE('Correct Mgr.');
126
127 CASE
128 WHEN emprec.comm IS NULL THEN
129 DBMS_OUTPUT.PUT_LINE('Commission Is NULL.');
130 END CASE;
131
132 END IF;
133
134 WHEN emprec.sal > 500 THEN
135 DBMS_OUTPUT.PUT_LINE('Salary2 : '||emprec.sal);
136
137 WHEN emprec.sal > 1000 THEN
138 DBMS_OUTPUT.PUT_LINE('Salary3 : '||emprec.sal);
139 END CASE;
140
141 WHEN 'ALLEN' THEN
142 DBMS_OUTPUT.PUT_LINE('Salary4 : '||emprec.sal);
143 END CASE;
144
145 END;
146 /
--OUTPUT :::
Result 1 : SCOTT
Result 2 : SCOTT
True
False
NULL
Inside Give_bonus Procedure :::
100
1500
Bonus Amount : 15000
Salary1 : 801
Correct Mgr.
Commission Is NULL.
ALTERNATIVE|||
Salary1 : 801
Correct Mgr.
Commission Is NULL.SQL> SELECT empno,ename,sal,CASE
2 WHEN sal > 0 AND sal <= 1000 THEN 'UNDERPAID'
3 WHEN sal > 1000 AND sal <= 2000 THEN 'MIDPAID'
4 WHEN sal > 2000 AND sal <= 5000 THEN 'CORRECTPAID'
5 ELSE 'TOPPAID'
6 END sal_status,
7 deptno,
8 CASE deptno
9 WHEN 10 THEN 'ACCOUNTING'
10 WHEN 20 THEN 'RESEARCH'
11 WHEN 30 THEN 'SALES'
12 WHEN 40 THEN 'OPERATIONS'
13 END dept_category
14 FROM emp;
EMPNO ENAME SAL SAL_STATUS DEPTNO DEPT_CATEG
---------- ---------- ---------- ----------- ---------- ----------
7839 KING 5001 TOPPAID 10 ACCOUNTING
7698 BLAKE 2851 CORRECTPAID 30 SALES
7782 CLARK 2451 CORRECTPAID 10 ACCOUNTING
7566 JONES 2976 CORRECTPAID 20 RESEARCH
7788 SCOTT 3001 CORRECTPAID 20 RESEARCH
7902 FORD 3001 CORRECTPAID 20 RESEARCH
7369 SMITH 801 UNDERPAID 20 RESEARCH
7499 ALLEN 1601 MIDPAID 30 SALES
7521 WARD 1251 MIDPAID 30 SALES
7654 MARTIN 1251 MIDPAID 30 SALES
7844 TURNER 1501 MIDPAID 30 SALES
7876 ADAMS 1101 MIDPAID 20 RESEARCH
7900 JAMES 951 UNDERPAID 30 SALES
7934 MILLER 1301 MIDPAID 10 ACCOUNTING
14 rows selected.
