Oracle中的一些函數

一、比較大小函數 sign

函數語法：
sign(n)

函數說明：
取數字n的符號，大于0返回1；小于0返回-1；等于0返回0

示例：
1、select sign( 2 ),sign(- 2 ),sign( 0 ) from dual;

　 　SIGN(2) SIGN(-2) SIGN(0)
　———- ———- ———-
　　 1 -1 0

2、a=100 b=200
　　則sign(a-b)返回 -1

二、nvl(EXPR1,EXPR2)

函數語法：
NVL(EXPR1, EXPR2)

函數說明：
從兩個表達式返回一個非 null 值。如果 EXPR1的計算結果為 null 值，則 NVL( ) 返回 EXPR2。如果 EXPR1的計算結果不是 null 值，則返回 EXPR1。EXPR1和 EXPR2可以是任意一種數據類型。如果 EXPR1與 EXPR1的結果皆為 null 值，則 NVL( ) 返回 .NULL.。

返回值類型：
字符型、日期型、日期時間型、數值型、貨幣型、邏輯型或 null 值

應用：
在不支持 null 值或 null 值無關緊要的情況下，可以使用 NVL( ) 來移去計算或操作中的 null 值。

select nvl(a.name,'空得') as name from student a  join  school b  on  a.ID=b.ID

注意：兩個參數得類型要匹配

三、nvl2（exp1,exp2,exp3）

Oracle在NVL()的功能上擴展，提供了NVL2函數。該函數決意exp1是空值和非空值時返回哪個表達式：

• 假設exp1為空值，則返回exp3
• 假設exp1為非空，則返回exp2。

四、coalesce()函數

coalesce是一個函數， (expression_1, expression_2, ...,expression_n)依次參考各參數表達式，遇到非null值即停止并返回該值。如果所有的表達式都是空值，最終將返回一個空值。
1、coalesce函數是用來獲取第一個不為空的列的值
2、coalesce函數里面所有表達式必須為同一類型或者能轉換成同一類型
3、CREATE OR REPLACE VIEW v AS SELECT NULL AS c FROM dual;這樣建立的視圖，列c的數據類型是char。

注意：
COALESCE(expression1,...n) 與此 CASE 函數等價：
CASE  expression
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL

coalesce()函數可以用來完成幾乎所有的空值處理，不過在很多數據庫系統中都提
供了它的簡化版，這些簡化版中只接受兩個變量，其參數格式如下：
MySQL:

  IFNULL(expression,value)   //expr1不為null返回expr1，否則返回expr2

Oracle:
NVL(expression,value)
這幾個函數的功能和COALESCE(expression,value)是等價的。比如SQL語句用于返回人
員的“意中人”，如果自己的name不為空則將name做為“意中人”，如果name為空則返回NULL：

MySQL:  select name,herName,isnull(name,herName) as Love from table
Oracle: select name,herName,NVL(name,herName) as Love from table

五、is null、is not null 和 "<>"、!=

首先對Oracle中的null做一些簡單的說明：
1、oracle中null與0，空字符串，空格，包括null與null也是不等的。
2、oracle中對null做算術運算結果仍然為null。
3、處理方法有nvl函數，比較方法是is null或者is not null。
4、null不能被索引，比如select count (null) from dual的查詢結果是0。
5、null排序比其他所有類型的大，一個可以為空的字段倒排序，前面的都是null數據。

"<>''只能判斷空字符串，is Null是對Null字符的判斷，兩種完全不同的數據。一個是空字符，是個字符串，只不過是沒有值，另一個是空值(null)。null在數據庫中是特有的一類數據。where子句中的is null和is not null將不會使用索引而是進行全表搜索。因此優化效率需要通過改變查詢方式，分情況討論等方法，去掉where子句中的is null和is not null。
例一：

select  a.字段n from tab_a a where a.字段2 is not null;
//a.字段2增加了索引的，但是查詢速度非常慢，

做了如下優化：

select  a.字段n from tab_a a where nvl(a.字段2,'0') != '0';

速度提升很明顯。
原因是什么呢？其實很簡單，因為is null和is not null使字段的索引失效了。
例二：

select * from table where field = null;
select * from table where field is null;
如果 field 是null的話第一種條件查不出結果，第二種條件查的出結果。

原因是在oracle中，判斷一個字段或者一個變量是否為 null 語法是 "is null"。返回的結果是boolean型。如果使用 "= null"，會返回null。如果在代碼或語句中使用，可能會獲取不到結果或者報錯。另外，null并不表示不存在，而是未知，我們通常成為“UNKNOWN”。
如果case when條件寫判斷是否為null的話，會導致查詢的結果有未知的錯誤。
結論：如果要判斷是否為null，Oracle使用is null 或者is not null