ORACLE PLSQL CASE STATEMENT

ORACLE PLSQL CASE STATEMENT

                         Oracle case statement uses selector rather than multiple boolean expressions. Selects sequence based on selector, Its more readable compared to IF THEN ELSE.

Note:CASE Statement is an alternative to IF THEN ELSE statement in Oracle PL/SQL.

SYNTAX:

            case selector 

              when condition(s) then 

              ....

             ....

            ....

            else 

          End Case;

CASE Statement in  Select Statement

Create table called "Sports"

SQL> create table sports(ID number(2),name varchar2(20),country char(2));

SQL> insert into sports values(1,'cricket','IN');
SQL> insert into sports values(2,'cricket','UK');
SQL> insert into sports values(3,'Hand FootBall','US');
SQL> insert into sports values(4,'Base Ball','US');

SQL> select * from sports;

        ID NAME                 CO
---------- -------------------- --
         1 cricket              IN
         2 cricket              UK
         3 Hand FootBall        US
         4 Base Ball            US

Display Custom message using Case Statement

select Name, case country
when 'IN' then Name||' most played game in '||country
when 'US' then Name||' most played game in '||country
when 'UK' then Name||' most played game in '||country
End "MSG"
from sports

SQL>/

NAME                 MSG
-------------------- -------------------------------------------
cricket              cricket most played game in IN
cricket              cricket most played game in UK
Hand FootBall        Hand FootBall most played game in US
Base Ball            Base Ball most played game in US

CASE STATEMENT IN PL/SQL ANONYMOUS BLOCK

declare
       msg varchar2(20);
  cursor sportsCur is select * from sports;
begin
             for i in sportsCur
              loop
                case i.country
                 when 'IN' then 

                                   Dbms_output.put_line(i.id||' '||i.name||' is most played game in INDIA');
                 when 'US' then 

                                  Dbms_output.put_line(i.id||' '||i.name||' is most played game in United States');
                 when 'UK' then 

                               Dbms_output.put_line(i.id||' '||i.name||' is most played game in United Kingdom');
               else  dbms_output.put_line(i.id||' '||i.name||' '||i.country);
              end case;
             end loop;
end;
 

Execute the Script

SQL>/

1 cricket is most played game in INDIA
2 cricket is most played game in United Kingdom
3 Hand FootBall is most played game in United States
4 Base Ball is most played game in United States

PL/SQL procedure successfully completed.

Tags:  ORACLE PLSQL CASE STATEMENT,Oracle case statement, PL/SQL case statement,

PLSQL case statement, how to use PL/SQL case statement, PL/SQL case statement syntax,Using Case statement inside for loop.