Oracle/plsql case Condition Statement usage

This article will introduce the usage of the oracle/plsql case Condition Statement, which is similar to mysql mssql. If you need to learn more, let's take a look.

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. Its value is the list of conditions you compare. (I .e., condition_1, condition_2,... condition_n)

Both condition_1 and condition_n must be of the same data type. The order listed in the condition evaluation. One condition is that once it is found to be true, the case statement returns the result and does not evaluate any further conditions.

Both result_1 and result_n must be of the same data type. This is a condition for the returned value, once found to be true.

Note:

If no condition is true, the case statement returns the value in the ELSE clause.

If the ELSE clause is omitted and any conditions are found to be true, the case statement returns NULL.

A maximum of 255 cases can be compared. Every... clause is considered as a 2 comparison.

Applies:

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' The owner is System' ELSE 'the owner is another value' END From all_tables;

Or you can write an SQL statement to declare the statement in this case: (the expression clause is omitted)

The Code is as follows:	Copy code
Select table_name, CASE WHEN owner = 'sys 'then' The owner is SYS' WHEN owner = 'system' The owner is System' ELSE 'the owner is another value' END From all_tables;

The IF-THEN-ELSE statements in the preceding two cases 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 by one.

Note that the else clause of the case statement is optional. You can omit it. Let's see if the preceding SQL statement is omitted from the ELSE clause.

Your SQL statement is as follows:

The Code is as follows:	Copy code
Select table_name, CASE owner WHEN 'sys 'then' The owner is SYS' WHEN 'system' The owner is System' END From all_tables;

Instance
The following example demonstrates how to use the case statement to compare different conditions:

 

The Code is as follows:	Copy code
Select CASE WHEN a <B THEN 'hello' WHEN d <e THEN 'Goodbye' END From suppliers;