「數字公交」專案 Oracle 開發實戰技巧三則

專案背景

基於 Oracle C/S 架構的「數字公交」專案，整理三個高頻復用的開發技巧，供後續維護參考。

技巧一：觸發器自動填充 Sequence 主鍵

表 USERS 主鍵 USERID 取自 Sequence S_USERID，使用 Row-Level Trigger 透明化分配：

CREATE OR REPLACE TRIGGER users_trig
BEFORE INSERT ON users
FOR EACH ROW
DECLARE
    seq_val NUMBER;
BEGIN
    SELECT s_userid.NEXTVAL INTO seq_val FROM dual;
    :NEW.userid := seq_val;
END;

優點：應用端無需關心主鍵生成邏輯，避免併發衝突。

技巧二：CONNECT BY 處理遞迴階層查詢

部門表 ORG 含 ORGID (當前 ID) 與 PORGID (父部門 ID)，需查詢完整上下游：

-- 向下查詢所有子部門 (從 ORGID=1 開始)
SELECT * FROM org
CONNECT BY PRIOR orgid = porgid
START WITH orgid = 1;

-- 向上查詢所有父部門 (從 ORGID=1 開始)
SELECT * FROM org
CONNECT BY PRIOR porgid = orgid
START WITH orgid = 1;

PRIOR 指向父行欄位，START WITH 定義根節點。Oracle 專屬語法，簡潔高效。

技巧三：位元運算實現 角色-模組 權限矩陣

需求：10 個模組，角色擁有權限用 10 位 0/1 字串表示 (如 '1010001100')。用戶可有多角色，最終權限為所有角色字串逐位 OR 運算。

查詢當前用戶對指定模組 modID 是否有權：

SELECT SUBSTR(power, :modID, 1) AS rightbit
FROM role
WHERE roleid IN (
    SELECT roleid FROM role_user
    WHERE userid IN (
        SELECT userid FROM users WHERE loginname = :loginName
    )
);

應用端邏輯：結果集中任一行 rightbit = '1' 即有權，全為 '0' 則無權。

優點：單欄位儲存多模組權限，查詢極快，避免多表 Join。

總結

三個技巧分別解決：主鍵自動化、樹狀結構查詢、緊湊權限模型，均為 Oracle 開發高頻場景的最佳實踐。