Oracle資料庫之六 單行函數

六、單行函數

6.1、認識單行函數

​ 函數就是和 Java 語言之中的方法的功能是一樣的，都是為了完成某些特定操作的功能支持，而在 Oracle 資料庫裡面也包含了大量的單行函數，這些函數掌握了以後，可以方便的幫助進行資料庫的相關開發。

​ 對開發者而言，最為重要的就是 SQL 語法和單行函數，可是 Oracle 中的單行函數的數量是非常多的。本章只講解使用，後面會講解如何去開發用戶自己的函數（PL/SQL 編程）

6.1.1 單行函數語法

​ funcation_name(列|表達式[,參數1,參數2,])

​ 函數名稱(列 | 表達式 | 數值)，而且在Oracle 書中只講解基本的一些單行函數。

6.1.2 單行函數分類

• 字元函數：接收數據返回具體的字元信息
• 數值函數：對數字進行處理，如：四捨五入
• 日期函數：直接對日期進行相關操作
• 轉換函數：日期、字元、數字之間可以完成互相轉換功能
• 通用函數：Oracle 子句提供的有特色的函數

6.2、字元函數

• 一定是以字元數據為主（字元串）

• 在這裡有一個問題就會出現，在 Oracle 裡面所有的驗證操作必須存在在完整的 SQL 語句之中，所以如果現在只是進行功能驗證，使用的是一張具體的表。

scott 用戶下：
SELECT UPPER('li xing hua') 
FORM emp;

• 這個時候發現結果被重覆顯示了14行，所以現在函數的功能的確是進行了驗證，但是代價太高。如果使用 DISTINCT 可以消除，那麼如果 emp 表中的數據很多呢？那麼中間處理的數據量就會很大，所以現在就希望有一張表可以幫助用戶進行驗證，而在 Oracle 里就提供了一個 dual 的數據表（是虛擬表）。

範例：驗證 UPPER() 和 LOWER() 函數

SELECT UPPER('li xing hua'),LOWER('MLDN')
FORM dual;

範例：現在查詢出雇員姓名是“smith”的完整信息，但是由於失誤，沒有考慮到大小寫問題，此時可以使用UPPER() 函數將全部內容變為大寫。

SELECT * 
FORM emp
WHERE ename = UPPER('smith');

範例：查詢所有雇員姓名，要求每個雇員的姓名以首字母大寫的形式出現

SELECT ename 原始姓名,INITCAP(ename) 姓名開頭首字母大寫
FORM emp;

範例：查詢所有雇員信息，要求將雇員姓名中所有的字母“A”替換成“_”

SELECT ename,REPLACE(ename,'A','_')
FORM emp;

範例：查出姓名長度是5的所有雇員信息

SELECT * 
FORM emp
WHERE LENGTH(ename) = 5;

範例：查詢出雇員姓名前三個字母是“JAM”的雇員信息

• 那麼現在要想辦法截取出前三個字元，截取操作就一定要使用 SUBSTR() 函數，要註意的是，SUBSTR() 函數有兩種形式：
  • 從指定位置截取到結尾：SUBSTR(列 | 字元串,截取開始點)
  • 截取部分字元串：SUBSTR(列 | 字元串,截取開始點,截取個數)

SELECT * 
FORM emp
WHERE SUBSTR(ename,1,3) = 'JAM'; 

或者：

SELECT * 
FORM emp
WHERE SUBSTR(ename,0,3) = 'JAM';

• 註意：在 Oracle 中，下標都是從1開始，如果設置為0，也會自動將其轉換為1 。

範例： 查詢所有10部門雇員姓名，但不顯示每個雇員姓名的前三個字母

SELECT ename 原姓名,SUBSTR(ename,3) 截取之後的姓名 
FORM emp
WHERE deptno = 10;

範例：顯示每個雇員姓名及姓名的後三個字母

• 要想截取每個姓名之中的後三個，首先要解決的問題是開始點，從一個指定的開始點一直截取到結尾，可是每個雇員的姓名長度是不同的，那麼開始點如何確定呢？

  • 實現一：使用傳統做法，先求得姓名長度，然後減2確定開始點

  SELECT ename 原始姓名,SUBSTR(ename,LENGTH(ename) - 2) 截取之後的姓名  
  FORM emp
  WHERE deptno = 10;

  • 實現二：除了此類做法之外，也可以設置開始點為負數

  SELECT ename 原始姓名,SUBSTR(ename,-3) 截取之後的姓名  
  FORM emp
  WHERE deptno = 10;

