oracle分歧处理,Oracle 中条件分歧小结

当前位置:我的异常网» 数据库 » Oracle 中条件分歧小结

Oracle 中条件分歧小结

www.myexceptions.net  网友分享于：2013-09-09  浏览：5次

Oracle 中条件分歧总结

Oracle 中条件分歧总结：

* Decode

* IF--THEN--ELSIF---END IF

* CASE WHEN---END CASE

1.DECODE介绍

In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE      statement.

The syntax for the decode function is:

decode( expression , search , result [, search , result]... [, default] )

expression is the value to compare.

search is the value that is compared against expression.result is the value returned, if expression is equal to search.default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).

Applies To:

Oracle 9i, Oracle 10g, Oracle 11g

For example:

You could use the decode function in an SQL statement as follows:

SELECT supplier_name,

decode(supplier_id,10000,'IBM',

10001,'Microsoft',

10002,'Hewlett Packard',

'Gateway') result

FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN

result := 'IBM';

ELSIF supplier_id = 10001 THEN

result := 'Microsoft';

ELSIF supplier_id = 10002 THEN

result := 'Hewlett Packard';

ELSE

result := 'Gateway';

END IF;

Refer to:http://www.techonthenet.com/oracle/functions/index.php

2. IF-THEN-ELSE Statement

There are three different syntaxes for these types of statements.

Syntax #1: IF-THEN

IF condition THEN

{...statements...}

END IF;

Syntax #2: IF-THEN-ELSE

IF condition THEN

{...statements...}

ELSE

{...statements...}

END IF;

Syntax #3: IF-THEN-ELSIF

IF condition THEN

{...statements...}

ELSIF condition THEN

{...statements...}

ELSE

{...statements...}

END IF;

Here is an example of a function that uses the IF-THEN-ELSE statement:

CREATE OR REPLACE Function IncomeLevel

( name_in IN varchar2 )

RETURN varchar2

IS

monthly_value number(6);

ILevel varchar2(20);

cursor c1 is

select monthly_income

from employees

where name = name_in;

BEGIN

open c1;

fetch c1 into monthly_value;

close c1;

IF monthly_value <= 4000 THEN

ILevel := 'Low Income';

ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN

ILevel := 'Avg Income';

ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN

ILevel := 'Moderate Income';

ELSE

ILevel := 'High Income';

END IF;

RETURN ILevel;

END;

3.Case Statement

Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.

The syntax for the case statement is:

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 is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)

condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the case statement will return the result and not evaluate the conditions any further.

result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.

Note:

If no condition is found to be true, then the case statement will return the value in the ELSE clause.

If the ELSE clause is omitted and no condition is found to be true, then the case statement will return NULL.

You can have up to 255 comparisons in a case statement. Each WHEN ... THEN clause is considered 2 comparisons.

Applies To:

Oracle 9i, Oracle 10g, Oracle 11g

For example:

You could use the case statement in an SQL statement as follows: (includes the expression clause)

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 could write the SQL statement using the case statement like this: (omits the expression clause)

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 above two case statements are equivalent to the following IF-THEN-ELSE statement:

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 will compare each owner value, one by one.

One thing to note is that the ELSE clause within the case statement is optional. You could have omitted it. Let's take a look at the SQL statement above with the ELSE clause omitted.

Your SQL statement would look as follows:

select table_name,

CASE owner

WHEN 'SYS' THEN 'The owner is SYS'

WHEN 'SYSTEM' THEN 'The owner is SYSTEM'

END

from all_tables;

With the ELSE clause omitted, if no condition was found to be true, the case statement would return NULL.

For Example:

Here is an example that demonstrates how to use the case statement to compare different conditions:

select

CASE

WHEN a < b THEN 'hello'

WHEN d < e THEN 'goodbye'

END

from suppliers;

Frequently Asked Questions

Question:  Can you create a case statement that evaluates two different fields? I want to return a value based on the combinations in two different fields.

Answer:  Yes, below is an example of a case statement that evaluates two different fields.

select supplier_id,

CASE

WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'

WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'

END

from suppliers;

文章评论