Oracle 透明閘道同步人力資料庫：組織架構與人員資料 JOB 實作

跨資料庫同步人力資料是企業資訊整合常見需求。本文展示利用 Oracle 透明閘道（DB Link 指向 HR 資料庫）撰寫 PL/SQL JOB，定期同步「組織架構」與「人員資料」的完整邏輯，包含刪除、更新、新增三階段處理。

同步範圍與來源

• 來源：HR 資料庫 pubpeople@HR_51_PROD.REGRESS.RDBMS.DEV.US.Oracle.COM
• 目標：本地表 EIP_SYS_DEPARTMENT（組織）、EIP_SYS_USER（人員）
• 同步鍵：組織用 section_no || dept_no，人員用 username（HR 端 user_id 前綴 g）

第一階段：同步組織架構

/*----------------同步組織機構------------------- */
-- 1. 清空現有資料
DELETE FROM eip_sys_department;

-- 2. 從 HR 撈取有效部門（dept_no 不為 null）
INSERT INTO eip_sys_department
SELECT DISTINCT section_no || dept_no, section_no, section, dept_no, dept
FROM pubpeople@HR_51_PROD.REGRESS.RDBMS.DEV.US.Oracle.COM
WHERE dept_no IS NOT NULL;

-- 3. 手動補充固定單位（視專案需求）
INSERT INTO eip_sys_department (id, sectionid, section, deptid, dept) VALUES ('23234','23','天聯公司','234','經營部');
INSERT INTO eip_sys_department (id, sectionid, section, deptid, dept) VALUES ('23235','23','天聯公司','235','人力資源部');
INSERT INTO eip_sys_department (id, sectionid, section, deptid, dept) VALUES ('23236','23','天聯公司','236','項目部');
INSERT INTO eip_sys_department (id, sectionid, section, deptid, dept) VALUES ('23238','23','天聯公司','238','業務部');
INSERT INTO eip_sys_department (id, sectionid, section, deptid, dept) VALUES ('23239','23','天聯公司','239','綜合部');

COMMIT;
/*----------------同步組織機構完畢-------------------*/

第二階段：同步人員資料（三步驟策略）

邏輯說明：

1. 刪除：本地存在但 HR 已離職（HR 無此 user_id）且 HRUSER = 'y' 的帳號。
2. 更新：本地與 HR 同鍵值，欄位異動時更新（程式碼省略，實務常用 MERGE）。
3. 新增：HR 有但本地無的新進人員（程式碼省略）。

/*----------------同步人員信息-------------------*/
/*
同步信息分成三步：
1、刪除 EIP 中 username 為 'g' 開頭，且 HR 不存在的資料
2、更新 EIP 與 HR 同鍵的人員資訊
3、新增 HR 有但 EIP 無的人員資訊
*/

-- 步驟 1：刪除已離職人員
DELETE FROM eip_sys_user t
WHERE username LIKE 'g____%'
  AND NOT EXISTS (
    SELECT 1
    FROM pubpeople@HR_51_PROD.REGRESS.RDBMS.DEV.US.Oracle.COM pp
    WHERE 'g' || pp.user_id = t.username
  )
  AND HRUSER = 'y';

-- 步驟 2：更新現有人員（範例，實務建議用 MERGE）
/*
MERGE INTO eip_sys_user t
USING (
  SELECT 'g' || user_id AS username, ...其他欄位...
  FROM pubpeople@HR_51_PROD.REGRESS.RDBMS.DEV.US.Oracle.COM
) s
ON (t.username = s.username)
WHEN MATCHED THEN UPDATE SET t.col1 = s.col1, ...
WHEN NOT MATCHED THEN INSERT (...) VALUES (...);
*/

-- 步驟 3：新增新進人員（同上 MERGE 可一次完成）

COMMIT;
/*----------------同步人員信息完畢-------------------*/

排程部署建議

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'SYNC_HR_DATA_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN sync_hr_procedure; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0', -- 每天凌晨 2 點
    enabled         => TRUE,
    comments        => '同步 HR 組織與人員資料'
  );
END;
/

效能與維運注意事項

• 大量刪除/插入會產生大量 UNDO 與 REDO，建議分批處理（如每 5,000 筆 COMMIT）。
• DB Link 斷線重試：在 PL/SQL 中包裹 EXCEPTION WHEN OTHERS THEN ... RETRY 機制。
• 同步時間窗：避開 HR 系統維護時段，建議凌晨低峰期執行。
• 稽核追蹤：在同步表加入 SYNC_TIME、SYNC_STATUS 欄位，記錄每筆資料最後同步狀態。

此架構已在多個專案驗證穩定，配合 DBMS_SCHEDULER 可實現全自動化、可監控的跨庫人力資料同步。