学习笔记----几个Oracle函数

1.比较大小函数SIGN

sign(x)或者Sign(x)叫做符号函数，其功能是取某个数的符号（正或负）：

当x>0，sign(x)=1;

当x=0，sign(x)=0;

当x<0， sign(x)=-1；

x可以是函数或计算表达式

SIGN函数经常和DECODE函数组合运用，

例如

2.流程控制函数DECODE

在逻辑编程中，经常用到If – Then –Else 进行逻辑判断。在DECODE的语法中，实际上就是这样的逻辑处理过程。它的语法如下：

DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )

Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。如果value结果不等于给出的任何配对时，Decode 结果就返回else 。 这里的if、then及else 都可以是函数或计算表达式。

DECODE的优势：

CASE 语句返回的值需一致，否则语句不成立，但是decode则可以。  

例如今天遇到的返回值有字符，有数值。

select decode(comm,null,'no comm',comm) from employees  

 decode可以违反第3NF（行不可再分，列不可再分，列不可重复）：列重复

[sql] view plain copy

 print?

1. hr@ORCL> select * from a;  
2.   
3.         ID NAME  
4. ---------- ----------  
5.          1 a  
6.          2 b  
7.          3 c  
8.          1 a  
9.   
10. hr@ORCL> select sum(decode(id,1,1,0)) think,  
11.   2             sum(decode(id,2,2,0)) water,  
12.   3             sum(decode(id,3,3,0)) linshuibin  
13.   4        from a;  
14.   
15.      THINK      WATER LINSHUIBIN  
16. ---------- ---------- ----------  
17.          2          2          3  

 

    一个字段，decode函数可以完全改写简单case；

    多个字段，需要复杂的case，方可。

    语法：
    DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返then1,...,如果不等于任何一个if值，则返回else。可以用函数或表达式来替代value，if，then，else从而作出一些更有用的比较。

    来看看具体的运用：
    1 假设我们想给百度职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％
    则：

    select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary) "revised_salary" from employee

    2 表table_subject，有subject_name列。要求按照：语、数、外的顺序进行排序
    则：

    select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3)

    case表达式

    case表达式，可分两种，简单和搜索，简单case后接表达式，如：

    对于简单的case需要几点注意：

    1）寻找when的优先级：从上到下

    2）再多的when，也只有一个出口，即其中有一个满足了expr就马上退出case

    3）不能把return_expr和else_expr指定为null，而且，expr、comparison_expr和return_expr的数据类型必须相同。

  搜索case：

    CASE WHEN condition THEN return_expr

              [WHEN condition THEN return_expr]

              ...

    ELSE else_expr

    END

    例子：

[sql] view plain copy

 print?

1. SELECT (CASE WHEN cust_credit_limit BETWEEN  0 AND 3999 THEN  ' 0 - 3999'  
2.    WHEN cust_credit_limit BETWEEN  4000 AND 7999 THEN ' 4000 - 7999'  
3.    WHEN cust_credit_limit BETWEEN  8000 AND 11999 THEN  ' 8000 - 11999'  
4.    WHEN cust_credit_limit BETWEEN  12000 AND 16000 THEN '12000 - 16000' END)  
5.   AS BUCKET, COUNT(*) AS Count_in_Group  
6. FROM customers WHERE cust_city = 'Marshal' GROUP BY  
7.  (CASE WHEN cust_credit_limit BETWEEN  0 AND 3999 THEN ' 0 - 3999'  
8.  WHEN cust_credit_limit BETWEEN  4000 AND 7999 THEN ' 4000 - 7999'  
9.  WHEN cust_credit_limit BETWEEN  8000 AND 11999 THEN  ' 8000 - 11999'  
10.  WHEN cust_credit_limit BETWEEN  12000 AND 16000 THEN '12000 - 16000' END);  
11.   
12. BUCKET        COUNT_IN_GROUP  
13. ------------- --------------  
14.  0 - 3999                  8  
15.  4000 - 7999               7  
16.  8000 - 11999              7  
17. 12000 - 16000              1  

case语句可以用在 SELECT,FROM WHERE,和ORDER By 排序。

在用CASE语句排序是 要先CASE 再需要排序的列 才会有效。

NVL家族:NVL,NVL2，COALESCE

NVL

语法：NVL( expr1, expr2) 　　

功能：如果expr1为NULL，则NVL函数返回expr2的值，否则返回expr1的值，如果两个参数的都为NULL ，则返回NULL。 　　

注意事项：expr1和expr2必须为同一数据类型，除非显式的使用TO_CHAR函数进行类型转换。

 

NVL2

语法：NVL2(expr1,expr2,expr3)

功能：如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。

 

NULLIF

语法：NULLIF ( expression1 , expression2 ) 　　

参数：expression1,expression2 常量、列名、函数、子查询或算术运算符、按位运算符以及字符串运算符的任意组合。 　　

返回类型：返回类型与第一个 expression1 相同。 　　

输出结果：如果两个表达式不相等，NULLIF 返回第一个 expression1 的值。如果两个表达式相等，NULLIF 返回空值NULL。

 

COALESCE

语法：COALESCE ( expression [ ,...n ] )

参数：expression 任何类型的表达式。n表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。

返回类型：将相同的值作为 expression 返回。

作用：返回表达式中第一个非空表达式

注意：如果所有自变量均为 NULL，则 COALESCE 返回 NULL 值。 　　COALESCE(expression1,...n) 与此 CASE 函数等价： 　　CASE 　　WHEN (expression1 IS NOT NULL) THEN expression1 　　... 　　WHEN (expressionN IS NOT NULL) THEN expressionN 　　ELSE NULL

返回值类型

字符型、日期型、日期时间型、数值型、货币型、逻辑型或 null 值

    对于简单的case需要几点注意：

    1）寻找when的优先级：从上到下

    2）再多的when，也只有一个出口，即其中有一个满足了expr就马上退出case

    3）不能把return_expr和else_expr指定为null，而且，expr、comparison_expr和return_expr的数据类型必须相同。