Oracle/plsql case Condition Statement usage

Source: Internet
Author: User

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.