--***************通用函數
--NVL處理NULL
--NVL(數字|列,默認值),如果顯示數字是NULL的話,則使用默認值
--NVL2(數字|列,默認值1,默認值2) 如果列不是NULL則返回結果1,如果為空返回2
--NULLIF(表達式1,表達2)比較表達式1,和表達2,的結果是否相等,如果相等返回NULL,如果不相等返回表達式1
--DECODE(列|值,判斷值1,顯示結果1,判斷值2,顯示結果2.............)
--CASE 列|數值 WHEN 表達式1 THEN 顯示結果1,else表達式n..end
--COALESCE (表達1,表達2,。。。。表達n)
--使用NVL處理NULL
--要求查詢員工的編號,姓名,職位,入職日期和年薪(月薪+comm)*12
--驗證NVL
SELECT NVL(NULL,0),NVL(3,0) FROM dual;
結果:0,3
SELECT e.empno,e.ename,e.job,e.hiredate, (e.sal+NVL(e.comm,0))*12 年薪 FROM emp e;
--使用NVL2函數
SELECT e.empno,e.ename,e.job,e.hiredate, (e.sal+NVL2(e.comm,comm,0))*12 年薪 from emp e;
SELECT e.empno,e.ename,e.job,e.hiredate,e.comm, NVL2(e.comm,e.sal+e.comm,e.sal)*12 年薪 from emp e;
--NULLIF函數比較2個表達式,如果相等返回NULL,不相等返回表達式1
SELECT NULLIF(1,1),NULLIF(1,2) FROM dual;
結果:null,1
--比較名字長度和職位長度
SELECT e.empno,e.ename,e.job,LENGTH(e.ename),LENGTH(e.job), NULLIF(LENGTH(e.ename),LENGTH(e.job)) 長度是否相同 FROM emp e;
--decode使用DECODE要全部判斷,如果沒有判斷的內容就會顯示NULL
SELECT e.ename,e.sal, DECODE(job,'CLERK','業務員', 'SALESMAN','銷售員', 'MANAGER','經理', 'ANALYST','分析師', 'PRESIDENT','總裁') 職位 FROM emp e;
--CASE表達式
--顯示每個員工的姓名,工資,職位,同時顯示新的工資(新工資為業務員增長10%,銷售員增加20%,經理增長30%,其他職位的人增長50%
SELECT e.ename,e.sal,e.job, CASE e.job WHEN 'CLERK' THEN e.sal*1.1 WHEN 'SALESMAN' THEN e.sal*1.2 WHEN 'SALSEMAN' THEN e.sal*1.3 ELSE e.sal*1.5 END 新工資 FROM emp e;
---coalesce (表達1,表達2,。。。。表達n)依次判斷,如果表達式1為NULL,則顯示表達式2,如果表達式2是NULL,則顯示表達式3.。。。。。
SELECT e.ename,e.sal,e.comm, COALESCE(e.comm,100,2000),COALESCE(e.comm,NULL,NULL) FROM emp e;