SQL Case Function

Case Function

CASE:- CASE statement is used for series of IF statements.CASE statement is evaluated from top to bottom. If a condition is true, then corresponding THEN clause is executed and execution jumps to the END CASE clause.

Note:- Case has two types syntax

1) Value Match CASE Expression
Example1:- 

SELECT ename, empno, deptno
  (CASE deptno
     WHEN 10 THEN 'Accounting'
     WHEN 20 THEN 'Research'
     WHEN 30 THEN 'Sales'
     WHEN 40 THEN 'Operations'
     ELSE 'Unknown'
   END) department
FROM emp
ORDER BY ename;


2) Searched CASE Expression
SELECT ename, empno, deptno
  (CASE
     WHEN deptno=10 THEN 'Accounting'
     WHEN deptno=20 THEN 'Research'
     WHEN deptno=30 THEN 'Sales'
     WHEN deptno=40 THEN 'Operations'
     ELSE 'Unknown'
   END) department
FROM emp
ORDER BY ename;


Note:-We can use any operators in searched CASE Expression. 
Example2:- To display grade based on their salaries.

select ename, case when sal < 1000  then 'Low Salary'
            when (sal >=1000 and sal < 2000)  then 'Medium Salary'
            when (sal >= 2000 and sal < 3000) then 'Enough Salary'
            else 'High Salary'
            end sal_grade
from emp ;


Example3:-

select e.ename,case when e.job in ('PRESODENT')    then 'Boss'
                        when e.job in ('MANAGER')  then 'Managers'
                        else 'General Employees'
                        end emp_category
from emp e;


Note:-We can use CASE in PLSQL with same syntax
Example4:-

declare
    grade char(1);
begin
    grade := '&grade';
    case grade
      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;
/

PL/SQL procedure successfully completed.