Usage of ORACLE/PLSQL case Conditional statement

statement syntax

The code is as follows	Copy Code
case [Expression] When Condition_1 THEN result_1 When Condition_2 THEN result_2 ... When Condition_n THEN result_n ELSE result End

expression is optional. It's worth the list of conditions that you compare. (ie: condition_1,condition_2, ... condition_n)

Condition_1 to Condition_n must be of the same data type. The order that is listed in the condition evaluation. One condition is that once the discovery is true, the case statement will return the result and not evaluate any further conditions.

Result_1 to Result_n must be of the same data type. This is the return value of a condition that once found is true.

Attention:

If there is no condition to be true, then the case statement returns the value in the ELSE clause.

If the ELSE clause is omitted and any condition discovery is true, then the case statement returns NULL.

A maximum of 255 can be compared in a case statement. When, Each ... The terms are considered to be 2 comparisons.

Applies To:

Oracle 9i, Oracle 10g, Oracle 11g

Instance
You can use the case statement in the SQL statement as follows: (including expression clauses)

The code is as follows	Copy Code
Select table_name, Case owner When ' sys ' THEN ' the owner is SYS ' When ' system ' THEN ' the owner is System ' ELSE ' The owner is another value ' End From All_tables;

Or you can write an SQL statement, using the case to declare: (omitting the expression clause)

The code is as follows	Copy Code
Select table_name, Case When owner= ' sys ' THEN ' the owner is sys ' When owner= ' system ' THEN ' the owner is System ' ELSE ' The owner is another value ' End From All_tables;

The if-then-else statements below the two case statements are equivalent:

The code is as follows	Copy Code
IF owner = ' SYS ' THEN Result: = ' The owner is SYS '; elsif owner = ' SYSTEM ' THEN Result: = ' The owner is SYSTEM '; ELSE Result: = ' The owner is another value '; End IF;

The case statement compares the value of each owner, one after the other.

It is important to note that the ELSE clause in the case statement is optional. You can omit. Let's look at the above SQL statement and the ELSE clause omitted.

Your SQL statement looks like this:

The code is as follows	Copy Code
Select table_name, Case owner When ' sys ' THEN ' the owner is SYS ' When ' system ' THEN ' the owner is System ' End From All_tables;

Instance
Here's an example that shows how to use case statements to compare different conditions:

code is as follows	copy code
select Case   where a < b THEN ' Hello '   when D < e THEN ' goodbye ' end from suppliers;