【merge】openGauss、PostgreSQL、Oracle的merge写法

---

测试表

/* 测试表 tzq_log_t */
CREATE TABLE tzq.tzq_log_t (
  log_id int8 not null default nextval('tzq_log_s'),
  log_type varchar(100),
  log_title varchar(100),
  log_content text,
  last_update_date timestamp(6) not null default CURRENT_TIMESTAMP,
  CONSTRAINT tzq_log_t_pkey PRIMARY KEY (log_id)
);
/* 测试接口表 tzq_log_ti */
CREATE TABLE tzq.tzq_log_ti (
  log_id int8 not null,
  log_type varchar(100),
  log_title varchar(100),
  log_content text,
  last_update_date timestamp(6),
  process_status int8,
  process_date timestamp(6)
);

一、openGauss

/* openGauss 的 merge */
DO $$ 
DECLARE
  cur_interface record;
  v_isok BOOLEAN;
BEGIN
  FOR cur_interface IN (SELECT ctid AS row_id
                              ,log_id
                              ,log_type
                              ,log_title
                              ,log_content
                              ,last_update_date
                          FROM tzq.tzq_log_ti
                         WHERE process_status = 2
                         ORDER BY log_id ASC) LOOP
    BEGIN
      v_isok:= true;
      MERGE INTO tzq.tzq_log_t a
      USING (SELECT cur_interface.log_id
                   ,cur_interface.log_type
                   ,cur_interface.log_title
                   ,cur_interface.log_content
                   ,cur_interface.last_update_date) b
      ON (a.log_id = b.log_id)
      WHEN MATCHED THEN
        UPDATE
           SET log_type         = b.log_type
              ,log_title        = b.log_title
              ,log_content      = b.log_content
              ,last_update_date = b.last_update_date
      WHEN NOT MATCHED THEN
        INSERT
          (a.log_id
          ,a.log_type
          ,a.log_title
          ,a.log_content
          ,a.last_update_date)
        VALUES
          (b.log_id
          ,b.log_type
          ,b.log_title
          ,b.log_content
          ,b.last_update_date);
    END;
    IF (v_isok) THEN
      UPDATE tzq.tzq_log_ti ti
         SET process_status   = 4
       WHERE ti.ctid = cur_interface.row_id;
      COMMIT;
    END IF;
  END LOOP;
END $$;

二、PostgreSQL

/* PostgreSQL 的 merge */
DO $$ 
DECLARE
  cur_interface record;
  v_isok BOOLEAN;
BEGIN
  FOR cur_interface IN (SELECT ctid AS row_id
                              ,log_id
                              ,log_type
                              ,log_title
                              ,log_content
                              ,last_update_date
                          FROM tzq.tzq_log_ti
                         WHERE process_status = 2
                         ORDER BY log_id ASC) LOOP
    BEGIN
      v_isok:= true;
      INSERT INTO tzq.tzq_log_t
            (log_id
            ,log_type
            ,log_title
            ,log_content
            ,last_update_date)
      SELECT cur_interface.log_id
            ,cur_interface.log_type
            ,cur_interface.log_title
            ,cur_interface.log_content
            ,cur_interface.last_update_date
          ON CONFLICT ON CONSTRAINT tzq_log_t_pkey DO UPDATE
         SET log_type         = EXCLUDED.log_type
            ,log_title        = EXCLUDED.log_title
            ,log_content      = EXCLUDED.log_content
            ,last_update_date = EXCLUDED.last_update_date;
    END;
    IF (v_isok) THEN
      UPDATE tzq.tzq_log_ti ti
         SET process_status   = 4
       WHERE ti.ctid = cur_interface.row_id;
      COMMIT;
    END IF;
  END LOOP;
END $$;

三、Oracle

/* Oracle 的 merge */
MERGE INTO tzq.tzq_log_t a
USING (SELECT cur_interface.log_id
             ,cur_interface.log_type
             ,cur_interface.log_title
             ,cur_interface.log_content
             ,cur_interface.last_update_date) b
ON (a.log_id = b.log_id)
WHEN MATCHED THEN
  UPDATE
     SET log_type         = b.log_type
        ,log_title        = b.log_title
        ,log_content      = b.log_content
        ,last_update_date = b.last_update_date
WHEN NOT MATCHED THEN
  INSERT
    (a.log_id
    ,a.log_type
    ,a.log_title
    ,a.log_content
    ,a.last_update_date)
  VALUES
    (b.log_id
    ,b.log_type
    ,b.log_title
    ,b.log_content
    ,b.last_update_date);
COMMIT;