.Net程式員學用Oracle系列(11)：系統函數（下）

1、 " 聚合函數 " 1.1、 "COUNT 函數" 1.2、 "SUM 函數" 1.3、 "MAX 函數" 1.4、 "MIN 函數" 1.5、 "AVG 函數" 2、 " ROWNUM 函數 " 2.1、 "ROWNUM 函數簡介" 2.2、 "利用 ROWNUM 函數實現分頁功能" 3、 " ...

1、聚合函數
- 1.1、COUNT 函數
- 1.2、SUM 函數
- 1.3、MAX 函數
- 1.4、MIN 函數
- 1.5、AVG 函數
2、ROWNUM 函數
- 2.1、ROWNUM 函數簡介
- 2.2、利用 ROWNUM 函數實現分頁功能
3、高級函數
- 3.1、高級函數簡介
- 3.2、語法說明及案例
4、總結

1、聚合函數

常見的聚合函數有 COUNT、SUM、MAX、MIN、AVG 共 5 個，它們都可以對一組值執行聚合計算，並返回單個值。聚合函數經常與 SELECT 語句的 GROUP BY 子句一起使用。預設所有聚合函數都會忽略 NULL 值，其中 COUNT 函數稍有不同，本人覺得 SQL 的這個設計還是蠻貼心的，因為這會幫開發者規避掉很多繁瑣的細節問題。

因為 COUNT 函數返回值是數據集的行數，與統計欄位的值大小無關，所以也可以讓 COUNT 函數統計所有行，即不忽略 NULL 值。如果需要 COUNT 函數統計欄位值為 NULL 的行，只需要將真實的欄位名換成 * 或者是一個具體的常量或變數，如：1、ROWNUM 等。實際開發中一般沒人會用變數（不易理解），本人推薦一律用常量（一般來說比用 * 要高效）。

1.1、COUNT 函數

語法：

SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions];

或

SELECT expression1, expression2, ... expression_n,
       COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

單獨使用：如要統計研發一部的人數。示例：

SELECT COUNT(t.staff_id) count_staff FROM demo.t_staff t WHERE t.dept_code='010101';

結合 GROUP BY 使用：如要統計開發部下各三級部門的人數。示例：

SELECT t.dept_code,COUNT(t.staff_id) count_staff 
FROM demo.t_staff t
WHERE INSTR(t.dept_code,'0101')=1 
GROUP BY t.dept_code;

1.2、SUM 函數

語法：

SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];

或

