出处：http://www.hkln.net/doc/oracle/sql/function.htm#a8
| 簡 介 | 下 一 節 到 頁 頂 |
Oracle 提 供 了 一 些 基 本 的 函 數 ， 用 來 運 算 資 料 ， 這 些 函 數 可 分 為 以 下 幾 類 ：
- 文 字
- 數 目
- 日 期
- 其 它 類 別 。
本 章 所 介 紹 的 函 數 都 是 運 算 一 行 的 資 料 ， 所 以 稱 為 「 單 行 函 數 」 (Single-Row Functions) ， 另 外 有 一 種 叫 做 「 分 組 函 數 」 (Group Functions) ， 用 來 運 算 多 行 的 資 料 ， 分 組 函 數 會 在 稍 後 的 篇 章 介 紹 。
至 於 詳 細 的 函 數 說 明 ， 請 參 考 Oracle 的 SQL Reference 文 件 。
| DUAL 表 格 | 上 一 節 下 一 節 到 頁 頂 |
之 前 介 紹 的 SELECT 句 子 的 FROM 子 句 ， 都 是 寫 一 個 資 料 庫 的 表 格 名 稱 ， 例 如 EMP ， 不 過 有 時 我 們 會 用 資 料 庫 來 做 些 簡 單 的 運 算 ， 例 如 計 算 1+2 ， 這 根 本 不 需 要 使 用 任 何 表 格 ， 那 麼 ， FROM 子 句 應 該 寫 什 麼 呢 ？ 答 案 就 是 DUAL 。
SQL> SELECT 1 + 2 FROM DUAL;
1+2
----------
3
因 為 每 個 SELECT 句 子 都 必 需 有 FROM 子 句 ， 所 以 Oracle 就 做 個 DUAL 表 格 來 符 合 這 個 守 則 了 。
DUAL 表 格 還 可 以 用 來 提 供 一 些 資 訊 ， 例 如 使 用 者 名 稱 、 系 統 時 間 等 。
SQL> SELECT USER FROM DUAL; USER ------------------------------ SCOTT SQL> SELECT SYSDATE FROM DUAL; SYSDATE --------- 03-NOV-02
| 數 目 函 數 | 上 一 節 下 一 節 到 頁 頂 |
| 函數 | 傳回的資料 |
|---|---|
| ABS(X) | 絕 對 值 |
| ROUND(X, Y) | X 的 四 捨 五 入 值 ， 如 果 Y 是 正 數 ， 就 取 小 數 位 的 右 邊 ， 負 數 就 取 左 邊 |
| CEIL(X) | 與 ROUND(X, 0) 差 不 多 ， 不 過 它 會 Round Up |
| FLOOR(X) | 與 CEIL(X) 差 不 多 ， 不 過 它 會 Round Down |
| MOD(X, Y) | X 除 以 Y 的 餘 數 |
| SIGN(X) | 如 果 X 是 正 數 ， 就 傳 回 1 ， 負 數 就 傳 回 -1 ， 0 就 傳 回 0 |
| SQRT(X) | 平 方 根 |
| TRUNC(X, Y) | 與 ROUND(X, Y) 差 不 多 ， 不 過 它 是 Truncation |
例 子 ：
SQL> SELECT ABS(2), ABS(-2) FROM DUAL
ABS(2) ABS(-2)
---------- ----------
2 2
SQL> SELECT ROUND(1.5, 0), ROUND(0.15, 1), ROUND(15, -1) FROM DUAL;
ROUND(1.5,0) ROUND(0.15,1) ROUND(15,-1)
------------ ------------- ------------
2 .2 20
SQL> SELECT CEIL(1.4), CEIL(1.5), CEIL(-1.4), CEIL(-1.5) FROM DUAL;
CEIL(1.4) CEIL(1.5) CEIL(-1.4) CEIL(-1.5)
---------- ---------- ---------- ----------
2 2 -1 -1
SQL> SELECT FLOOR(1.4), FLOOR(1.5), FLOOR(-1.4), FLOOR(-1.5) FROM DUAL;
FLOOR(1.4) FLOOR(1.5) FLOOR(-1.4) FLOOR(-1.5)
---------- ---------- ----------- -----------
1 1 -2 -2
SQL> SELECT MOD(4, 2), MOD(4,3), MOD(4,5) FROM DUAL;
MOD(4,2) MOD(4,3) MOD(4,5)
---------- ---------- ----------
0 1 4
SQL> SELECT SIGN(2), SIGN(-2), SIGN(0) FROM DUAL;
SIGN(2) SIGN(-2) SIGN(0)
---------- ---------- ----------
1 -1 0
SQL> SELECT SQRT(4), SQRT(2) FROM DUAL;
SQRT(4) SQRT(2)
---------- ----------
2 1.41421356
SQL> SELECT TRUNC(12.34, 0), TRUNC(12.34, 1), TRUNC(12.34, -1) FROM DUAL;
TRUNC(12.34,0) TRUNC(12.34,1) TRUNC(12.34,-1)
-------------- -------------- ---------------
12 12.3 10
| 文 字 函 數 | 上 一 節 下 一 節 到 頁 頂 |
| 函數 | 傳回的資料 |
|---|---|
| LPAD(X, Y [,Z]) | 把 X 的 左 邊 加 入 字 元 Z （ 預 設 的 字 元 是 空 格 ） ， 令 它 的 長 度 成 為 Y 。 |
| RPAD(X, Y [,Z]) | 同 上 ， 把 X 的 右 邊 加 入 字 元 Z 。 |
| LOWER(X) | 把 X 所 有 字 元 變 成 小 寫 |
| UPPER(X) | 把 X 所 有 字 元 變 成 大 寫 |
| INITCAP(X) | 把 X 的 每 個 英 文 字 的 第 一 個 字 元 變 成 大 寫 ， 其 它 字 元 變 成 小 寫 。 |
| LENGTH(X) | X 的 長 度 |
| SUBSTR(X, Y [,Z]) | 由 X 的 第 Y 個 字 元 開 始 ， 抽 取 Z 個 字 元 （ 預 設 抽 取 所 有 字 元 ） 。 |
| INSTR(X, Y) | Y 在 X 的 位 置 。 |
| CONCAT(X, Y) | 把 X 和 Y 連 接 。 也 可 以 用 || （ 兩 條 直 線 ） 運 算 子 。 |
例 子 ：
SQL> SELECT LPAD(ENAME, 10, '=') FROM EMP WHERE ENAME = 'SCOTT'
LPAD(ENAME,10,'=')
---------------------
=====SCOTT
SQL> SELECT RPAD(ENAME, 10, '=') FROM EMP WHERE ENAME = 'SCOTT'
RPAD(ENAME,10,'=')
---------------------
SCOTT=====
SQL> SELECT LOWER(ENAME) FROM EMP WHERE ENAME = 'SCOTT';
LOWER(ENAM
----------
scott
SQL> SELECT INITCAP(ENAME) FROM EMP WHERE ENAME = 'SCOTT';
INITCAP(EN
----------
Scott
SQL> SELECT LENGTH(ENAME) FROM EMP WHERE ENAME = 'SCOTT';
LENGTH(ENAME)
-------------
5
SQL> SELECT SUBSTR(ENAME, 2, 3) FROM EMP WHERE ENAME = 'SCOTT';
SUBSTR
------
COT
SQL> SELECT INSTR(ENAME, 'COT') FROM EMP WHERE ENAME = 'SCOTT';
INSTR(ENAME,'COT')
------------------
2
SQL> SELECT ENAME || '_' || JOB || '_' || SAL FROM EMP WHERE ENAME = 'SCOTT';
ENAME||'_'||JOB||'_'||SAL
-------------------------------------------------------------
SCOTT_ANALYST_3000
說 明 ：
SQL> SELECT SUBSTR(ENAME, 2, 3) FROM EMP WHERE ENAME = 'SCOTT';
SUBSTR
------
COT
留 意 第 一 個 字 元 的 位 置 是 1 ， 而 不 是 0 。
| NVL 函 數 | 上 一 節 下 一 節 到 頁 頂 |
NVL 函 數 可 以 把 NULL 值 轉 換 成 另 一 些 資 料 。 假 設 你 想 計 算 EMP 表 格 內 的 每 個 僱 員 的 總 薪 金 ， 而 總 薪 金 的 計 法 是 SAL + COMM ， 你 可 能 會 寫 ：
SQL> SELECT ENAME, SAL, COMM, (SAL + COMM) TOTAL_SALARY FROM EMP; ENAME SAL COMM TOTAL_SALARY ---------- ---------- ---------- ------------ SMITH 800 ALLEN 1600 300 1900 WARD 1250 500 1750 JONES 2975 MARTIN 1250 1400 2650 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 0 1500 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 14 rows selected.
為 什 麼 有 些 行 的 TOTAL_SALARY 是 空 的 呢 ？ 因 為 它 們 的 COMM 是 NULL ， 而 NULL 加 上 數 目 都 會 是 NULL 。
解 決 方 法 就 是 把 NULL 值 轉 換 成 0 ， 那 就 不 會 得 出 NULL 了 。
SQL> SELECT ENAME, SAL, NVL(COMM, 0), (SAL + NVL(COMM, 0)) TOTAL_SALARY FROM EMP; ENAME SAL NVL(COMM,0) TOTAL_SALARY ---------- ---------- ----------- ------------ SMITH 800 0 800 ALLEN 1600 300 1900 WARD 1250 500 1750 JONES 2975 0 2975 MARTIN 1250 1400 2650 BLAKE 2850 0 2850 CLARK 2450 0 2450 SCOTT 3000 0 3000 KING 5000 0 5000 TURNER 1500 0 1500 ADAMS 1100 0 1100 JAMES 950 0 950 FORD 3000 0 3000 MILLER 1300 0 1300 14 rows selected.
說 明 ：
NVL(COMM, 0)
如 果 COMM 是 NULL ， 就 傳 回 0 ， 否 則 傳 回 COMM 。
| DECODE 函 數 | 上 一 節 下 一 節 到 頁 頂 |
DECODE 函 數 的 功 能 有 點 像 程 式 語 言 的 if-then-else 句 子 ， 它 會 能 夠 根 據 資 料 不 同 的 值 ， 傳 回 不 同 的 資 料 ， 語 法 如 下 ：
DECODE(COLUMN_NAME
,VALUE1, SUBSTITUTE1
,VALUE2, SUBSTITUTE2
, ...
,DEFAULT
)
如 果 COLUMN_NAME 的 值 是 VALUE1 ， 就 傳 回 SUBSTITUTE1 ， VALUE2 ， 就 傳 回 SUBSTITUTE2 ， 如 此 類 推 ， 如 果 以 上 的 值 都 不 是 ， 就 傳 回 DEFAULT 。
其 實 DECODE 也 可 以 做 到 NVL 的 功 能 ， 例 如 NVL(COMM, 0) 可 以 寫 成 ：
DECODE(COMM, NULL, 0, COMM)
假 設 你 想 列 出 每 個 僱 員 的 部 門 名 稱 ， 除 了 可 以 把 EMP 連 接 到 DEPT 外 ， 還 可 以 用 DECODE 來 做 。
SQL> SELECT ENAME 2 ,DECODE(DEPTNO 3 ,10 ,'Accounting' 4 ,20 ,'Research' 5 ,30 ,'Sales' 6 ,40 ,'Opeartions' 7 ,'UNKNOWN' 8 ) DEPARTMENT 9 FROM EMP 10 ; ENAME DEPARTMENT ---------- ------------- SMITH Research ALLEN Sales WARD Sales JONES Research MARTIN Sales BLAKE Sales CLARK Accounting SCOTT Research KING Accounting TURNER Sales ADAMS Research JAMES Sales FORD Research MILLER Accounting 14 rows selected.
| 日 期 函 數 | 上 一 節 下 一 節 到 頁 頂 |
Oracle 是 用 數 目 來 儲 存 日 期 資 料 的 ， 數 目 的 整 數 部 份 代 表 與 Julian Calendar 相 距 的 日 數 （ 由 公 元 前 4712 年 1 月 1 日 開 始 ） ， 而 小 數 部 份 代 表 時 、 分 和 秒 。
例 子 ：
SQL> SELECT SYSDATE ,SYSDATE - 1 YESTERDAY ,SYSDATE + 1 TOMORROW 2 FROM DUAL; SYSDATE YESTERDAY TOMORROW --------- --------- --------- 03-NOV-02 02-NOV-02 04-NOV-02
說 明 ：
示 範 把 日 期 當 作 數 目 來 運 算
| 函數 | 傳回的資料 |
|---|---|
| ADD_MONTHS(X, Y) | X 的 Y 個 月 後 的 日 期 |
| LAST_DAY(X) | X 所 屬 月 份 的 最 後 一 日 |
| MONTHS_BETWEEN(X, Y) | X 與 Y 之 間 相 距 多 少 個 月 ， 如 果 X 比 Y 少 ， 傳 回 的 是 一 個 負 數 。 |
| NEXT_DAY(X, Y) | X 的 下 一 個 星 期 Y |
例 子 ：
SQL> SELECT ADD_MONTHS('3-NOV-02', 2) FROM DUAL;
ADD_MONTH
---------
03-JAN-03
SQL> SELECT LAST_DAY('3-NOV-02') FROM DUAL;
LAST_DAY(
---------
30-NOV-02
SQL> SELECT MONTHS_BETWEEN('3-NOV-02', '1-OCT-02') FROM DUAL;
MONTHS_BETWEEN('3-NOV-02','1-OCT-02')
-------------------------------------
1.06451613
SQL> SELECT NEXT_DAY('2-NOV-02', 'TUESDAY') FROM DUAL;
NEXT_DAY(
---------
05-NOV-02
| 日 期 函 數 : TO_CHAR 函 數 | 上 一 節 下 一 節 到 頁 頂 |
你 可 以 透 過 TO_CHAR 函 數 ， 把 日 期 資 料 轉 換 成 指 定 的 文 字 格 式 。
例 子 ：
SQL> SELECT SYSDATE FROM DUAL; SYSDATE --------- 03-NOV-02 SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-RRRR HH24:MI:SS') FROM DUAL; TO_CHAR(SYSDATE,'DD- -------------------- 03-NOV-2002 19:52:49
說 明 ：
TO_CHAR 函 數 的 第 二 個 參 數 是 轉 換 的 格 式 ：
| 格式字元 | 說明 |
|---|---|
| D | 星 期 幾 （ 1 至 7 ） |
| DD | 月 的 第 幾 日 （ 1 至 31 ） |
| MON | 月 （ 簡 寫 ） |
| MONTH | 月 （ 全 寫 ） |
| YY | 年 （ 2 個 數 位 ） |
| YYYY | 年 （ 4 個 數 位 ） |
| RR | 年 （ 2 個 數 位 ， 「 千 禧 年 相 容 」 (Millennium-Compliant) ） |
| RRRR | 年 （ 4 個 數 位 ， 「 千 禧 年 相 容 」 (Millennium-Compliant) ） |
| HH | 時 （ 2 個 數 位 ） |
| HH24 | 時 （ 2 個 數 位 ， 24 小 時 制 ） |
| MI | 分 （ 2 個 數 位 ） |
| SS | 秒 （ 2 個 數 位 ） |
| 日 期 函 數 : TO_DATE 函 數 | 上 一 節 到 頁 頂 |
TO_DATE 函 數 是 用 來 把 文 字 轉 換 成 日 期 的 ， 例 如 當 你 在 SQL 中 表 示 一 個 日 期 ， 可 以 寫 ：
SQL> SELECT TO_DATE('03-NOV-02', 'DD-MON-RR') FROM DUAL;
TO_DATE('
---------
03-NOV-02
第 一 個 參 數 是 文 字 內 容 ， 第 二 個 參 數 是 該 文 字 內 容 的 格 式 ， Oracle 會 跟 據 這 個 格 式 來 把 文 字 轉 成 日 期 。
假 設 你 想 知 道 EMP 表 格 中 ， 有 哪 些 僱 員 是 於 1987 年 1 月 1 日 或 以 後 受 僱 的 ， 可 以 寫 ：
SQL> SELECT ENAME, HIREDATE FROM EMP
2 WHERE TO_DATE('01-JAN-1987', 'DD-MON-YYYY') <= HIREDATE
3 ;
ENAME HIREDATE
---------- ---------
SCOTT 19-APR-87
ADAMS 23-MAY-87
因 為 HIREDATE 是 日 期 ， 所 以 必 需 用 TO_DATE 把 你 寫 在 SQL 的 文 字 轉 成 日 期 ， 才 能 作 出 比 較 。
如 果 你 沒 有 輸 入 第 二 個 參 數 ， 那 麼 Oracle 就 會 使 用 預 設 的 日 期 格 式 ， 它 是 儲 存 在 一 個 名 叫 NLS_DATE_FORMAT 的 參 數 ， 你 可 以 在 表 格 V$NLS_PARAMETERS 取 得 ：
SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR
SQL> SELECT ENAME, HIREDATE FROM EMP
2 WHERE TO_DATE('01-JAN-1987') <= HIREDATE
3 ;
ENAME HIREDATE
---------- ---------
SCOTT 19-APR-87
ADAMS 23-MAY-87
由 此 可 見 ， 預 設 的 格 式 為 DD-MON-RR ， 所 以 就 算 TO_DATE 沒 有 第 二 個 參 數 也 沒 有 問 題 。