在使用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选择出的所有数据向 目标表中
插入数据，因此插入的数据如果都是常量，必然导致重复插入的数据
