本文共 4996 字，大约阅读时间需要 16 分钟。
在使用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选择出的所有数据向 目标表中 插入数据，因此插入的数据如果都是常量，必然导致重复插入的数据
转载地址：http://ssbql.baihongyu.com/