oracle 数据同步

CREATE OR REPLACE PROCEDURE pro_syn_contact_ecp(p_error_code OUT VARCHAR2)
 IS
---1.数据对比

---2.不存在向比较表和同步表中新增一条记录

---3.存在字段作修改时修改比较表和向同步表中增加一条记录

---4.原表中已经不存在的数据 把比较表中的数据更改为失效，同步表中新增一条记录
  CURSOR c_contact IS
    SELECT * FROM c2u_union_contact_master_v;

  v_contact_str VARCHAR2(30000);
BEGIN

  ----- contact data
  --- step 1 , update snapshot table
  p_error_code := 'Y';
  FOR r_contact IN c_contact
  LOOP
    BEGIN
      SELECT cust_party_id || chinese_name || english_name || dept ||
             dept_type || title || title_type || phones || emails || sex ||
             address_cn || address_en || status
        INTO v_contact_str
        FROM t_erp_contacts_snapshot_t
       WHERE contact_id = r_contact.contact_id;
    EXCEPTION
      WHEN no_data_found THEN
        v_contact_str := NULL;
    END;
 
    IF v_contact_str IS NULL
    THEN
      INSERT INTO t_erp_contacts_snapshot_t
        (contact_id,
         cust_party_id,
         chinese_name,
         english_name,
         dept,
         dept_type,
         title,
         title_type,
         phones,
         emails,
         sex,
         address_cn,
         address_en,
         status,
         update_date)
      VALUES
        (r_contact.contact_id,
         r_contact.cust_party_id,
         r_contact.chinese_name,
         r_contact.english_name,
         r_contact.dept,
         r_contact.dept_type,
         r_contact.title,
         r_contact.title_type,
         r_contact.phones,
         r_contact.emails,
         r_contact.sex,
         r_contact.address_cn,
         r_contact.address_en,
         r_contact.status,
         SYSDATE);
      INSERT INTO t_erp_contacts_snapshot_t_his
        (history_id,
         contact_id,
         cust_party_id,
         chinese_name,
         english_name,
         dept,
         dept_type,
         title,
         title_type,
         phones,
         emails,
         sex,
         address_cn,
         address_en,
         status,
         update_date,
         sync_flag,
         sync_date)
      VALUES
        (c2u.t_erp_contacts_his_s.nextval,
         r_contact.contact_id,
         r_contact.cust_party_id,
         r_contact.chinese_name,
         r_contact.english_name,
         r_contact.dept,
         r_contact.dept_type,
         r_contact.title,
         r_contact.title_type,
         r_contact.phones,
         r_contact.emails,
         r_contact.sex,
         r_contact.address_cn,
         r_contact.address_en,
         r_contact.status,
         SYSDATE,
         'N',
         NULL);
    ELSIF v_contact_str <>
          r_contact.cust_party_id || r_contact.chinese_name ||
          r_contact.english_name || r_contact.dept || r_contact.dept_type ||
          r_contact.title || r_contact.title_type || r_contact.phones ||
          r_contact.emails || r_contact.sex || r_contact.address_cn ||
          r_contact.address_en || r_contact.status
    THEN
      UPDATE t_erp_contacts_snapshot_t
         SET cust_party_id = r_contact.cust_party_id,
             chinese_name  = r_contact.chinese_name,
             english_name  = r_contact.english_name,
             dept          = r_contact.dept,
             dept_type     = r_contact.dept_type,
             title         = r_contact.title,
             title_type    = r_contact.title_type,
             phones        = r_contact.phones,
             emails        = r_contact.emails,
             sex           = r_contact.sex,
             address_cn    = r_contact.address_cn,
             address_en    = r_contact.address_en,
             status        = r_contact.status,
             update_date   = SYSDATE
       WHERE contact_id = r_contact.contact_id;
      INSERT INTO t_erp_contacts_snapshot_t_his
        (history_id,
         contact_id,
         cust_party_id,
         chinese_name,
         english_name,
         dept,
         dept_type,
         title,
         title_type,
         phones,
         emails,
         sex,
         address_cn,
         address_en,
         status,
         update_date,
         sync_flag,
         sync_date)
      VALUES
        (c2u.t_erp_contacts_his_s.nextval,
         r_contact.contact_id,
         r_contact.cust_party_id,
         r_contact.chinese_name,
         r_contact.english_name,
         r_contact.dept,
         r_contact.dept_type,
         r_contact.title,
         r_contact.title_type,
         r_contact.phones,
         r_contact.emails,
         r_contact.sex,
         r_contact.address_cn,
         r_contact.address_en,
         r_contact.status,
         SYSDATE,
         'N',
         NULL);
    END IF;
  END LOOP;

  ----- Step 1.5 cory_liu 2017/2/17 将已经不符合条件的人员失效
  INSERT INTO t_erp_contacts_snapshot_t_his
    (history_id,
     contact_id,
     cust_party_id,
     chinese_name,
     english_name,
     dept,
     dept_type,
     title,
     title_type,
     phones,
     emails,
     sex,
     address_cn,
     address_en,
     status,
     update_date,
     sync_flag,
     sync_date)
    SELECT c2u.t_erp_contacts_his_s.nextval,
           t.contact_id,
           t.cust_party_id,
           t.chinese_name,
           t.english_name,
           t.dept,
           t.dept_type,
           t.title,
           t.title_type,
           t.phones,
           t.emails,
           t.sex,
           t.address_cn,
           t.address_en,
           'I',
           SYSDATE,
           'N',
           NULL
      FROM t_erp_contacts_snapshot_t t
     WHERE t.contact_id IN
           (SELECT c1.contact_id
              FROM t_erp_contacts_snapshot_t c1
             WHERE c1.status = 'A'
               AND NOT EXISTS
             (SELECT 1
                      FROM c2u_union_contact_master_v c2
                     WHERE c2.status = 'A'
                       AND c2.contact_id = c1.contact_id));
  UPDATE t_erp_contacts_snapshot_t
     SET status = 'I', update_date = SYSDATE
   WHERE contact_id IN
         (SELECT contact_id
            FROM t_erp_contacts_snapshot_t c1
           WHERE status = 'A'
             AND NOT EXISTS
           (SELECT 1
                    FROM c2u_union_contact_master_v c2
                   WHERE c2.status = 'A'
                     AND c2.contact_id = c1.contact_id));

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    p_error_code := 'E';
END;