MERGE開發中遇到的問題

在使用merge開發中遇到的問題。
有一個需求，需要從基礎表中取資料放到目標表，如果目標表中已經有基礎表的資料了，就修改目標表中的資料，
如果沒有基礎表中的資料就把基礎表的資料插入到目標表中。因此用到merge完成這樣一個操作

首先 建立基本環境
prompt PL/SQL Developer import file
prompt Created on 2017年9月19日 by SAMSUNG
set feedback off
set define off
prompt Creating CL_BAS...
create table CL_BAS
(
  line_id                 VARCHAR2(20) not null,
  index_item_code         VARCHAR2(4) not null,
  worst_mansge_limit_nval NUMBER not null,
  best_manage_limit_nval  NUMBER not null,
  reg_dttm                DATE not null,
  register_id             VARCHAR2(20) not null,
  update_dttm             DATE not null,
  updater_id              VARCHAR2(20) not null
)
;
comment on table CL_BAS
  is '???????';
comment on column CL_BAS.line_id
  is '?????';
comment on column CL_BAS.index_item_code
  is '??????';
comment on column CL_BAS.worst_mansge_limit_nval
  is '???????';
comment on column CL_BAS.best_manage_limit_nval
  is '???????';
comment on column CL_BAS.reg_dttm
  is '????';
comment on column CL_BAS.register_id
  is '??????';
comment on column CL_BAS.update_dttm
  is '????';
comment on column CL_BAS.updater_id
  is '??????';
alter table CL_BAS
  add constraint CL_BAS_PK primary key (LINE_ID, INDEX_ITEM_CODE);

prompt Creating LINE_BAS...
create table LINE_BAS
(
  line_id               VARCHAR2(20) not null,
  line_type_code        VARCHAR2(2) not null,
  line_name             VARCHAR2(100) not null,
  line_desc             VARCHAR2(500),
  ltdly_unit_start_days NUMBER not null,
  ltdly_unit_days       NUMBER not null,
  ltdly_unit_end_days   NUMBER not null,
  reg_dttm              DATE not null,
  register_id           VARCHAR2(20) not null,
  update_dttm           DATE not null,
  updater_id            VARCHAR2(20) not null
)
;
comment on table LINE_BAS
  is '????';
comment on column LINE_BAS.line_id
  is '?????';
comment on column LINE_BAS.line_type_code
  is '??????';
comment on column LINE_BAS.line_name
  is '????';
comment on column LINE_BAS.line_desc
  is '????';
comment on column LINE_BAS.ltdly_unit_start_days
  is '??????????';
comment on column LINE_BAS.ltdly_unit_days
  is '????????';
comment on column LINE_BAS.ltdly_unit_end_days
  is '?????????';
comment on column LINE_BAS.reg_dttm
  is '????';
comment on column LINE_BAS.register_id
  is '??????';
comment on column LINE_BAS.update_dttm
  is '????';
comment on column LINE_BAS.updater_id
  is '??????';
alter table LINE_BAS
  add constraint LINE_BAS_PK primary key (LINE_ID);

prompt Disabling triggers for CL_BAS...
alter table CL_BAS disable all triggers;
prompt Disabling triggers for LINE_BAS...
alter table LINE_BAS disable all triggers;
prompt Deleting LINE_BAS...
delete from LINE_BAS;
commit;
prompt Deleting CL_BAS...
delete from CL_BAS;
commit;
prompt Loading CL_BAS...
insert into CL_BAS (line_id, index_item_code, worst_mansge_limit_nval, best_manage_limit_nval, reg_dttm, register_id, update_dttm, updater_id)
values ('DFDF', '01', 1, 1, to_date('19-09-2017 12:50:57', 'dd-mm-yyyy hh24:mi:ss'), '1', to_date('19-09-2017 12:50:57', 'dd-mm-yyyy hh24:mi:ss'), '1');
commit;
prompt 1 records loaded
prompt Loading LINE_BAS...
insert into LINE_BAS (line_id, line_type_code, line_name, line_desc, ltdly_unit_start_days, ltdly_unit_days, ltdly_unit_end_days, reg_dttm, register_id, update_dttm, updater_id)
values ('DFDF', '02', 'dfdf', 'dfdf', 11, 1, 1, to_date('18-09-2017 14:33:24', 'dd-mm-yyyy hh24:mi:ss'), 'admin', to_date('18-09-2017 14:33:24', 'dd-mm-yyyy hh24:mi:ss'), 'admin');
insert into LINE_BAS (line_id, line_type_code, line_name, line_desc, ltdly_unit_start_days, ltdly_unit_days, ltdly_unit_end_days, reg_dttm, register_id, update_dttm, updater_id)
values ('FDFD', '01', 'dfdf', 'dfdf', 1, 1, 1, to_date('18-09-2017 14:33:24', 'dd-mm-yyyy hh24:mi:ss'), 'admin', to_date('18-09-2017 14:33:24', 'dd-mm-yyyy hh24:mi:ss'), 'admin');
insert into LINE_BAS (line_id, line_type_code, line_name, line_desc, ltdly_unit_start_days, ltdly_unit_days, ltdly_unit_end_days, reg_dttm, register_id, update_dttm, updater_id)
values ('FDFDF', '02', 'fdf', 'dfdf', 1, 1, 1, to_date('18-09-2017 14:33:42', 'dd-mm-yyyy hh24:mi:ss'), 'admin', to_date('18-09-2017 14:33:42', 'dd-mm-yyyy hh24:mi:ss'), 'admin');
insert into LINE_BAS (line_id, line_type_code, line_name, line_desc, ltdly_unit_start_days, ltdly_unit_days, ltdly_unit_end_days, reg_dttm, register_id, update_dttm, updater_id)
values ('FFFF', '04', 'ff', 'ff', 1, 1, 1, to_date('18-09-2017 16:26:23', 'dd-mm-yyyy hh24:mi:ss'), 'lu0.zhang', to_date('18-09-2017 16:26:23', 'dd-mm-yyyy hh24:mi:ss'), 'lu0.zhang');
commit;
prompt 4 records loaded
prompt Enabling triggers for CL_BAS...
alter table CL_BAS enable all triggers;
prompt Enabling triggers for LINE_BAS...
alter table LINE_BAS enable all triggers;
set feedback on
set define on
prompt Done.

執行merge時報錯 違反主鍵約束
MERGE INTO CL_BAS T1
USING LINE_BAS T2  ON (T1.LINE_ID = T2.LINE_ID
                   AND T2.LINE_ID = 'DFDF')
WHEN MATCHED THEN
  UPDATE SET T1.WORST_MANSGE_LIMIT_NVAL = 1
WHEN NOT MATCHED THEN
  INSERT
    (LINE_ID,
     INDEX_ITEM_CODE,
     WORST_MANSGE_LIMIT_NVAL,
     BEST_MANAGE_LIMIT_NVAL,
     REG_DTTM,
     REGISTER_ID,
     UPDATE_DTTM,
     UPDATER_ID)
  VALUES
    ('101',
     '102',
     1,
     1,
     SYSDATE,
     11,
     SYSDATE,
     11);
    
說明MERGE是批量新增資料，特別在插入常量資料時，並不是只插入一條資料，而是根據USING選擇出的所有資料向 目標表中
插入資料，因此插入的資料如果都是常量，必然導致重複插入的資料

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/750077/viewspace-2145181/，如需轉載，請註明出處，否則將追究法律責任。