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
