"This is the most accepted question for PLSQL developer role.It has been asked in many interviews like Mphasis and Kyndryl."
INTRODUCTION:
Case was came after oracle database version 8.1.6 whereas decode was came before oracle database version 8.1.6.Decode and case statements in an oracle work or acts like if then elsif conditional statements.
if sal='2000000' then 'General Manager'
elsif sal='3000000' then 'Senior Manager'
elsif sal='1200000' then 'Manager'
else 'Employee'
end if;
create table student_details(Student_RollNo Number,Student_Name Varchar2(20),student_marks Number,student_result varchar2(1));
insert into student_details values (100,'Rahul',90,'P');
insert into student_details values (101,'Rakesh',30,'F');
insert into student_details values (102,'Ram',60,'P');
insert into student_details values (103,'Rohan',10,'F');
insert into student_details values (104,'Radhe',50,Null);
commit
select * from student_details
select Student_RollNo,Student_Name,Student_result,DECODE(student_result,'P','PASS','F','Fail','Result is not present or available') As result
from student_details;
select student_RollNo,Student_Name,Student_marks,Student_result,CASE
WHEN student_result='P' and student_marks >='60' THEN 'PASSED WITH FIRST CLASS'
WHEN student_result='P' and student_marks<'60' THEN 'PASSED WITH SECOND CLASS'
WHEN student_result='F' and student_marks<'50' THEN 'FAIL'
ELSE 'Result is not present or available'
END
As result
FROM student_details;
select Student_RollNo,Student_Name,Student_result,
case student_result
when 'P' THEN 'PASS'
when 'F' THEN 'FAIL'
ELSE 'Result is not present or available'
END
As result
FROM student_details;
Case can do every activity or tasks as like DECODE.Since,Decode is the old version of the function and case is the new version or extended version. Case includes all the features of Decode. Case is much faster than decode in performance wise.
Case is used with logical operators i.e. <,>,<=,>= and = whereas decode is used with equality operator (=).
select ename,sal,case when (sal>=4000 and sal<=5000) then 'Grade A'
when (sal>=3000 and sal<=4000) then 'Grade B'
when (sal>=2000 and sal<=3000) then 'Grade C'
else 'Grade D'
end sal_Grade from emp where rownum<4;
select ename,sal,case when (sal>=4000 and sal<=5000) then 'Grade A'
when (sal>=3000 and sal<=4000) then 'Grade B'
when (sal>=2000 and sal<=3000) then 'Grade C'
else 'Grade D'
end sal_Grade from emp;
Case work with predicates and searchable queries. In the below mention query, predicate with in is used for an employee post category and searchable subqueries are used with the exist clause in common bracket. Decode works with an expression that are scalar values only.
select e.ename,case when e.ename in('KING','SMITH','WARD') then 'Senior Manager'
when exists(select 1 from emp emp1 where emp1.mgr=e.empno) then 'Managers'
else 'Employees'
end emp_post
from emp e
where rownum < 5;
Case can be used in expression in plsql and sql whereas decode can be used in the sql as function.
Decode is called as the function because it can take input in the form of open and closed () brackets.
declare
grade char(1);
begin
grade := 'h';
case grade
when 'h' then dbms_output.put_line('Excellent');
when 's' then dbms_output.put_line('Very Good');
when 'a' then dbms_output.put_line('Good');
when 'b' then dbms_output.put_line('Average');
when 'c' then dbms_output.put_line('Poor');
when 'f' then dbms_output.put_line('Fail');
else dbms_output.put_line('No such grade');
end case;
end;
/
Case can work as the parameter to the procedure call whereas Decode cannot work as the parameter to the procedure call.
create or replace procedure padd(num1 number,num2 number) is
vadd number;
begin
vadd:=num1+num2;
dbms_output.put_line('Addition of two numbers is:'||vadd);
end;
exec padd(case &val when 10 then 100 else 1 end,100);
exec padd(decode(&val,10,100,1),100);
Case is handling null value differently like Decode.
select decode(null,null,0,2) from dual;
select case null when null then 0 else 2
end
from dual;
select CASE null
when null then 'NULL'
else 'NOT NULL'
end result from dual;
select DECODE(null,null,'NULL','NOT NULL')result
from dual;
Searched Case work like Decode.
select case
when null is null then 'NULL'
else 'NOT NULL'
end result
from dual;
Case provides data type inconsistency error but decode does not provides data type inconsistency error.
select case 2 when 1 then '1'
when '2' then '2'
when 3 then '3'
else '4'
end
from dual;
select decode(2,1,1,
'2','2',
'3','3',
4) from dual;
Decode is not used in PLSQL block as a function and will display an error message like 'DECODE' may be used inside a SQL statement only.
Case is ANSI standard whereas Decode is oracle standard.
Decode is easy and shorter to read and understand than case.
select ename,deptno,decode(deptno,10,'Computer',
20,'IT',
30,'Civil',
'Not Known') as departments
from emp;
select ename,deptno,decode(deptno,10,'Computer',
20,'IT',
30,'Civil',
'Not Known') as departments
from emp
where rownum<4;
select ename,deptno,case deptno
when 10 then 'Computer'
when 20 then 'IT'
when 30 then 'Not Known'
else 'Not Known'
end as departments
from emp;
select ename,deptno,case deptno
when 10 then 'Computer'
when 20 then 'IT'
when 30 then 'Not Known'
else 'Not Known'
end as departments
from emp
where rownum<5;
Debug of case is easier than debug of decode.
Case is preferred for huge logic of complexity where as decode is preferred to use for less logic.
Decode can go for null validation whereas case cannot go for null validation.
Case datatype must be same in all the conditional check where as decode data type may be different in the conditional check.
SELECT DECODE(:input,'1','One',2,'Two','3','Three','Nothing')
from dual;
Decode can be used in the select statement whereas case is used in the plsql block.
Case is directly used in the plsql block whereas decode is indirectly used in the plsql block.
set serveroutput on;
declare
b varchar2(5);
begin
b:='B';
case b
when 'A' then dbms_output.put_line('Incorrect Output Details');
when 'B' then dbms_output.put_line('correct Output Details');
end case;
end;
/
create or replace procedure print_number(n number)
is
begin
dbms_output.put_line(n);
end;
/
begin
print_number(case &n when 10 then 30 else 100 end);
end;
Case can be used as the part of an expresssion or standalone statement.
DECLARE
Student_result Varchar2(10);
student_input varchar2(10);
BEGIN
SELECT DECODE(:student_input,'P','PASS','F','Fail','Null') INTO Student_result from dual;
END;
/
DECLARE
Student_result varchar2(100);
Student_inout varchar2(1):='P';
BEGIN
Student_result:=case Student_inout
WHEN 'P' THEN 'Pass'
WHEN 'F' THEN 'Fail'
ELSE 'Not Failed or Pass' End;
END;
/
Case is used in the where clause whereas decode is not used in the where clause.
The syntax for decode is
DECODE(Expression,Search,Result,[Search,Result]...[,default]
The syntax for case is
CASE [EXPRESSION]
WHEN condition_1 then result_1
WHEN condition_2 then result_2
END result
WHEN condition_n then result_n
END CASE
|
CASE
|
DECODE
|
|
1). Case was came after oracle
database version 8.1.6 .
|
1). Decode was came before oracle
database version 8.1.6.
|
|
2). Case is the new version or extended version.
|
2). Decode is the old version of the
function.
|
|
3). Case is used with logical
operators i.e. <,>,<=,>=.
|
3). Decode is used with equality
operator (=).
|
|
4). Case work with predicates and
searchable queries.
|
4). Decode works with an expression
that are scalar values only.
|
|
5). Case can be used in expression in
plsql and sql.
|
5). Decode can be used in the sql as
function.
|
|
6). Case can work as the parameter to
the procedure call.
|
6). Decode cannot work as the
parameter to the procedure call.
|
|
7) Case is handling null value as
different like DECODE and returns NOT NULL.
|
7) Decode is handling null value as
different like CASE and returns
NULL.
|
|
8) Case provides data type
inconsistency error.
|
8) Decode does not provides data type
inconsistency error.
|
|
9) Case is used in the plsql block.
|
9) Decode can be used in the select
statement.
|
|
10) Case is directly used in the plsql
block.
|
10) Decode is in directly used in
the plsql block.
|
|
11) Case is used in the where clause.
|
11) Decode is not used in the
where clause.
|
|
12) Case
is much faster than as performance wise.
|
12) Decode
is lesser than case as performance wise.
|
|
13) Case
is ANSI standard.
|
13) Decode
is oracle standard.
|
|
14) Case
is not easy,shorter to read and understand than decode.
|
14) Decode is easy ,shorter to
read and understand than case.
|
|
15) Debugging of case is easier.
|
15) Debugging of Decode is easier.
|
|
16) Case is preferred for huge logic
of complexity.
|
16) Decode is preferred to use for
less logic.
|
|
17) Case cannot go for null
validation.
|
17) Decode can go for null validation.
|
|
18) Case datatype must be same in all
the conditional check.
|
18) Decode data type may be different
in the conditional check.
|
|
19) The syntax for case is
CASE [EXPRESSION]
WHEN condition_1 then result_1
WHEN condition_2 then result_2
END result
WHEN condition_n then result_n
END CASE
|
19) The syntax for decode is
DECODE(Expression,Search,Result,[Search,Result]...[,default]
|
Comments
Post a Comment