今天一直在研究參數返回之類的問題,上午正好有機會和大家討論一下.

???? 1.nvl函數

???? ? ? nvl函數將一個null值轉換為一個實際的值,數據類型可所以日期,數字,字符,數據類型必須匹配:如:
? ? ? ? nvl(commision,0)

???? ? ? ? ? nvl(hiredate,'01-JAN-87')

???? ? ? ? ? nvl(job_id,'no manager')

???? ? ? ? ? nvl(to_char(job_id),'no manager')

???? ? ? nvl可以轉換任何數據類型,但是轉換的數據類型返回值必須是nvl(expr1,expr2)第一個參數expr的類型.如:date,number, ? ? varchar2 or char

???? ? ? 例子:盤算員工的年薪,獎金為空的用0取代。

???? ? ? SQL> select empno,ename,sal,nvl(comm,0) comm, (sal+nvl(comm,0))*12 annual_sal from emp ;

???? ???? EMPNO ENAME???????????? SAL?????? COMM ? ANNUAL_SAL
? ? ?------- ---------- ---------- ---------- ----------
? ? ?7369 SMITH???????????? 800????????? 0?????? 9600
? ? ?7499 ALLEN??????????? 1600??????? 300????? 22800
? ? ?7521 WARD???????????? 1250??????? 500????? 21000
? ? ?7566 JONES??????????? 2975????????? 0????? 35700
? ? ?7654 MARTIN?????????? 1250?????? 1400????? 31800
? ? ?7698 BLAKE??????????? 2850????????? 0????? 34200

???? ? ? nvl(arg,value)代表如果后面的arg的值為null那么返回的值為后面的value? 如: NVL(a,b)就是判斷a是否是NULL,如果不是返回a的值,如果是返回b的值通過查詢取得某個字段的總計值,如果這個值位null將給出一個預設的默許值

???? 返回值類型 :
字符型、日期型、日期時間型、數值型、貨幣型、邏輯型或 null 值?
說明:
在不支持 null 值或 null 值可有可無的情況下,可以使用 NVL( ) 來移去盤算或操縱中的 null 值. select nvl(a.name,'空值') as name from student a join school b on a.ID=b.ID 注意:兩個參數得類型要匹配 問:什么是NULL？ 答:在我們不知道具體有什么數據的時候,也即未知,可以用NULL, 我們稱它為空,ORACLE中,含有空值的表列長度為零。
ORACLE答應任何一種數據類型的字段為空，除了以下兩種情況：

???? 1)主鍵字段（primary key），

???? 2)定義時已經加了NOT NULL限制條件的字段

???? 說明:

???? (1)等價于沒有任何值、是未知數。

???? (2)NULL與0、空字符串、空格都不同。

???? (3)對空值做加、減、乘、除等運算操縱，結果仍為空。

???? (4)NULL的處理使用NVL函數。

???? (5)比較時使用關鍵字用“is null”和“is not null”。

???? (6)空值不能被索引，所以查詢時有些符合條件的數據可能查不出來， count(*)中，用nvl(列名,0)處理后再查。

???? (7)排序時比其他數據都大（索引默許是降序排列，小→大）， 所以NULL值老是排在最后。
使用方法:
SQL> select 1 from dual where null=null; 沒有查到記載?
SQL> select 1 from dual where null=''; 沒有查到記載?
SQL> select 1 from dual where ''=''; 沒有查到記載?
SQL> select 1 from dual where null is null; 1 --------- 1?
SQL> select 1 from dual where nvl(null,0)=nvl(null,0); 1 --------- 1?
對空值做加、減、乘、除等運算操縱，結果仍為空。?
SQL> select 1+null from dual;?
SQL> select 1-null from dual;?
SQL> select 1*null from dual;?
SQL> select 1/null from dual; 查詢到一個記載. 注:這個記載就是SQL語句中的那個null 設置某些列為空值 update table1 set 列1=NULL where 列1 is not null;?
現有一個商品銷售表sale，表結構為：?

