MERGE开发中遇到的问题

在使用merge开发中遇到的问题。
有一个需求，需要从基础表中取数据放到目标表，如果目标表中已经有基础表的数据了，就修改目标表中的数据，
如果没有基础表中的数据就把基础表的数据插入到目标表中。因此用到merge完成这样一个操作

成都创新互联从2013年成立，先为临淄等服务建站，临淄等地企业，进行企业商务咨询服务。为临淄企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

首先建立基本环境
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 hh34:mi:ss'), '1', to_date('19-09-2017 12:50:57', 'dd-mm-yyyy hh34: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 hh34:mi:ss'), 'admin', to_date('18-09-2017 14:33:24', 'dd-mm-yyyy hh34: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 hh34:mi:ss'), 'admin', to_date('18-09-2017 14:33:24', 'dd-mm-yyyy hh34: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 hh34:mi:ss'), 'admin', to_date('18-09-2017 14:33:42', 'dd-mm-yyyy hh34: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 hh34:mi:ss'), 'lu0.zhang', to_date('18-09-2017 16:26:23', 'dd-mm-yyyy hh34: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选择出的所有数据向目标表中
插入数据，因此插入的数据如果都是常量，必然导致重复插入的数据

当前文章：MERGE开发中遇到的问题
网页URL：http://6mz.cn/article/pjjohe.html

网站建设知识

MERGE开发中遇到的问题

其他资讯