Oracle 開發實用技巧：觸發器自動編號、CONNECT BY 階層查詢、角色權限位運算

1. 觸發器自動分配 Sequence 主鍵

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 為本部門 ID，PORGID 為父部門 ID。

-- 由上而下：找所有子部門
SELECT * FROM ORG
CONNECT BY PRIOR ORGID = PORGID
START WITH ORGID = 1;

-- 由下而上：找所有父部門
SELECT * FROM ORG
CONNECT BY PRIOR PORGID = ORGID
START WITH ORGID = 1;

3. 使用者/角色/權限位運算設計

• 角色權限以 10 位 0/1 字串表示 (10 模組)
• 用戶可擁多角色
• 用戶實際權限 = 所有角色權限字串 OR 運算 (原文為 AND，實務常用 OR)
• 查詢當前用戶對模組權限：

SELECT SUBSTR(POWER, :MOD_ID, 1) RIGHTBIT
FROM ROLE
WHERE ROLEID IN (
  SELECT ROLEID FROM ROLE_USER
  WHERE USERID IN (
    SELECT USERID FROM USERS WHERE LOGINNAME = :LOGINNAME
  )
);
-- 結果集有 '1' 即有權限，全 '0' 則無