1、單行函數分類：一行記錄，傳回一行結果；
2、其他常用函數：空值處理函數
測試資料如下：
1）nvl(a,b)：a為空，就用b值填充。
- a是什麼資料類型，填充值b就必須是相同的資料類型。
SQL> select id,nvl(id,'0'),
2 name,nvl(name,'哈哈'),
3 comm,nvl(comm,0)
4 from test;
ID NVL(ID,'0') NAME NVL(NAME,'哈哈') COMM NVL(COMM,0)
-------------------- -------------------- -------------------- -------------------- --------- -----------
1 1 唐僧 唐僧 0
0 孫悟空 孫悟空 400.00 400
3 3 哈哈 200.00 200
4 4 劉備 劉備 300.00 300
0 哈哈 200.00 200
6 6 哈哈 0
6 rows selected 複制
2）nvl2(a,b,c)：a為空，傳回c，否則傳回b。
SQL> select nvl2(name,name,'無名氏'),sal,comm,sal+nvl2(comm,comm,0)"總工資" from test;
NVL2(NAME,NAME,'無名氏') SAL COMM 總工資
------------------------ --------- --------- ----------
唐僧 1200.00 1200
孫悟空 1500.00 400.00 1900
無名氏 1300.00 200.00 1500
劉備 1400.00 300.00 1700
無名氏 1200.00 200.00 1400
無名氏 1300.00 1300
6 rows selected 複制
3）nullif(a,b)：比較a和b是否相等，相等傳回空，不相等傳回a。
SQL> select nullif(2,2) from dual;
NULLIF(2,2)
-----------
SQL> select nullif(2,5) from dual;
NULLIF(2,5)
-----------
2 複制
4）coalesce(a,b,c,d…)：可以傳入n個參數，傳回第一個不為空的值。
-從左到右依次檢測a,b,c,d哪一個不為空。當遇到第一個不為空的值，就傳回該值。
SQL> select coalesce(null,null,3,null,4,5) from dual;
COALESCE(NULL,NULL,3,NULL,4,5)
------------------------------
3
SQL> select sal,comm,sal+coalesce(comm,0)"總工資" from test;
SAL COMM 總工資
--------- --------- ----------
1200.00 1200
1500.00 400.00 1900
1300.00 200.00 1500
1400.00 300.00 1700
1200.00 200.00 1400
1300.00 1300
6 rows selected 複制
3、case when函數
- 判斷工資等級：[1000,1300)及格；[1300,1500)中等；[1500,2000)優秀；
----------------------- 第一種使用方式
SQL> select sal,
2 case
3 when sal>=1500 and sal<2000 then '優秀'
4 when sal>=1300 and sal<1500 then '中等'
5 when sal>=1000 and sal<1300 then '及格'
6 end "工資等級"
7 from test;
SAL 工資等級
--------- --------
1200.00 及格
1500.00 優秀
1300.00 中等
1400.00 中等
1200.00 及格
1300.00 中等
6 rows selected
----------------------- 第二種使用方式
SQL> select sal,
2 case when sal>=1000 and sal<1300 then 1 else 0 end "及格",
3 case when sal>=1300 and sal<1500 then 1 else 0 end "中等",
4 case when sal>=1500 and sal<2000 then 1 else 0 end "優秀"
5 from test;
SAL 及格 中等 優秀
--------- ---------- ---------- ----------
1200.00 1 0 0
1500.00 0 0 1
1300.00 0 1 0
1400.00 0 1 0
1200.00 1 0 0
1300.00 0 1 0 複制
4、decode函數：常與sign()符号函數合起來使用。
- 文法：decode(value,if1,then1,if2,then2,if3,then3,…,else)
1）等值判斷
SQL> select
2 decode(name,null,'無名氏',name),
3 sal
4 from test;
DECODE(NAME,NULL,'無名氏',NAME SAL
---------------------------------------- ---------
唐僧 1200.00
孫悟空 1500.00
無名氏 1300.00
劉備 1400.00
無名氏 1200.00
無名氏 1300.00
6 rows selected
SQL> select
2 decode(name,null,'無名氏','唐僧','唐僧1','孫悟空','孫悟空1','劉備','劉備1'),
3 sal
4 from test;
DECODE(NAME,NULL,'無名氏','唐? SAL
------------------------------ ---------
唐僧1 1200.00
孫悟空1 1500.00
無名氏 1300.00
劉備1 1400.00
無名氏 1200.00
無名氏 1300.00
6 rows selected 複制
2）非等值判斷
- 講述這個案例之前，先來說明符号函數sign(x)的作用。
- 當x < 0，傳回結果是-1；
- 當x = 0，傳回結果是0；
- 當x > 0，傳回結果是1；
SQL> select sign(-5) from dual;
SIGN(-5)
----------
-1
SQL> select sign(0) from dual;
SIGN(0)
----------
0
SQL> select sign(5) from dual;
SIGN(5)
----------
1 複制
- 判斷工資等級：<1300)不及格；[1300,1500)中等；>=1500優秀；
SQL> select sal,
2 decode(sign(sal-1300),
3 -1,
4 '不及格',
5 0,
6 '中等',
7 1,
8 decode(sign(sal-1500),
9 -1,
10 '中等',
11 0,
12 '優秀',
13 1,
14 '優秀'))
15 from test;
SAL DECODE(SIGN(SAL-1300),-1,'不及
--------- ------------------------------
1200.00 不及格
1500.00 優秀
1300.00 中等
1400.00 中等
1200.00 不及格
1300.00 中等
6 rows selected 複制