Case statement in oracle database

[Kingfisher]

Hello sir ,

I am learning the PL/SQL programming and at the first stage of the development always curious to know the newer things in the database programming,I need to know How many ways we can implement the conditional statement in the PL/SQL programming.

The conditional statements definitions are much closer to the definition of other languages.If you have some ideas about this then please help me.

Thanks.

[Reegan]

Hello dude,
You can include the conditional statement anywhere in your but that should be under the execution section of the code.

It performs the same as the IF ELSE statements,you can create a condition in your program using IF ELSE statement and one more way is using CASE statement .The case statement provides the same functionality as the IF statement gives.It depends on you whatever you want to choose.It is introduces and applied with oracle 9i, 10g, 11g.

[opaper]

Starting from version Oracle 9i, you can use the case statement to specify the conditional statement within an SQL statement. It perform the same functionality of an IF-THEN-ELSE statement.

The syntax to define the CASE statement -

CASE  [ expression ]
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  WHEN condition_n THEN result_n
  ELSE result
END

Where the [ expression ] is optional,it shows the value to compare it with the list of conditions and the condition_1 is specified condition and result_1 is statement which would get print when the condition would be true and you can specify another condition as above.

[MindSpace]

The CASE statement can be evaluated as IF-ELSE statement.I am going to show you an example which will make the use of CASE expression more clear.

The example will match the condition and choose the category according to their salary.

select Emp_sal, case when Emp_sal < 2000 then 'Payment_category 1' 
                 when Emp_sal < 3000 then 'Payment_category 2' 
                 when Emp_sal < 4000 then 'Payment_category 3' 
                 else 'Payment_category 4' 
            end 
from employees_data;

The query using CASE statement and will allow the category according to payment given by to employee.The salary of particular employee would be checked with three conditions and if not found to all then will be send to the category 4th.

[Modifier]

You can perform the nesting of CASE statement when you need more and more condition statement depends on the requirement of program.

As you noted earlier,there are so many ways to define the CASE statement.I am going to show you an example contains the nested CASE statement.

The example is as follows-

select (case when <condition> then
                            (case when <condition> then 'value '
                                  else 'value'
                             end)
             when <conditio> then
                            (case when <condition> then 'value '
                                  else 'value'
                             end)
             else 'value'
        end) 
from  <table_name>
where  <condition>;