Oracle fuction CASE example

oracle case


in plsql ......

declare
grade varchar2(2):='A';
begin
IF grade = 'A' THEN
dbms_output.put_line('Excellent');
ELSIF grade = 'B' THEN
dbms_output.put_line('Very Good');
ELSIF grade = 'C' THEN
dbms_output.put_line('Good');
ELSIF grade = 'D' THEN
dbms_output. put_line('Fair');
ELSIF grade = 'F' THEN
dbms_output.put_line('Poor');
ELSE
dbms_output.put_line('No such grade');
END IF;
end;
/


Now using case

we can write above query like this also......................

declare

c1 varchar2(2):='B';

begin

CASE c1
WHEN 'A' THEN dbms_output.put_line('Excellent');
WHEN 'B' THEN dbms_output.put_line('Very Good');
WHEN 'C' THEN dbms_output.put_line('Good');
WHEN 'D' THEN dbms_output.put_line('Fair');
WHEN 'F' THEN dbms_output.put_line('Poor');
ELSE dbms_output.put_line('No such grade');
END CASE;
end;
/


select case 'A'
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
WHEN 'D' THEN 'Fair'
WHEN 'F' THEN 'Poor'
ELSE 'No such grade'
end
from dual
/
select case deptno
WHEN 10 THEN 'Excellent'
WHEN 20 THEN 'Very Good'
WHEN 30 THEN 'Good'
ELSE 'No such grade'
end,ename
from emp
/



declare
v_job varchar2(9);
geh char(20);
begin
SELECT job INTO v_job FROM emp WHERE empno = 7521;
geh := CASE v_job
WHEN 'PRESIDENT' THEN 1
WHEN 'SALESMAN' THEN 1.07
WHEN 'MANAGER' THEN 1.05
WHEN 'CLERK ' THEN 1.1
WHEN 'ANALYST' THEN 1.04
ELSE 0
end ;
UPDATE emp SET sal = sal* geh;
END;