???? month char(6) --月份?

???? sellnumber(10,2) --月銷售金額?

???? create table sale (month char(6),sell number);?

???? insert into sale values('200001',1000);?

???? insert into sale values('200002',1100);?

???? insert into sale values('200003',1200);?

???? i nsert into sale(month) values('200009'); （注意：這條記載的sell值為空）?

???? commit; 共輸入4條記載
SQL> select * from sale where sell like '%';?
MONTH SELL?

???? ------ ---------?

???? 200001 1000?

???? 200002 1100?

???? 200003 1200?

???? 查詢到3記載. 結果說明:查詢結果說明此SQL語句查詢不出列值為NULL的字段 此時需對字段為NULL的情況另外處理。?
SQL> select * from sale where sell like '%' or sell is null;?
SQL> select * from sale where nvl(sell,0) like '%';?
MONTH SELL?

???? ------ ---------?

???? 200001 1000?

???? 200002 1100?

???? 200003 1200?

???? 200009?

???? 查詢到4記載. Oracle的空值就是這么的用法,我們最好熟悉它的約定,以防查出的結果不正確

???? 2.nvl2函數

???? ? ? nvl2(expre1,expre2,expre3)函數,如果第一個參數不為空,那么返回第二個參數,如果為空,返回第三個參數.參數1可所以任何數據類型,參數2和參數3也可所以任何數據類型,除了long類型之外.如果后兩個參數數據類型不一樣,數據庫將會把第三個參數的數據類型轉換為第二個參數的數據類型。

???? ? ? SQL> select ename,sal,comm,nvl2(comm,'sal+comm','sal') income from emp;

???? ? ? ENAME???????????? SAL?????? COMM INCOME
? ? ---------- ---------- ---------- --------
? ? SMITH???????????? 800??????????? sal
? ? ALLEN??????????? 1600??????? 300 sal+comm
? ? WARD???????????? 1250??????? 500 sal+comm
? ? JONES??????????? 2975??????????? sal
? ? MARTIN?????????? 1250?????? 1400 sal+comm
? ? BLAKE??????????? 2850??????????? sal
? ? CLARK??????????? 2450??????????? sal
? ? SCOTT??????????? 3000??????????? sal
? ? KING???????????? 5000??????????? sal
? ? TURNER?????????? 1500????????? 0 sal+comm

???? 3.nullif 函數
? ? nullif(expre1,expre2) 比較兩個參數,如果參數1和參數2相等,返回null.如果不相等,返回第一個參數,你可以為第一個參數指定空字符。

???? ? ? SQL> SELECT ENAME,SAL,COMM,NULLIF(SAL,COMM)RESULTS FROM EMP;

???? ? ? ENAME???????????? SAL?????? COMM??? RESULTS
? ? ---------- ---------- ---------- ----------
? ? SMITH???????????? 800??????? 800
? ? ALLEN??????????? 1600??????? 300?????? 1600
? ? WARD???????????? 1250??????? 500?????? 1250

???? ? ? 這里的Smith的結果返回為null,這里的nullif與case表達式差不多

???? 4.coalesce函數

???? ? ?? coalesce(expre1,expre2,expre3)

???? ? ? 參數1:如果第一參數不為空，返回第一個參數。
? ? 參數2:如果參數1為空，則返回第二個參數。
? ? 參數3:如果參數1和參賽2都為空，返回第三個參數。

???? ? ? 例子：
? ? 顯示雇員名字,如果獎金不為null,返回comm,如果comm為null,那么返回參數2,如果參數1和參數2都為null，那么返回數字10.

???? ? ? SQL> select ename,coalesce(comm,sal,10) income from emp;