• 現在很明顯使用第二種最方便，這個也屬於 Oracle 的特色，不過需要註意的是：Java 語言的字元串下標還是從0開始，而且 Java 里的 substring() 方法是不能設置負數的。

• 面試題：請問 Oracle 中的 SUBSTR() 函數的下標開始點是從0還是1開始？

  ​ 答：可以設置為0，也可以設置為1，即使使用了0，那麼最終的結果也會將其定義為1。

範例：返回指定字元的 ASCII 碼

SELECT ASCII('A'),ASCII('L')
FORM dual;

範例：驗證 CHR() 函數，將ASCII 碼變回字元

SELECT CHR(100) FORM dual;

範例：去掉字元串左邊空格 - LTRIM() ，去掉右邊空格 - RTRIM()

SELECT '     MLDN    LiXingHua     ' 原始字元串，LTRIM('    ') 去掉左空格
FORM dual;

SELECT '     MLDN    LiXingHua     ' 原始字元串，RTRIM('    ') 去掉右空格
FORM dual;

範例：去掉左右空格

SELECT '     MLDN    LiXingHua     ' 原始字元串，TRIM('    ') 去掉左右空格
FORM dual;

• 不管如何取消空格，中間的空格可是無法消除掉的。

範例：字元串左填充 - LPAD() ，字元串右填充 - RPAD()

SELECT LPAD('MLDN',10,'*') LPAD函數使用,RPAD('MLDN',10,'*') RPAD函數使用,
LPAD(RPAD('MLDN',10,'*'),16,'*') 組合使用 
FORM dual;

範例：字元串查找 - INSTR()

SELECT 
    INSTR('MLDN Java','MLDN') 查找得到,
    INSTR('MLDN Java','Java') 查找得到,
    INSTR('MLDN Java','JAVA') 查找不到
FORM dual;

• 這個函數和 Java 中的 indexOf() 方法功能是相同的。
• 小結：
  • 字元函數的主要功能是進行字元串數據的操作

6.3、數值函數

範例：驗證 ROUND() 函數的使用

SELECT 
    ROUND(789.652) 不保留小數,
    ROUND(789.652,2) 不保留小數,
    ROUND(789.652,-1) 不保留小數,
FROM dual;

範例：列出每個雇員的一些基本信息和日工資情況

• 對於日工資的計算可以採用30天為基礎，肯定會有小數，就保留2位

SELECT empno,ename,job,hiredate,sal,ROUND(sal/30,2) 日薪金
FROM emp;

• ROUND() 函數的功能是小數進位，而 TRUNC() 的功能是不進位。

範例：驗證 TRUNC() 函數的使用

SELECT 
    TRUNC(789.652) 截取小數，
    TRUNC(789.652,2) 截取2位小數，
    TRUNC(789.652,-2) 取整
FROM dual;

範例：MOD() 函數驗證，模就是取餘

SELECT MOD(10,3) FROM dual;

6.4、日期函數

• 如果現在要想進行日期的操作，那一定會存在一個前提，必須知道當前日期
• 取得當前的系統時間，可以直接利用 SYSDATE 偽列取得當前日期時間。所謂偽列指的是不是表中的列，但是有可以直接使用的列。

SELECT empno,ename,SYSDATE FROM emp;

SELECT SYSDATE FROM dual;

• 在預設情況下顯示的內容，只包含了年、月、日三個內容，如果要顯示更多內容，就必須修改語言環境。
• 修改日期顯示格式：
  • 運行 - sqlplus/nolog - conn c##scott/tiger
  • 輸入以下代碼

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';

SELECT SYSDATE FROM dual;

• 更改的日期顯示格式在關閉視窗重新打開後就會還原了

• 除了取得系統時間的操作之外，在 Oracle 中也有如下的三個日期操作公式：
  • 日期 - 數字 = 日期， 表示若幹天前的日期
  • 日期 + 數字 = 日期， 表示若幹天後的日期
  • 日期 - 日期 = 數字（天數），表示兩個日期的天數的間隔

• 可是絕對不會存在 “日期 + 日期” 的計算，下麵為其驗證。

SELECT 
    SYSDATE+3 三天之後的日期,
    SYSDATE-3 三天之前的日期
FROM dual;

