Oracle/PLSQL: Decode Function

最新推荐文章于 2024-05-14 23:25:50 发布

最新推荐文章于 2024-05-14 23:25:50 发布 · 84 阅读

文章标签：

#Oracle #Microsoft #IBM #SQL

本文详细介绍了 Oracle/PLSQL 中的 Decode 函数，该函数的功能类似于 IF-THEN-ELSE 语句。Decode 函数可以用于 SQL 查询中，通过比较表达式的值来返回不同的结果。文章还提供了一个具体的示例，展示了如何使用 Decode 函数以及它与 IF-THEN-ELSE 语句之间的等效性。

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

The syntax for the decode function is:
[code]
decode( expression , search , result [, search , result]... [, default] )
[/code]
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:
[code]
SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;
[/code]

The above decode statement is equivalent to the following IF-THEN-ELSE statement:
[code]
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;
[/code]

The decode function will compare each supplier_id value, one by one.