今天一直在研究參數(shù)返回之類的問題,上午正好有機會和大家討論一下.
???? 1.nvl函數(shù)
????
? ? nvl函數(shù)將一個null值轉(zhuǎn)換為一個實際的值,數(shù)據(jù)類型可所以日期,數(shù)字,字符,數(shù)據(jù)類型必須匹配:如:
? ? ? ? nvl(commision,0)
???? ? ? ? ? nvl(hiredate,'01-JAN-87')
???? ? ? ? ? nvl(job_id,'no manager')
???? ? ? ? ? nvl(to_char(job_id),'no manager')
???? ? ? nvl可以轉(zhuǎn)換任何數(shù)據(jù)類型,但是轉(zhuǎn)換的數(shù)據(jù)類型返回值必須是nvl(expr1,expr2)第一個參數(shù)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的值通過查詢?nèi)〉媚硞€字段的總計值,如果這個值位null將給出一個預(yù)設(shè)的默許值
????
返回值類型 :
字符型、日期型、日期時間型、數(shù)值型、貨幣型、邏輯型或 null 值?
說明:
在不支持 null 值或 null 值可有可無的情況下,可以使用 NVL( ) 來移去盤算或操縱中的 null 值. select nvl(a.name,'空值') as name from student a join school b on a.ID=b.ID 注意:兩個參數(shù)得類型要匹配 問:什么是NULL？ 答:在我們不知道具體有什么數(shù)據(jù)的時候,也即未知,可以用NULL, 我們稱它為空,ORACLE中,含有空值的表列長度為零。
ORACLE答應(yīng)任何一種數(shù)據(jù)類型的字段為空，除了以下兩種情況：
???? 1)主鍵字段（primary key），
???? 2)定義時已經(jīng)加了NOT NULL限制條件的字段
???? 說明:
???? (1)等價于沒有任何值、是未知數(shù)。
???? (2)NULL與0、空字符串、空格都不同。
???? (3)對空值做加、減、乘、除等運算操縱，結(jié)果仍為空。
???? (4)NULL的處理使用NVL函數(shù)。
???? (5)比較時使用關(guān)鍵字用“is null”和“is not null”。
???? (6)空值不能被索引，所以查詢時有些符合條件的數(shù)據(jù)可能查不出來， count(*)中，用nvl(列名,0)處理后再查。
????
(7)排序時比其他數(shù)據(jù)都大（索引默許是降序排列，小→大）， 所以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?
對空值做加、減、乘、除等運算操縱，結(jié)果仍為空。?
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 設(shè)置某些列為空值 update table1 set 列1=NULL where 列1 is not null;?
現(xiàn)有一個商品銷售表sale，表結(jié)構(gòu)為：?
???? 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記載. 結(jié)果說明:查詢結(jié)果說明此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的空值就是這么的用法,我們最好熟悉它的約定,以防查出的結(jié)果不正確
???? 2.nvl2函數(shù)
???? ? ? nvl2(expre1,expre2,expre3)函數(shù),如果第一個參數(shù)不為空,那么返回第二個參數(shù),如果為空,返回第三個參數(shù).參數(shù)1可所以任何數(shù)據(jù)類型,參數(shù)2和參數(shù)3也可所以任何數(shù)據(jù)類型,除了long類型之外.如果后兩個參數(shù)數(shù)據(jù)類型不一樣,數(shù)據(jù)庫將會把第三個參數(shù)的數(shù)據(jù)類型轉(zhuǎn)換為第二個參數(shù)的數(shù)據(jù)類型。
???? ? ? 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 函數(shù)
? ? nullif(expre1,expre2) 比較兩個參數(shù),如果參數(shù)1和參數(shù)2相等,返回null.如果不相等,返回第一個參數(shù),你可以為第一個參數(shù)指定空字符。
???? ? ? 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的結(jié)果返回為null,這里的nullif與case表達(dá)式差不多
???? 4.coalesce函數(shù)
???? ? ?? coalesce(expre1,expre2,expre3)
????
? ? 參數(shù)1:如果第一參數(shù)不為空，返回第一個參數(shù)。
? ? 參數(shù)2:如果參數(shù)1為空，則返回第二個參數(shù)。
? ? 參數(shù)3:如果參數(shù)1和參賽2都為空，返回第三個參數(shù)。
????
? ? 例子：
? ? 顯示雇員名字,如果獎金不為null,返回comm,如果comm為null,那么返回參數(shù)2,如果參數(shù)1和參數(shù)2都為null，那么返回數(shù)字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.條件表達(dá)式: case when 函數(shù)
????
? ? case expre when comparison_expre1 then return_expre1
? ? ? ?[when comparison_expre2 then return_expre2
? ? ? ?when comparison_expre3 then return_expre3
? ? ? ?else else_expres]
? ? end
???? ? ? 在所有表達(dá)式中:expre,comparison_expre,return_expre 這三個表達(dá)式數(shù)據(jù)類型要一樣,可以使用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函數(shù)
???? ? ? DECODE(col|expression ,search1,result1[,search2,result2][,default]).如果default值被省略了,那么當(dāng)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函數(shù)
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
文章結(jié)束給大家分享下程序員的一些笑話語錄： 祝大家在以后的日子里. 男生象Oracle般健壯; 女生象win7般漂亮; 桃花運象IE中毒般頻繁; 錢包如Gmail容量般壯大, 升職速度趕上微軟打補丁 ， 追女朋友像木馬一樣猖獗， 生活像重裝電腦后一樣幸福， 寫程序敲代碼和聊天一樣有**。
參數(shù)返回Oracle 常用函數(shù):nvl/nullif/case when/wm_concat/replace
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