範例：查詢出每個雇員的到今天為止的雇佣天數，以及十天前每天雇員的雇佣天數

SELECT empno 雇員編號, ename 雇員姓名, 
    SYSDATE-hiredate 雇佣天數, 
    (SYSDATE-10)-hiredate 10天前雇佣天數
FROM emp;

• 結果有小數點，可以用 TRUNC() 截取小數點

SELECT empno 雇員編號, ename 雇員姓名, 
    TRUNC(SYSDATE-hiredate) 雇佣天數, 
    TRUNC((SYSDATE-10)-hiredate) 10天前雇佣天數
FROM emp;

• 以上只是針對於當前時間的操作，對於 Oracle 而言，也提供相應的日期函數，之所以使用日期函數，主要是避免閏年問題，或者是一個月有28,29,30,31天的問題，通過日期函數的計算取得的日期時間是最準確的。

範例：驗證 ADD_MONTHS() 函數

• 使用 ADD_MONTHS() 函數的主要功能是在一個指定日期上增加若幹個月之後求得的新日期。

SELECT SYSDATE,
    ADD_MONTHS(SYSDATE,3) 三個月之後的日期，
    ADD_MONTHS(SYSDATE,-3) 三個月之前的日期，
    ADD_MONTHS(SYSDATE,60) 六十個月之後的日期，
FROM dual;

範例：要求顯示所有雇員在被雇佣三個月之後的日期

SELECT empno,ename,job,sal,hiredate,ADD_MONTHS(hiredate,3)
FROM emp; 

範例：驗證 NEXT_DAY() 函數

• 主要是求出下一個指定的日期數，如果說現在的日期是‘2019年08月30日 星期五’ ，那麼如果現在想要知道下一個‘星期一’ 或是 ‘星期日’ 的具體日期，則可以使用 NEXT_DAY() 函數。

SELECT 
    SYSDATE,
    NEXT_DAY(SYSDATE,'星期日') 下一個星期日,
    NEXT_DAY(SYSDATE,'星期一') 下一個星期一
FROM dual;

範例：LAST_DAY() 函數驗證，求當月的最後一天

SELECT SYSDATE,
    LAST_DAY(SYSDATE)
FROM dual;

範例：查詢所有是在其雇佣所在月的倒數第三天被公司雇佣的完整雇佣信息

• 每一位雇員都有自己的雇佣日期，那麼現在要查詢出，你雇佣日期所在月倒數第三天雇佣的人，首先需要知道的是每個雇員雇佣所在月的最後一天。

SELECT empno,ename,job,hiredate,LAST_DAY(hiredate) 
FROM emp
WHERE LAST_DAY(hiredate)-2=hiredate;

範例：MONTHS_BETWEEN() 函數的驗證：查詢出每個雇員的編號、姓名、雇員傭日期，雇佣的月數及年份

• MONTHS_BETWEEN() 函數的功能是取得兩個日期時間的月份間隔

SELECT 
    empno 雇員編號, ename 雇員姓名, hiredate 雇佣日期,
    TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) 雇佣總月數,
    TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 雇佣總年數
FROM emp;

範例：查詢出每個雇員的編號、姓名、雇佣日期、已雇佣的年數、月數、天數

• 對於本程式而言，一定是分步計算，而且有一定的難度，因為要操作的是準確性。
• 例如，今天的日期是 2019-09-01日，而 BLAKE 的雇佣日期是 1981-05-01，那麼這位雇員到今天為止被雇佣了38年、4個月、0天。
• 步驟一：求出年，年只需要依靠月就可以計算出來。

SELECT 
    empno 雇員編號, ename 雇員姓名, hiredate 雇佣日期,
    TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 雇佣總年數
FROM emp;

• 步驟二：求出月，計算年的時候存在小數，那麼這裡面的數據就是月，只需要求模即可得到。

SELECT 
    empno 雇員編號, ename 雇員姓名, hiredate 雇佣日期,
    TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 已雇佣年數,
    TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) 已雇佣月數
FROM emp;

• 步驟三：是針對於天的計算，因為現在已經計算出了年和月，所以天應該刨去年和月的數字信息。那麼現在的問題是，如果要想計算天數唯一知道的公式就是 “日期1 - 日期2”，那麼日期1 一定使用的是 SYSDATE ，而日期2（應該去掉年和月），可以利用 ADD_MONTHS() 函數實現此功能。