SELECT expression1, expression2, ... expression_n,
       SUM(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

單獨使用：如要統計研發一部的固定工資之和。示例：

SELECT SUM(v.fixed_salary) sum_salary FROM demo.v_staff v WHERE v.dept_code='010101';

結合 GROUP BY 使用：如要統計開發部下各三級部門的固定工資之和。示例：

SELECT v.dept_code,SUM(v.fixed_salary) sum_salary 
FROM demo.v_staff v
WHERE INSTR(v.dept_code,'0101')=1 
GROUP BY v.dept_code;

1.3、MAX 函數

語法：

SELECT MAX(aggregate_expression)
FROM tables
[WHERE conditions];

或

SELECT expression1, expression2, ... expression_n,
       MAX(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

單獨使用：如要統計研發一部的最高工資。示例：

SELECT MAX(v.fixed_salary) max_salary FROM demo.v_staff v WHERE v.dept_code='010101';

結合 GROUP BY 使用：如要統計開發部下各三級部門的最高工資。示例：

SELECT v.dept_code,MAX(v.fixed_salary) max_salary FROM demo.v_staff v
WHERE INSTR(v.dept_code,'0101')=1 GROUP BY v.dept_code;

1.4、MIN 函數

語法：

SELECT MIN(aggregate_expression)
FROM tables
[WHERE conditions];

或

SELECT expression1, expression2, ... expression_n,
       MIN(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

單獨使用：如要統計研發一部的最低工資。示例：

SELECT MAX(v.fixed_salary) min_salary FROM demo.v_staff v WHERE v.dept_code='010101';

結合 GROUP BY 使用：如要統計開發部下各三級部門的最低工資。示例：

SELECT v.dept_code,MAX(v.fixed_salary) min_salary 
FROM demo.v_staff v
WHERE INSTR(v.dept_code,'0101')=1 
GROUP BY v.dept_code;

1.5、AVG 函數

語法：

SELECT AVG(aggregate_expression)
FROM tables
[WHERE conditions];

或

SELECT expression1, expression2, ... expression_n,
       AVG(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

單獨使用：如要統計研發一部的平均工資。示例：

SELECT AVG(v.fixed_salary) avg_salary FROM demo.v_staff v WHERE v.dept_code='010101';

結合 GROUP BY 使用：如要統計開發部下各三級部門的平均工資。示例：

SELECT v.dept_code,AVG(v.fixed_salary) avg_salary 
FROM demo.v_staff v
WHERE INSTR(v.dept_code,'0101')=1 
GROUP BY v.dept_code;

2、ROWNUM 函數

2.1、ROWNUM 函數簡介

SQL 標準中規定了 SELECT TOP 語法，用於限制查詢返回的行數，如 (MS)SQL Server 就實現了標準的 SELECT TOP；而 Oracle 卻沒有直接實現，但 Oracle 中的 ROWNUM 函數功能與 TOP 極其相似，也算是間接的實現了 TOP 吧！ROWNUM 函數的語法比較靈活，相應的語法陷阱也比較多，多數初學者由於對 ROWNUM 函數理解不夠透徹，時常會寫出令自己差異的語句。

在執行查詢的時候，Oracle 會順序的把查詢結果集的行編號賦值給 ROWNUM 函數。這裡有兩個需要註意的細節問題：

第一個，既然 ROWNUM 的值來自於結果集的行編號，那麼也就是說先有結果集，然後才有 ROWNUM 值的。
第二個，行編號從 1 開始，逐行遞增。也就是說，ROWNUM 的值集總是一個首項為 1，公差為 1 的等差數列。

很多人都喜歡稱 ROWNUM 為偽列，我猜一方面由於 Oracle 中“偽造”的對象相對較多，另一方面 ROWNUM 的用法真心跟普通列沒啥區別，就想真有 ROWNUM 列一樣。從這個角度來說，把 ROWNUM 叫偽列也很貼切。示例：

SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM < 1; -- res: 0
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM = 1; -- res: 1

這兩條語句應該比較好理解的，因為 ROWNUM 的值從 1 開始，所以 ROWNUM < 1 的記錄有 0 條，只有第 1 條記錄的 ROWNUM = 1。在 (MS)SQL Server 中查前 N 條記錄只需在查詢欄位列表前加上 TOP N 即可，若要在 Oracle 中實現類似功能則只需在 WHERE 條件中加上 ROWNUM <= N。示例：

-- 查詢年齡最小的 3 名員工的姓名、出生日期、基本工資和崗位工資
SELECT * FROM(SELECT t.staff_name,t.birthday,t.base_salary,t.post_salary FROM demo.t_staff t ORDER BY t.birthday DESC) WHERE ROWNUM<=3;
結果：
STAFF_NAME                                         BIRTHDAY     BASE_SALARY  POST_SALARY
-------------------------------------------------- ----------- ------------ ------------
小玲                                               1994-06-17       2500.00      2900.00
韓三                                               1993-08-18       2500.00      5050.00
王二                                               1992-09-02       2500.00      1850.00

再來看看如下 6 條 SQL 語句（目前員工表中總數據條數為 16）：

SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM <= 10; -- res: 10
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM >= 1;  -- res: 16
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM = 10;  -- res: 0
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM > 1;   -- res: 0
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM != 1;  -- res: 0
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM != 10; -- res: 9

前兩條語句似乎很好理解，如果給員工表的行按數字從 1、2、3 …… 16 編個號，那麼其中編號 <= 10 的有 10 行，即第 1 條語句的結果，編號 >= 1 的有 16 行，即第 2 條語句的結果。但為什麼第 3、4、5 條語句的結果全都是 0 呢？從數學角度來看似乎說不通啊？這就牽扯到上文提到的兩個細節問題了，首先 ROWNUM 是結果集的行編號，有結果集才會有行編號，而 ROWNUM 永遠都是從 1 開始的，換句話說選出的結果集不可能沒有 ROWNUM = 1 的行。結合本例也可以這麼來理解，ROWNUM/行編號總是從 1 開始與運算符右邊的數字做比較，若結果為 TRUE，則該行被選出，並繼續用下一個 ROWNUM/行編號做比較，若結果為 FALSE，則下一行過來後 ROWNUM/行編號還是 1，如此迴圈，就不會產生結果行，效果上相當於停止了比較，最終的結果集為之前所有被選出行的集合。再來看第 3 條語句，由於 1 != 10，所以結果為 0 行。同理，第 4、5 條語句中，1 不 > 1，也不 != 1，所以結果也是 0 行。第 6 條語句中，從 1 到 9 都 != 10，所以能被選出，而 10 不 != 10，從第 10 行開始往後的每一行 ROWNUM 都是 10，都不會被選出（始終選不出第 10 行記錄），所以最終結果是 9 行。講到這裡，相信你已經能夠自己分析出下麵兩條語句的結果了吧！

SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM BETWEEN 1 AND 5; -- res: 5
SELECT COUNT(1) res FROM demo.t_staff WHERE ROWNUM BETWEEN 2 AND 5; -- res: 0

本人剛接觸 Oracle 時，總是習慣性的把 ROWNUM 寫成 ROW_NUMBER()，一執行就報錯，然後改成 ROW_NUMBER、ROW_NUM、ROW 等再執行還是報錯，直到很熟練之後才能一次寫對。後來想想，主要是此前在 (MS)SQL Server 中用過 ROW_NUMBER()，但並不熟練，且 Oracle 中也有 ROW_NUMBER()，實際上在 Oracle 中還有 ROWID、ROWS、ROW 等都是關鍵字。對一個初學者而言，如果不及早把這些關鍵字羅列到一起，仔細區分它們的含義和寫法，著實容易張冠李戴甚至拼寫錯誤。本人在此濃墨重筆，只願讀者你不再和我走一樣的彎路！

2.2、利用 ROWNUM 函數實現分頁功能

普通分頁：若把員工表中數據按每頁 5 條來分頁，下麵將演示取第 2 頁數據的 3 種普通分頁寫法。
寫法一：

SELECT t.staff_name,t.birthday FROM(
  SELECT ROWNUM rn,n.staff_name,n.birthday FROM demo.t_staff n WHERE n.is_disabled=0
) t WHERE t.rn >= ((2-1)*5+1) AND t.rn <= (2*5); -- 或 WHERE t.rn BETWEEN 6 AND 10

寫法二：

SELECT t.staff_name,t.birthday FROM(
  SELECT ROWNUM rn,n.staff_name,n.birthday FROM demo.t_staff n WHERE n.is_disabled=0 AND ROWNUM <= (2*5)
) t WHERE t.rn >= ((2-1)*5+1);

寫法三：

SELECT t.staff_name,t.birthday FROM demo.t_staff t WHERE t.is_disabled=0 AND ROWNUM <= (2*5)
MINUS
SELECT t.staff_name,t.birthday FROM demo.t_staff t WHERE t.is_disabled=0 AND ROWNUM <= ((2-1)*5+1)

上例中的 MINUS 是補集運算符，將在.Net程式員學用Oracle系列(14)：子查詢、集合查詢中具體講解。

排序分頁：實際開發過程中，一般分頁都需要排序，可能用 ROWNUM 寫過排序分頁功能的開發人員都曾遇到過一個陷阱，且聽我細細道來。沿用上半節的案例，再加一個按出生日期順序排序，錯誤示例（僅分析寫法一，其它寫法原理相同）：

SELECT t.staff_name,t.birthday FROM(
  SELECT ROWNUM rn,n.staff_name,n.birthday FROM demo.t_staff n WHERE n.is_disabled=0 ORDER BY n.birthday
) t WHERE t.rn >= ((2-1)*5+1) AND t.rn <= (2*5) ORDER BY t.birthday;

沒有這方面經驗的開發人員十有八九會改寫成上面這樣，如果你改動一下頁碼就會發現這個分頁的排序根本就不對，給人感覺好像子查詢里的那個 ORDER BY 壓根兒就沒起作用。當初我第一次遇到這個陷阱時，也是詫異萬分，其實是因為在這個子查詢中，會先選出滿足 WHERE 條件的記錄，並按物理存儲位置(ROWID)順序給 ROWNUM 賦值，然後再按 ORDER BY 的欄位進行排序，而外部查詢是先選出滿足 ROWNUM 條件的記錄，然後再按 ORDER BY 的欄位進行排序，所以子查詢中的 ORDER BY 會失效。正確示例：

SELECT t3.staff_name,t3.birthday FROM(
  SELECT ROWNUM rn,t2.staff_name,t2.birthday FROM(
    SELECT t1.staff_name,t1.birthday FROM demo.t_staff t1 WHERE t1.is_disabled=0 ORDER BY t1.birthday
  ) t2
) t3 WHERE t3.rn >= ((1-1)*5+1) AND t3.rn <= (1*5);

註意：有一種特殊情況，就是當 ORDER BY 的欄位是主鍵時，Oracle 會先生成 ROWNUM，然後再來排序。

3、高級函數

3.1、高級函數簡介

在 Oracle 提供的高級函數中，除去分析函數仍有 20 來個。本人感覺大部分還是比較實用的，譬如 DECODE、NVL2、LNNVL 等函數都挺好用的，唯一的問題是——它們都是 Oracle 的“方言”。在資料庫編程規範的第 4 節中，已經闡述了為什麼要儘量使用 SQL 標準而不是 Oracle 的“方言”。本節將列出 15 個我個人感覺還比較實用的函數，但只會具體講解 CASE、NVL、SQLCODE 和 SQLERRM 4 個在 Oracle 中找不到替代方案的函數，以及 USER 和 USERENV 兩個在處理系統許可權或環境問題時可能會用上的函數，其它函數不推薦使用，暫不介紹，有興趣的讀者可自行研究。

序號	函數名	語法原型	常用
1	CASE	CASE [ expression ] WHEN condition_1 THEN result_1 ... WHEN condition_n THEN result_n ELSE result END	√
2	DECODE	DECODE( expression , search , result [, search , result]... [, default] )	×
3	EMPTY_BLOB	EMPTY_BLOB()	×
4	EMPTY_CLOB	EMPTY_CLOB()	×
5	GROUP_ID	GROUP_ID()	×
6	LNNVL	LNNVL( condition )	×
7	NANVL	NANVL( value, replace_with )	×
8	NULLIF	NULLIF( expr1, expr2 )	×
9	NVL	NVL( string1, replace_with )	√
10	NVL2	NVL2( string1, value_if_not_null, value_if_null )	×
11	SQLCODE	SQLCODE	×
12	SQLERRM	SQLERRM	×
13	SYS_CONTEXT	SYS_CONTEXT( namespace, parameter [, length] )	×
14	USER	USER	×
15	USERENV	USERENV( parameter )	×

3.2、語法說明及案例

SQLCODE & SQLERRM：當需要在過程中處理異常時，就會用到這兩個函數。經典示例：

EXCEPTION
  WHEN OTHERS THEN
    err_code := SQLCODE;
    err_msg := SUBSTR(SQLERRM, 1, 200);

    INSERT INTO audit_table (error_number, error_message)
    VALUES (err_code, err_msg);
END;

USER：當前登錄的用戶的 USERNAME。實際開發中，有時候我們需要知道當前登錄用戶到底是那一個，這個函數就派上用場了。示例：

SELECT USER res FROM DUAL;  -- res：DEMO

USERENV：用於檢索當前 Oracle 會話信息，可檢索信息的代碼包括：{CLIENT_INFO/ENTRYID/INSTANCE/ISDBA/LANG/LANGUAGE/SESSIONID/TERMINAL}。這個函數替代函數是 SYS_CONTEXT，但這個功能仍然存在向後相容，不過本人只在開發過程中用用，不必考慮這些因素。下麵四個是我覺得比較實用的參數，尤其是第三個，在解決某些由編碼造成的問題時很有用。示例：

SELECT USERENV('ISDBA') res FROM DUAL;    -- res：FALSE
SELECT USERENV('LANG') res FROM DUAL;     -- res：ZHS
SELECT USERENV('LANGUAGE') res FROM DUAL; -- res：SIMPLIFIED CHINESE_CHINA.ZHS16GBK
SELECT USERENV('TERMINAL') res FROM DUAL; -- res：HZZ-PC

4、總結

本文主要講述了 Oracle 中兩種十分常用而又極其特殊的數字函數和部分高級函數。從上上篇博文到上一篇博文，再到本文，本人根據自己工作需要和個人喜好，把 Oracle 中常用的一些系統函數基本都介紹了一遍。

本文鏈接：http://www.cnblogs.com/hanzongze/p/Oracle-SystemFunction-3.html
版權聲明：本文為博客園博主 韓宗澤 原創，作者保留署名權！歡迎通過轉載、演繹或其它傳播方式來使用本文，但必須在明顯位置給出作者署名和本文鏈接！本人初寫博客，水平有限，若有不當之處，敬請批評指正，謝謝！