INSERT INTO party_branch
SELECT
UUID(),m.name,m.secreta_name,m.contacts_name,m.contact_phon,m.category_name,m.type,’admin’,’admin’,
NOW(),NOW() FROM mypary_branch m

CREATE OR REPLACE PROCEDURE pro_syn_contact_ecp(p_error_code OUT
VARCHAR2)
 IS
—1.数量比较

             — INSERT INTO t (tid) VALUES (p_tid);

UPDATE party_branch SET id =REPLACE(id,’-‘,”)

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    p_error_code := ‘E’;
END;

         START TRANSACTION; 

非得分别试行 假诺

  v_contact_str VARCHAR2(30000);
BEGIN

   DECLARE STOP INT DEFAULT 0; 

那般生成的uuid全部数据均为相仿

  —– 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));

         SELECT1, ‘oal_xxx’,’pro_oal_log_move’,CONCAT(‘primary
key:’,p_oalid,’ 游标奉行常规结束!’卡塔尔(قطر‎,NOW(State of Qatar;       

 

—2.空头支票向比较表和同步表中新增加一条记下

       FETCH cur_oalid INTO p_oalid;

 

—3.设有字段作改过时修改比较表和向同步表中扩展一条记下

 

INSERT INTO party_branch
SELECT
 REPLACE(UUID(),’-‘,”),m.name,m.secreta_name,m.contacts_name,m.contact_phon,m.category_name,m.type,’admin’,’admin’,
NOW(),NOW() FROM mypary_branch m

  —– 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;

   WHILE STOP <> 1 DO 

 

—4.原表中已经不设有的数据
把比较表中的数额变动为失效,同步表中新扩大一条记下
  CURSOR c_contact IS
    SELECT * FROM c2u_union_contact_master_v;

   

   DECLARE cur_oalid CURSOR FOR

DELIMITER ;

   END; 

 END$$

USE `dbx`$$

 

 

             REPLACE INTO oal_xxx_history SELECT oal.*FROM oal_xxx
oal WHERE oal.id=p_oalid ;

            

         SELECToal.id FROM oal_xxx oal WHERE oal.`ymd`
<CONCAT(YEAR(DATE_ADD(NOW(),INTERVAL -1
MONTH)),’-‘,MONTH(DATE_ADD(NOW(),INTERVAL -1 MONTH
)),’-‘,DAY(DATE_ADD(NOW(),INTERVAL-1 MONTH ))) LIMIT 1000;  

admin

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注