Java教程

Merge语句

本文主要是介绍Merge语句,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

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);
这篇关于Merge语句的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!