您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle資料庫入門：函數與資料類型詳解

編輯：Oracle 資料庫相關知識

Oracle 主要資料類型

類型	特性與用途
`VARCHAR2(n)`	變長字串，上限 4000 位元組。插入值短於上限時自動縮減儲存，不補空格。推薦首選字串型別。
`NUMBER(p,s)`	精確數值。`p` 精度(1-38)，`s` 小數位。`s=0` 或省略為整數。可同時表達整數與浮點數。
`DATE`	日期+時間（秒精度）。內部儲存：世紀、年、月、日、時、分、秒。預設格式 `DD-MON-RR`。
`BLOB`	二進位大物件，通常透過 JDBC 等程式介面讀寫，較少直接用 SQL 操作。
`CHAR(n)`	定長字串，不足長度右側補空格。比較時需注意空格處理。最大 2000 位元組。
`NCHAR / NVARCHAR2`	Unicode 定長/變長字串，採用國家字符集（預設 AL16UTF16），支援多語言。
`VARCHAR`	SQL 標準型別，Oracle 提供 `VARCHAR2` 保證向前相容，勿使用 VARCHAR。
`LONG`	變長字串（至 2GB），不支援 WHERE 條件檢索，已被 CLOB 取代。

字符集查詢

SELECT * FROM v$nls_parameters;
-- NLS_CHARACTERSET      : 資料庫字符集 (如 ZHS16GBK，中文 2 位元組)
-- NLS_NCHAR_CHARACTERSET: 國家字符集 (預設 AL16UTF16，16-bit Unicode)

字符集修改風險極高，非 DBA 勿動。
跨庫匯入匯出時字符集不一致易導致亂碼。

函數分類總覽

分類	特性	代表函數
單行函數	逐列運算，每列回傳一值，可改變資料型別，可巢狀無限層	字元、數值、日期、轉換、通用
多行/分組函數	對一組列運算，回傳單一值，最多巢狀兩層	AVG, COUNT, MAX, MIN, SUM, STDDEV, VARIANCE

巢狀規則

單行函數：無限制層數，由內而外執行。
分組函數：最多兩層（如 MAX(AVG(sal))）。

-- 單行巢狀
SELECT empno, LPAD(INITCAP(TRIM(ename)), 10, ' ') AS 姓名, job, sal FROM emp;

-- 分組巢狂（實務意義有限）
SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;

最佳實務

資料庫層函數僅處理簡單轉換、格式化、空值處理等常規邏輯。複雜業務規則應置於應用層，避免邏輯耦合，利於維護、移植與資料庫管理。

日期型別運算

運算	結果	說明
DATE + 數值	DATE	數值代表天數
DATE - 數值	DATE	數值代表天數
DATE - DATE	數值	相差天數（含小數代表時分秒）
DATE + DATE	錯誤	無意義

核心通用函數 (空值處理與條件邏輯)

NVL(expr1, expr2)

expr1 為 NULL 時回傳 expr2，否則回傳 expr1。

SELECT empno, ename, sal, comm, sal + NVL(comm, 0) AS total FROM emp;
SELECT empno, ename, job, NVL(job, 'No job yet') FROM emp;

NVL2(expr1, expr2, expr3)

expr1 不為 NULL 回傳 expr2，否則回傳 expr3。

SELECT empno, ename, sal, comm,
       NVL2(comm, sal + comm, sal) AS 總收入 FROM emp;

NULLIF(expr1, expr2)

兩值相等回傳 NULL，否則回傳 expr1。

SELECT name 原名, NULLIF(pen_name, name) 化名 FROM author;

COALESCE(expr1, expr2, ...)

依序檢查，回傳第一個非 NULL 值；全為 NULL 則回傳 NULL。

SELECT empno, ename, sal, comm,
       COALESCE(sal + comm, sal, 0) AS 總收入 FROM emp;

CASE 運算式 (標準 SQL)

CASE expr
    WHEN comparison1 THEN result1
    WHEN comparison2 THEN result2
    ...
    ELSE default_result
END

範例：部門代碼轉名稱

SELECT empno, ename, sal,
    CASE deptno
        WHEN 10 THEN '財務部'
        WHEN 20 THEN '研發部'
        WHEN 30 THEN '銷售部'
        ELSE '未知部門'
    END AS 部門
FROM emp;

CASE ... END 整體視為一個查詢欄位，END 後可加別名。
每個運算式可為複合運算式。

DECODE 函數 (Oracle 專有)

DECODE(col | expr, search1, result1, search2, result2, ..., default)

同樣範例

SELECT empno, ename, sal,
    DECODE(deptno, 10, '財務部', 20, '研發部', 30, '銷售部', '未知部門') AS 部門
FROM emp;

分組函數與 GROUP BY / HAVING

COUNT 變體

COUNT(*)           -- 組內總列數（含 NULL）
COUNT(expr)        -- expr 非 NULL 的列數
COUNT(DISTINCT expr) -- expr 非 NULL 且不重複的列數

SELECT COUNT(*) FROM emp;
SELECT COUNT(comm) FROM emp;                 -- 有提成的人數
SELECT COUNT(DISTINCT deptno) FROM emp;      -- 部門種類數

分組函數忽略 NULL

SELECT AVG(comm) FROM emp;          -- 等同 SUM(comm)/COUNT(comm)，忽略 NULL
SELECT AVG(NVL(comm, 0)) FROM emp;  -- 強制將 NULL 視為 0 參與計算

GROUP BY 語法

SELECT 欄位, 分組函數(欄位)
FROM table
[WHERE 條件]
[GROUP BY 分組運算式]
[ORDER BY 欄位];

SELECT 清單中未包含於分組函數的欄位，必須出現在 GROUP BY。
GROUP BY 欄位不一定要出現在 SELECT。
執行順序：WHERE → GROUP BY → ORDER BY（預設升序）。

-- 多欄位分組
SELECT deptno, job, AVG(sal) FROM emp GROUP BY deptno, job ORDER BY deptno DESC;

-- 非法：未分組欄位混用分組函數
SELECT deptno, AVG(sal) FROM emp;  -- 錯誤

WHERE 不可用分組函數

-- 非法：WHERE 階段尚未分組、未計算平均
SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) > 2000 GROUP BY deptno;

HAVING 過濾分組

SELECT deptno, job, AVG(sal)
FROM emp
WHERE hiredate >= TO_DATE('1981-05-01', 'YYYY-MM-DD')
GROUP BY deptno, job
HAVING AVG(sal) > 1200
ORDER BY deptno, job;