SELECT 
    empno 雇員編號, ename 雇員姓名, hiredate 雇佣日期,
    TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 已雇佣年數,
    TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) 已雇佣月數,
    TRUNC(SYSDATE - ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) 已雇佣天數
FROM emp;

範例：EXTRACT() 函數

• 在 Oracle 9i 之後增加了一個 EXTRACT() 函數，此函數的主要功能是可以從一個日期時間（DATE）或者是時間間隔（INTERVAL）中截取出特定的部分，此函數使用語法如下：

EXTRACT
    ([YEAR | MONTH | DAY | HOUR | MINUTE | SECOND]
    | [TIMEZONE_HOUR | TIMEZONE_MINUTE]
    | [TIMEZONE_REGION | TIMEZONE_ABBR]
FROM 
     [日期(date_value) | 時間間隔(interval_value)]);

範例：從日期時間之中取出年、月、日數據

SELECT 
    EXTRACT(YEAR FROM DATE '2001-09-19') years,
    EXTRACT(MONTH FROM DATE '2001-09-19') months,
    EXTRACT(DAY FROM DATE '2001-09-19') days
FROM dual;

• 現在是通過一個日期的字元串完成，那麼也可以利用當前日期完成。SYSDATE、SYSTIMESTAMP（時間戳）。

SELECT SYSDATE,SYSTIMESTAMP
FROM dual;

範例：從時間戳之中取出年、月、日、時、分、秒

SELECT
    EXTRACT(YEAR FROM SYSTIMESTAMP) years,
    EXTRACT(MONTH FROM SYSTIMESTAMP) months,
    EXTRACT(DAY FROM SYSTIMESTAMP) days,
    EXTRACT(HOUR FROM SYSTIMESTAMP) hours,
    EXTRACT(MINUTE FROM SYSTIMESTAMP) minutes,
    EXTRACT(SECOND FROM SYSTIMESTAMP) seconds
FROM dual;

• 除了以上功能之外，主要功能是取得時間間隔，但是在此處需要使用到一個轉換函數：TO_TIMESTAMP() ，可以將字元串變為時間戳，而且此時的內容需要使用到部分子查詢功能，所以此處只為做個演示。

範例：取得兩個日期之間的間隔

