Oracle中的一些函數(shù)

一、比較大小函數(shù) sign

函數(shù)語法：
sign(n)

函數(shù)說明：
取數(shù)字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)

函數(shù)語法：
NVL(EXPR1, EXPR2)

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

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

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

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

注意：兩個(gè)參數(shù)得類型要匹配

三、nvl2（exp1,exp2,exp3）

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

• 假設(shè)exp1為空值，則返回exp3
• 假設(shè)exp1為非空，則返回exp2。

四、coalesce()函數(shù)

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

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

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

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

Oracle:
NVL(expression,value)
這幾個(gè)函數(shù)的功能和COALESCE(expression,value)是等價(jià)的。比如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做算術(shù)運(yùn)算結(jié)果仍然為null。
3、處理方法有nvl函數(shù)，比較方法是is null或者is not null。
4、null不能被索引，比如select count (null) from dual的查詢結(jié)果是0。
5、null排序比其他所有類型的大，一個(gè)可以為空的字段倒排序，前面的都是null數(shù)據(jù)。

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

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

做了如下優(yōu)化：

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

速度提升很明顯。
原因是什么呢？其實(shí)很簡單，因?yàn)閕s null和is not null使字段的索引失效了。
例二：

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

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