Oracle db使用转换函数和条件表达式

下列函数可用于任何数据类型，且适合使用空值的场合：

• NVL (expr1, expr2)

• NVL2 (expr1, expr2, expr3)

• NULLIF (expr1, expr2)

• COALESCE (expr1, expr2, ..., exprn)

条件表达式

• 使你可以在SQL 语句中使用IF-THEN-ELSE逻辑。

• 使用下面两种方法：

– CASE表达式

– DECODE函数

在SQL 语句中，用于执行条件处理（IF-THEN-ELSE逻辑）的两种方法是CASE表达式和DECODE函数。

注：CASE表达式符合ANSI SQL。而DECODE函数是Oracle 专用的语法。

CASE表达式

具有与IF-THEN-ELSE语句相同的功效，可简化条件查询：

CASE    expr     WHEN     comparison_expr1    THEN     return_expr1
[WHEN    comparison_expr2     THEN    return_expr2 
WHEN    comparison_exprn    THEN    return_exprn
ELSE     else_expr] 
END

CASE表达式使你可以在SQL 语句中使用IF-THEN-ELSE逻辑，而无需调用任何过程。

在简单的CASE表达式中，Oracle Server 会搜索其expr等于comparison_expr的第一对WHEN ... THEN，然后返回return_expr。如果没有满足此条件的WHEN ...THEN对，并且存在一个ELSE子句，则Oracle Server 将返回else_expr。否则，OracleServer 会返回空值。你不能为所有的return_expr和else_expr指定文字值ULL。表达式expr和comparison_expr必须具有相同的数据类型，可以是CHAR、VARCHAR2、NCHAR或NVARCHAR2。所有返回值(return_expr) 必须具有相同的数据类型。

使用CASE表达式

具有与IF-THEN-ELSE语句相同的功效，可简化条件查询：

hr@TEST0924> SELECT last_name, job_id, salary,
  2  CASE job_id WHEN 'IT_PROG' THEN 1.10*salary 
  3  WHEN 'ST_CLERK' THEN 1.15*salary
  4  WHEN 'SA_REP'  THEN 1.20*salary
  5  ELSE salary END "REVISED_SALARY"
  6  FROM employees;

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
OConnell                  SH_CLERK         2600           2600
Grant                     SH_CLERK         2600           2600
Whalen                    AD_ASST          4400           4400
Hartstein                 MK_MAN          13000          13000

示例中的SQL 语句用于解码JOB_ID的值。如果JOB_ID为IT_PROG，则薪金增加10%；如果JOB_ID为ST_CLERK，则薪金增加15%；如果JOB_ID为SA_REP，则薪金增加20%。对于所有其它职务角色，不增加薪金。

可以使用DECODE函数编写同样的语句。

以下代码是一个搜索CASE表达式示例。在搜索CASE表达式中，搜索从左到右进行，直到找到列出的条件，然后它会返回相应的返回表达式。如果找不到满足的条件，但存在一个ELSE子句，则返回ELSE子句中的返回表达式，其它情况则返回NULL。

hr@TEST0924> SELECT last_name,salary, 
  2  (CASE WHEN salary<5000 THEN 'Low' 
  3  WHEN salary<10000 THEN 'Medium' 
  4  WHEN salary<20000 THEN 'Good' 
  5  ELSE 'Excellent' 
  6  END) qualified_salary 
  7  FROM employees; 

LAST_NAME                     SALARY QUALIFIED
------------------------- ---------- ---------
OConnell                        2600 Low
Fay                             6000 Medium
Mavris                          6500 Medium
Higgins                        12008 Good
King                           24000 Excellent

DECODE函数

具有与CASE表达式或IF-THEN-ELSE语句相同的功效，

可简化条件查询：

DECODE(col|expression, search1, result1 
[, search2, result2,...,]
[, default])

DECODE函数用于对表达式进行解码，其方式与在各种语言中使用的IF-THEN-ELSE逻辑类似。DECODE函数在将表达式与每个搜索值进行比较之后对表达式进行解码。如果表达式与搜索值相同，则返回结果。

如果省略了默认值，并且搜索值不与任何结果值相匹配，则会返回一个空值。

使用DECODE函数

hr@TEST0924> SELECT last_name, job_id, salary, 
  2  DECODE(job_id, 'IT_PROG', 1.10*salary, 
  3  'ST_CLERK', 1.15*salary, 
  4  'SA_REP',  1.20*salary, 
  5  salary)
  6  REVISED_SALARY 
  7  FROM employees; 

LAST_NAME                 JOB_ID         SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
OConnell                  SH_CLERK         2600           2600
Grant                     SH_CLERK         2600           2600
Whalen                    AD_ASST          4400           4400
Hartstein                 MK_MAN          13000          13000

示例中的SQL 语句用于测试JOB_ID的值。如果JOB_ID为IT_PROG，则薪金增加10%；如果JOB_ID为ST_CLERK，则薪金增加15%；如果JOB_ID为SA_REP，则薪

金增加20%。对于所有其它职务角色，不增加薪金。

如果使用伪代码，则可将上述语句表示为以下IF-THEN-ELSE语句：

IF job_id = 'IT_PROG' THEN salary = salary*1.10 
IF job_id = 'ST_CLERK' THEN salary = salary*1.15 
IF job_id = 'SA_REP' THEN salary = salary*1.20 
ELSE salary = salary

使用DECODE函数

显示部门80 中每位雇员的适用税率：

hr@TEST0924> SELECT last_name, salary, 
  2  DECODE (TRUNC(salary/2000, 0),
  3  0, 0.00,
  4  1, 0.09,
  5  2, 0.20,
  6  3, 0.30,
  7  4, 0.40,
  8  5, 0.42, 
  9  6, 0.44,
 10  0.45) TAX_RATE
 11  FROM employees
 12  WHERE department_id = 80;

LAST_NAME                     SALARY   TAX_RATE
------------------------- ---------- ----------
Russell                        14000        .45
Partners                       13500        .44
Errazuriz                      12000        .44

此示例显示使用DECODE函数的另一个示例。在此示例中，根据月薪确定部门80 中每位雇员的税率。税率如下所示：

月薪范围税率

$0.00-1,999.99 00%

$2,000.00-3,999.99 09%

$4,000.00-5,999.99 20%

$6,000.00-7,999.99 30%

$8,000.00-9,999.99 40%

$10,000.00-11,999.99 42%

$12,200.00-13,999.99 44%

$14,000.00 或更高 45%

小结

请牢记以下内容：

• 转换函数可以转换字符、日期和数字值：TO_CHAR、TO_DATE、TO_NUMBER。

• 还有几个适合处理空值的函数，其中包括NVL、NVL2、NULLIF和COALESCE。

• 通过使用CASE表达式或DECODE函数，可以在SQL 语句中应用IF-THEN-ELSE逻辑。