Merge语句

概念&用处

用来合并update和insert语句

通过merge语句，根据一张表（原数据表，source table）对另外一张（目标表，target table）表进行查询，连接条件匹配上的进行update，无法匹配的执行INSERT。

登录

sqlplus / as sysdba
sqlplus scott/tiger

构建平台sql语句

create table KC
(
  kch  VARCHAR2(3) not null,
  kcm  VARCHAR2(16) not null,
  kkxq NUMBER(1) not null,
  xs   NUMBER(2) not null,
  xf   NUMBER(1)
)


create table XS
(
  xh   VARCHAR2(6),
  xm   VARCHAR2(6),
  zym  VARCHAR2(6),
  xb   VARCHAR2(2),
  cssj DATE,
  zxf  NUMBER(2),
  bz   VARCHAR2(20)
)



create table XS_KC
(
  xh  CHAR(6) not null,
  kch CHAR(6) not null,
  cj  INTEGER,
  xf  NUMBER(2)
)



insert into KC (kch, kcm, kkxq, xs, xf)
values ('001', '001', 1, 11, 1);
insert into KC (kch, kcm, kkxq, xs, xf)
values ('001', '001', 1, 11, 1);


insert into XS (xh, xm, zym, xb, cssj, zxf, bz)
values ('061101', '王林', '计算机', '男', to_date('10-02-1986', 'dd-mm-yyyy'), 50, null);
insert into XS (xh, xm, zym, xb, cssj, zxf, bz)
values ('101112', '李明', '计算机', '男', to_date('30-01-1986', 'dd-mm-yyyy'), 36, null);
insert into XS (xh, xm, zym, xb, cssj, zxf, bz)
values ('001', '张琼', '计算机', null, null, 45, '三好学生');
insert into XS (xh, xm, zym, xb, cssj, zxf, bz)
values ('121112', '王小二', '计算机', '男', to_date('30-01-1986', 'dd-mm-yyyy'), 36, null);



insert into XS_KC (xh, kch, cj, xf)
values ('061101', '101   ', 80, null);
insert into XS_KC (xh, kch, cj, xf)
values ('061101', '102   ', 78, null);
insert into XS_KC (xh, kch, cj, xf)
values ('061101', '206   ', 76, null);
insert into XS_KC (xh, kch, cj, xf)
values ('061103', '101   ', 82, null);
insert into XS_KC (xh, kch, cj, xf)
values ('061103', '102   ', 82, null);
insert into XS_KC (xh, kch, cj, xf)
values ('061103', '206   ', 83, null);
insert into XS_KC (xh, kch, cj, xf)
values ('061104', '101   ', 90, null);
insert into XS_KC (xh, kch, cj, xf)
values ('061107', '101   ', 98, null);
insert into XS_KC (xh, kch, cj, xf)
values ('061107', '102   ', 80, null);
commit;

使用MERGE语句XS1表中新增的数据插入表XS中：

create table xs1 as select * from xs;

insert into xs1(xh,xm,zym,xb,cssj,zxf) values('007','test','计算机','男',to_date('19900130','YYYYMMDD'),36)

--使用MERGE语句XS_JSJ表中新增的数据插入表XS中：
merge into xs a using xs1 b on(a.xh=b.xh) 
when not matched then 
insert(a.xh,a.xm,a.zym,a.xb,a.cssj,a.zxf)
values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf);

构建平台

create table PRODUCTS
    (
    PRODUCT_ID             INTEGER,
    PRODUCT_NAME      VARCHAR2(60),
    CATEGORY                VARCHAR2(60)
    );



create table NEWPRODUCTS
    (
    PRODUCT_ID                 INTEGER,
    PRODUCT_NAME          VARCHAR2(60),
    CATEGORY                    VARCHAR2(60)
    );

insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
commit;


insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
commit;

products为目标表，newproducts为源表，则若产品号相匹配，根据源表信息修改目标表的产品名(product_name)和产品类别（category）

merge into products p 
using newproducts np 
 on (p.product_id = np.product_id) when matched then 
 update set p.product_name=np.product_name, p.category=np.category;

若产品号不匹配，则根据源表将新的纪录添加到目标表

merge into products  p using newproducts np on (p.product_id=np.product_id)
when not matched then insert values(np.product_id,np.product_name,np.category);

增删改

merge into products  p using newproducts np on (p.product_id=np.product_id)
when matched then update set p.product_name= np.product_name,
	p.category = np.category
	delete where(p.category='ELECTRNCS')
when not matched then insert values(np.product_id,np.product_name,np.category);