Oracle 開發實戰技巧：觸發器、階層查詢與權限設計

1. 觸發器自動賦值 Sequence 當主鍵

避免應用層手動取序號，透過 BEFORE INSERT 觸發器自動填入：

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;

2. CONNECT BY 階層查詢（組織架構/物料清單）

org 表含 orgid(現節點) 與 porgid(父節點)：

• 向下找所有子部門：SELECT * FROM org CONNECT BY PRIOR orgid = porgid START WITH orgid = 1;
• 向上找所有父部門：SELECT * FROM org CONNECT BY PRIOR porgid = orgid START WITH orgid = 1;

搭配 LEVEL、SYS_CONNECT_BY_PATH 可輸出層級與完整路徑。

3. 角色-權限位元運算設計

假設系統有 10 個模組，角色權限用 10 碼「0/1 字串」表示（如 1010001100）。用戶可擁有多角色，最終權限 = 所有角色權限字串逐位 OR 運算。

-- 查詢某用戶在指定模組是否有權限
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 = :strLoginName
  )
);

應用層讀取結果集，若存在 '1' 則放行，全為 '0' 則拒絕。此設計儲存緊湊、查詢極快，適合模組數固定、權限頻繁檢查的系統。