???? ? ? ENAME????????? INCOME
? ? ---------- ----------
? ? SMITH???????????? 800
? ? ALLEN???????????? 300
? ? WARD????????????? 500
? ? JONES??????????? 2975
? ? MARTIN?????????? 1400
? ? SCOTT??????????? 3000
? ? KING???????????? 5000
? ? TURNER????????????? 0
? ?ADAMS??????????? 1100

???? 5.條件表達式: case when 函數

???? ? ? case expre when comparison_expre1 then return_expre1
? ? ? ?[when comparison_expre2 then return_expre2
? ? ? ?when comparison_expre3 then return_expre3
? ? ? ?else else_expres]
? ? end

???? ? ? 在所有表達式中:expre,comparison_expre,return_expre 這三個表達式數據類型要一樣,可以使用varchar,varchar2, char, nchar or nvarchar2.

???? ? ? 例子:

???? ? ? SQL> SELECT ENAME,SAL,JOB,

???? ? ? CASE JOB WHEN 'MANAGER' THEN SAL+100
? ? WHEN 'ANALYSIS' THEN SAL+200
? ? WHEN 'SALESMAN' THEN SAL+300
? ? ELSE SAL+400
? ? END "RESULTS"
? ? FROM EMP;

???? ? ? ENAME???????????? SAL JOB????????? RESULTS
? ? ---------- ---------- --------- ----------
? ? SMITH???????????? 800 CLERK?????????? 1200
? ? ALLEN??????????? 1600 SALESMAN??????? 1900
? ? WARD???????????? 1250 SALESMAN??????? 1550
? ? JONES??????????? 2975 MANAGER???????? 3075
? ? MARTIN?????????? 1250 SALESMAN??????? 1550

???? 6.decode函數

???? ? ? DECODE(col|expression ,search1,result1[,search2,result2][,default]).如果default值被省略了,那么當search值沒有與任何的math值匹配,將返回null值。

???? ? ? SQL> SELECT ENAME,JOB,SAL,DECODE(JOB,'MANAGER',SAL+100,?'SALESMAN',SAL+200,SAL) REVISED_SAL ? ? ? FROM EMP;

???? ? ? ENAME????? JOB????????????? SAL ? REVISED_SAL
? ? ---------- --------- ---------- -----------
? ? SMITH????? CLERK??????????? 800???????? 800
? ? ALLEN????? SALESMAN??????? 1600??????? 1800
? ? WARD?????? SALESMAN??????? 1250??????? 1450
? ? JONES????? MANAGER???????? 2975??????? 3075
? ? MARTIN???? SALESMAN??????? 1250??????? 1450
? ? BLAKE????? MANAGER???????? 2850??????? 2950
? ? CLARK????? MANAGER???????? 2450??????? 2550

???? ? ? 例子2:
? ? 盤算出30部門的各個員工的個人所得稅稅率
? ? SELECT ENAME,SAL,DECODE(TRUNC(SAL/1000,0),0,0.00,1,0.09,2,2.0,3.0) TAX_RATE?

???? ? ? FROM EMP?WHERE DEPTNO=30

???? ? ? ENAME???????????? SAL?? TAX_RATE
? ? ---------- ---------- ----------
? ? ALLEN??????????? 1600??????? .09
? ? WARD???????????? 1250??????? .09
? ? MARTIN?????????? 1250??????? .09
? ? BLAKE??????????? 2850????????? 2
? ? TURNER?????????? 1500??????? .09
? ? JAMES???????????? 950????????? 0

???? 7.wm_concat函數

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

????

文章結束給大家分享下程序員的一些笑話語錄： 祝大家在以后的日子里. 男生象Oracle般健壯; 女生象win7般漂亮; 桃花運象IE中毒般頻繁; 錢包如Gmail容量般壯大, 升職速度趕上微軟打補丁 ， 追女朋友像木馬一樣猖獗， 生活像重裝電腦后一樣幸福， 寫程序敲代碼和聊天一樣有**。

參數返回Oracle 常用函數:nvl/nullif/case when/wm_concat/replace