SELECT 
    EXTRACT(DAY FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
        - TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days
FROM dual;

範例：取得兩個日期時間之間間隔的天、時、分、秒

SELECT 
    EXTRACT(DAY FROM datetime_one - datetime_two) days,
    EXTRACT(HOUR FROM datetime_one - datetime_two) hours,
    EXTRACT(MINUTE FROM datetime_one - datetime_two) minutes,
    EXTRACT(SECOND FROM datetime_one - datetime_two) seconds
FROM (
    SELECT TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') datetime_one,
        TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss') datetime_two
    FROM dual);

• 如果覺得比較麻煩，也可以不使用子查詢，按原來的方法寫代碼就如下：

SELECT 
    EXTRACT(DAY FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
        - TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days,
    EXTRACT(HOUR FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
        - TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) hours,
    EXTRACT(MINUTE FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
        - TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) minutes,
    EXTRACT(SECOND FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
        - TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) seconds
FROM dual;

• 這樣看來，第二種方式就是最差的一種方式了，不推薦使用。

6.5、轉換函數

• 在資料庫之中主要使用的數據類型：字元、數字、日期（時間戳），那麼這三種數據類型之間就需要實現轉換操作，這就屬於轉換函數的功能。

6.5.1 TO_CHAR() 函數

• 在預設的情況下，如果查詢一個日期，則日期預設的顯示格式為“31-1月-12”，而這樣的日期顯示效果肯定不如常見的“2012-01-31” 讓人看起來習慣，所以此時就可以通過TO_CHAR() 函數對這個顯示的日期數據進行格式化（格式化之後的數據是字元串），但是如果要完成這種格式化，則首先要熟悉一下格式化日期的替代標記。

日期格式化標記：

• 在 TO_CHAR() 函數裡面，需要兩個參數：日期數據，轉換格式

範例：格式化日期時間

SELECT SYSDATE 當前系統時間,
    TO_CHAR(SYSDATE,'YYYY-MM-DD') 格式化日期,
    TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') 格式化日期時間,
    TO_CHAR(SYSDATE,'FMYYYY-MM-DD HH24:MI:SS') 去掉前導0的時間
FROM dual;

• 註意：在開發中一定別取消前導0

範例：使用其他方式格式化年、月、日

• 除了使用標記（是一種習慣，java.text.SimpleDateFormat），也可以使用單詞表示

SELECT SYSDATE 當前系統時間,
    TO_CHAR(SYSDATE,'YEAR-MONTH-DAY') 格式化日期
FROM dual;

或者：

SELECT SYSDATE 當前系統時間,
    TO_CHAR(SYSDATE,'YEAR-MONTH-DY') 格式化日期
FROM dual;

範例：查詢出所有在每年2月份雇佣的雇員信息

SELECT * 
FROM emp 
WHERE TO_CHAR(hiredate,'MM') = 2;

或者：

SELECT * 
FROM emp 
WHERE TO_CHAR(hiredate,'MM') = '02';

範例：將每個雇員的雇佣日期進行格式化顯示，要求所有的雇佣日期可以按照“年-月-日”的形式顯示，也可將雇佣的年、月、日拆開分別顯示

SELECT empno,ename,job,hiredate,
    TO_CHAR(hiredate,'YYYY-MM-DD') 格式化雇佣日期,
    TO_CHAR(hiredate,'YYYY') 年,
    TO_CHAR(hiredate,'MM') 月,
    TO_CHAR(hiredate,'DD') 日
FROM emp;

• TO_CHAR() 函數的最為重要的功能是可以將數字格式化，例如：2389042809，如果是有經驗的財務人員會按照三位一分：2,389,042,809 ，而要想按照此方法處理數字，就必須格式化。

數字格式化標記

範例：格式化數字顯示

SELECT
    TO_CHAR(987654321.789,'999,999,999,999.9999') 格式化數字,
    TO_CHAR(987654321.789,'000,000,000,000.0000') 格式化數字
FROM dual;

• 除了直接對數字格式化，也可以進行貨幣的顯示

SELECT
    TO_CHAR(987654321.789,'L999,999,999,999.9999') 顯示貨幣,
    TO_CHAR(987654321.789,'$999,999,999,999.9999') 顯示美元
FROM dual;

• 在開發之中，TO_CHAR() 函數的作用還是非常明顯的！建議掌握！

6.5.2 TO_DATE() 函數

• 這個函數主要是將字元串變為日期型數據，而改變的過程裡面依然需要之前 TO_CHAR() 函數出現的相關標記。

範例：轉換時間顯示

SELECT 
    TO_DATE('1979-09-19','YYYY-MM-DD')
FROM dual;

• 在之前講解日期函數時使用了一個 TO_TIMESTAMP() 函數，這個函數是將字元串變為時間戳。

範例：時間戳轉換

SELECT
    TO_TIMESTAMP('1981-09-27 18:07:10','YYYY-MM-DD HH24:MI:SS') datetime
FROM dual;

6.5.3 TO_NUMBER() 函數

• 作用是將字元串變為數字

範例：將字元串變為數字

SELECT
    TO_NUMBER('09') + TO_NUMBER('19') 加法操作,
    TO_NUMBER('09') * TO_NUMBER('19') 乘法操作
FROM dual;

• 但是在之前強調過，Oracle 裡面支持數據類型的自動轉型操作，上面的代碼也可寫為

SELECT '09' + '19' 加法操作,
    '09' * '19' 乘法操作
FROM dual;

6.6、通用函數

• 這些函數是 Oracle 資料庫的特色，對於這些函數瞭解有一定的好處。

• 對於通用函數而言，只有兩個核心函數：NVL() , DECODE()

6.6.1 使用 NVL() 函數處理 null

• 在資料庫之中，null 是無法進行計算的，即，在一個數學計算之中如果存在了 null，則最後的結果也肯定是 null

範例：查詢出每個雇員的編號、姓名、職位、雇佣日期、年薪

• 對於年薪最準確的做法是應該計算 “sal + comm”，可是這個時候 comm 列上是存在了 null 數據的

SELECT empno,ename,job,hiredate,(sal+comm)*12 年薪,sal,comm
FROM emp;

• 因為 comm 上的內容有的是 null，而現在發現，只要是 comm 為 null 的計算，最終的結果就是 null ，所以在這種情況下需要針對於 null 進行處理，肯定將 null 變為0才合適

範例：驗證 NVL()

SELECT NVL(null,0),NVL(3,0) 
FROM dual;

• 這個時候發現如果為 null，那麼就將其變為了0，如果不是 null，就繼續使用指定的數值

SELECT empno,ename,job,hiredate,(sal+NVL(comm,0))*12 年薪,sal,comm
FROM emp;

6.6.2 NVL2() 函數

• NVL2() 函數是在 Oracle 9i 之後增加的一個新的功能函數，相比較 NVL() 函數，NVL2() 函數可以同時對為 null 或不為 null 進行分別判斷並返回不同的結果

範例：查詢每個雇員的編號、姓名、年薪（sal + comm）、基本工資、獎金

SELECT empno,ename,job,hiredate,NVL2(comm,sal+comm,sal),sal,comm
FROM emp;

6.6.3 NULLIF() 函數

• NULLIF(表達式一,表達式二) 函數的主要功能是判斷兩個表達式的結果是否相等，如果相等則返回 NULL ，不相等則返回表達式一

範例：驗證 NULLIF() 函數

SELECT NULLIF(1,1),NULLIF(1,2)
FROM dual;

範例：查詢雇員編號、姓名、職位，比較姓名和職位的長度

SELECT empno,ename,job,LENGTH(ename),LENGTH(job),NULLIF(LENGTH(ename),LENGTH(job)) nullif
FROM emp;

6.6.4 DECODE() 函數

• DECODE() 函數是 Oracle 中最有特色的一個函數，DECODE() 函數類似於程式中的 if...else if...else ，但是判斷的內容都是一個具體的值，語法如下：

  DECODE(列|表達式, 值1, 輸出結果, 值2, 輸出結果, ..., 預設值)

範例：測試DECODE() 函數

SELECT 
    DECODE(2,1,'內容為一',2,'內容為二'),
    DECODE(2,1,'內容為一','沒有條件滿足')
FROM dual;

範例：現在雇員表中的工作有以下幾種：CLERK:業務員, SALESMAN:銷售人員, MANAGER:經理, ANALYST:分析員, PRESIDENT:總裁 ，要求查詢雇員的姓名、職位、基本工資等信息，但是要求將所有的職位信息都替換為中文顯示。

SELECT ename,sal,
DECODE(job,
    'CLERK','業務員',
    'SALESMAN','銷售人員',
    'MANAGER','經理',
    'ANALYST','分析員',
    'PRESIDENT','總裁') job
FROM emp;

• 但是需要註意的是，如果使用 DECODE() 函數判斷，那麼所有的內容都要判斷，如果只判斷部分內容，其它內容就會顯示 null

6.6.5 CASE 表達式

• CASE 表達式是在 Oracle 9i 引入的，功能與DECODE() 有些類似，都是執行多條件判斷。不過嚴格來講，CASE表達式本身並不屬於一種函數的範疇，它的主要功能是針對於給定的列或者欄位進行依次判斷，在 WHERE 中編寫判斷語句，而在 THEN 中編寫處理語句，最後如果都不滿足則使用 ELSE 進行處理。

範例：顯示每個雇員的工資、姓名、職位，同時顯示新的工資（新的工資標準：辦事員增長10%，銷售人員增長20%，經理增長30%，其他職位的人增長50%）

SELECT ename,sal,
    CASE job WHEN 'CLERK' THEN sal * 1.1
        WHEN 'SALESMAN' THEN sal * 1.2
        WHEN 'MANAGER' THEN sal * 1.3
    ELSE sal * 1.5
    END 新工資
FROM emp;

6.6.6 COALESCE() 函數

• COALESCE(表達式1, 表達式2, 表達式3,...表達式n) 函數的主要功能是對 null 進行操作，採用依次判斷表達式的方式完成，如果表達式1為 null，則顯示表達式2的內容，如果表達式2的內容為 null，則顯示表達式3的內容，依次類推，判斷到最後如果還是null，則最終的顯示結果就是 null 。

範例：驗證 COALESCE() 函數

SELECT ename,sal,comm,COALESCE(comm,100,2000),
    COALESCE(comm,null,null)
FROM emp;

• 小結：
  • 這些通用函數都具備一些邏輯性的操作在裡面，在以後進行程式編寫時還是會使用到的。
  • NVL() 和 DECODE() 是通用函數的基礎，其他函數都在此函數之上進行功能擴充。

說明：本學習資料是根據李興華的Oracle開發實戰經典整理