Oracle 資料庫進階物件實戰：視圖、索引、序列、同義詞、函數、觸發器、存儲過程與 Package

Oracle 資料庫進階物件實戰指南

本文系統整理 Oracle 進階資料庫物件，涵蓋視圖、索引、序列、同義詞、函數、觸發器、存儲過程與 Package，提供建立語法、核心參數、使用場景、最佳實踐與常見陷阱，適合開發與 DBA 參考。

第十一章 視圖

核心特性

• 不儲存資料，僅儲存查詢定義（資料字典）。
• 用途：安全性控管（隱藏敏感欄位）、簡化複雜多表查詢。

建立與選項

-- 基本建立
CREATE OR REPLACE VIEW emp_v20 (empno, ename, sal) 
AS SELECT empno, ename, sal FROM emp WHERE deptno = 20;

-- WITH CHECK OPTION：防止更新違背視圖定義條件
CREATE OR REPLACE VIEW emp_v20_chk 
AS SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 20
WITH CHECK OPTION CONSTRAINT emp_v20_ck;

-- WITH READ ONLY：唯讀視圖
CREATE OR REPLACE VIEW emp_v20_ro 
AS SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 20
WITH READ ONLY;

不可更新視圖場景

• 包含 GROUP BY、DISTINCT、集合運算、多表連接（部分情況）、衍生欄位等。

第十二章 索引

工作原理

索引儲存 (key_value, ROWID) 對，透過 B-Tree 結構快速定位 ROWID，再回表取資料。獨立於表儲存，Oracle 自動維護。

優缺點權衡

建立原則

• 查詢佔大多數、WHERE 條件頻出、選擇性高（接近 1，如 PK）的欄位。
• 複合索引：最高選擇性、最常用 WHERE 欄位放最左邊。
• 小表（<5M）優先全表掃描。

語法與類型

-- 一般索引
CREATE INDEX idx_emp_dept ON emp(deptno);

-- 複合索引（欄位順序關鍵）
CREATE INDEX idx_emp_dept_job ON emp(deptno, job);

-- 唯一索引
CREATE UNIQUE INDEX uk_emp_ename ON emp(ename);

-- 位圖索引（低基數欄位，如性別、狀態）
CREATE BITMAP INDEX bmp_emp_sex ON emp(sex);

-- 函數索引
CREATE INDEX idx_emp_upper_name ON emp(UPPER(ename));

索引失效常見原因

• WHERE 條件未用複合索引第一欄位。
• 索引欄參與運算/函數（UPPER(col)、col+0、TO_CHAR 等）。
• 使用 IS NULL、NOT IN、!=、LIKE '%...' 等。

維護建議

• 大量匯入後再建索引；定期 REBUILD 減少碎片；刪除無用索引；指定 TABLESPACE 分離 I/O。

第十三章 序列與同義詞

序列

CREATE SEQUENCE seq_emp
INCREMENT BY 1 START WITH 1 MAXVALUE 999999 MINVALUE 1
CYCLE CACHE 20 NOORDER;

-- 使用
SELECT seq_emp.NEXTVAL FROM dual;  -- 取得下一值
SELECT seq_emp.CURRVAL FROM dual;  -- 取得當前值（需先 NEXTVAL）
INSERT INTO emp(empno) VALUES(seq_emp.NEXTVAL);

注意：CACHE 可能導致跳號（異常關機丟失緩存值），關鍵業務建議 NOCACHE。

同義詞

• 私有：CREATE SYNONYM emp FOR scott.emp; 僅建立者可用。
• 公有：CREATE PUBLIC SYNONYM emp FOR scott.emp; 所有用戶可用（仍需權限）。
• 用途：簡化 SQL、屏蔽 Schema 名稱、分散式資料庫位置透明性。

第十四章 函數

CREATE OR REPLACE FUNCTION get_annual_sal(p_empno emp.empno%TYPE)
RETURN NUMBER
AS
    v_sal NUMBER;
BEGIN
    SELECT (sal + NVL(comm,0)) * 12 INTO v_sal FROM emp WHERE empno = p_empno;
    RETURN v_sal;
END;
/
-- 呼叫
SELECT get_annual_sal(7369) FROM dual;

參數模式：IN（預設，唯讀）、OUT（唯寫）、IN OUT（可讀寫）。

第十五章 觸發器

分類與觸發時機

行級觸發器範例：防止薪資降低

CREATE OR REPLACE TRIGGER trg_emp_sal_chk
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
    IF :NEW.sal < :OLD.sal THEN
        RAISE_APPLICATION_ERROR(-20010, '薪資不可降低');
    END IF;
END;
/
-- :NEW 為新值，:OLD 為舊值；INSERT 時 :OLD 為 NULL，DELETE 時 :NEW 為 NULL

INSTEAD OF 觸發器：讓複雜視圖可更新

CREATE OR REPLACE TRIGGER trg_v_reg_cou_ins
INSTEAD OF INSERT ON v_reg_cou
FOR EACH ROW
DECLARE v_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_cnt FROM regions WHERE region_id = :NEW.r_id;
    IF v_cnt = 0 THEN INSERT INTO regions VALUES(:NEW.r_id, :NEW.r_name); END IF;
    SELECT COUNT(*) INTO v_cnt FROM countries WHERE country_id = :NEW.c_id;
    IF v_cnt = 0 THEN INSERT INTO countries VALUES(:NEW.c_id, :NEW.c_name, :NEW.r_id); END IF;
END;

限制事項

• 觸發器主體不可查詢/修改觸發表（Mutating Table 錯誤）。
• 不可含 COMMIT/ROLLBACK/SAVEPOINT。
• LONG/LOB 操作受限。

第十六章 存儲過程

CREATE OR REPLACE PROCEDURE add_dept(
    p_deptno IN dept.deptno%TYPE,
    p_dname  IN dept.dname%TYPE,
    p_loc    IN dept.loc%TYPE
) AS
    v_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_cnt FROM dept WHERE deptno = p_deptno;
    IF v_cnt = 0 THEN
        INSERT INTO dept VALUES(p_deptno, p_dname, p_loc);
        DBMS_OUTPUT.PUT_LINE('新增成功');
    ELSE
        DBMS_OUTPUT.PUT_LINE('部門已存在');
    END IF;
END;
/
-- 呼叫
EXEC add_dept(50, 'AI', 'TAIPEI');

參數模式同函數；OUT/IN OUT 需用變數接收回傳值。

第十七章 Package

結構：規格與主體分離

-- 規格（介面）
CREATE OR REPLACE PACKAGE pkg_dept AS
    FUNCTION add_dept(p_no NUMBER, p_name VARCHAR2, p_loc VARCHAR2) RETURN NUMBER;
    FUNCTION del_dept(p_no NUMBER) RETURN NUMBER;
    PROCEDURE query_dept(p_no IN NUMBER);
END pkg_dept;
/

-- 主體（實作）
CREATE OR REPLACE PACKAGE BODY pkg_dept AS
    FUNCTION add_dept(...) RETURN NUMBER IS ... BEGIN ... END;
    FUNCTION del_dept(...) RETURN NUMBER IS ... BEGIN ... END;
    PROCEDURE query_dept(...) IS ... BEGIN ... END;
BEGIN NULL; END pkg_dept;
/
-- 呼叫
EXEC pkg_dept.add_dept(60, 'DATA', 'TAICHUNG');

優勢

• 模組化、資訊隱藏（主體可變不影響規格）。
• 一次載入進記憶體，減少磁碟 I/O。
• 支援多載、靜態變數共享會話狀態。