oracle merge into 的例子

内容来自 oracle 11g 实用教程 郑阿奇等编

在 oracle 11g中有merge语句，用于根据与原表连接的结果，对目标表执行插入，更新，或者删除操作。例如，根据在一个表中找到的差异在另一个表中插入，更新或者删除行，这种方法可以对两个表进行信息同步。语法格式如下

merge into <目标表名>

using <原表名>on <条件表达式>

when matched then {update set ...|delete}

when not matched then insert(...) values()

merge into a
 using xsb on (a.xh=xsb.学号)
 when matched then update set a.xm = xsb.姓名,a.xb=xsb.性别,a.cssj = xsb.出生时间,a.zy=xsb.专业,a.zxf=xsb.总学分,a.bz=xsb.备注
 when not matched then insert values(xsb.学号,xsb.姓名,xsb.性别,xsb.出生时间,xsb.专业,xsb.总学分,xsb.备注)

数据库

 create table a

(

 XH char(6) not null primary key,

 xM char(8) not null,

 XB char(2) not null,

 CSSJ date not null,

 ZY char(24) null,

 ZXF number(2) null,

 BZ varchar(200) null

 );

--------------------------------------------------------

--  文件已创建 - 星期日-七月-24-2016

--------------------------------------------------------

--------------------------------------------------------

--  DDL for Table XSB

--------------------------------------------------------

  CREATE TABLE "SCOTT"."XSB"

   (    "学号" CHAR(6 BYTE),

    "姓名" CHAR(8 BYTE),

    "性别" CHAR(2 BYTE) DEFAULT '男',

    "出生时间" DATE,

    "专业" CHAR(12 BYTE),

    "总学分" NUMBER(2,0),

    "备注" VARCHAR2(200 BYTE)

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ;

REM INSERTING into SCOTT.XSB

SET DEFINE OFF;

Insert into SCOTT.XSB (学号,姓名,性别,出生时间,专业,总学分,备注) values ('','周何骏  ','男',to_date('25-9月 -98','DD-MON-RR'),'计算机      ',90,null);

Insert into SCOTT.XSB (学号,姓名,性别,出生时间,专业,总学分,备注) values ('','王林    ','男',to_date('02-10月-97','DD-MON-RR'),'计算机      ',50,null);

Insert into SCOTT.XSB (学号,姓名,性别,出生时间,专业,总学分,备注) values ('','王燕    ','女',to_date('10-6月 -96','DD-MON-RR'),'计算机      ',50,null);

Insert into SCOTT.XSB (学号,姓名,性别,出生时间,专业,总学分,备注) values ('','王林    ','男',to_date('29-1月 -96','DD-MON-RR'),'通信工程    ',40,'有一门课不及格，待补考');

Insert into SCOTT.XSB (学号,姓名,性别,出生时间,专业,总学分,备注) values ('','林一帆  ','男',to_date('08-5月 -96','DD-MON-RR'),'计算机      ',52,'已提前修完一门课');

Insert into SCOTT.XSB (学号,姓名,性别,出生时间,专业,总学分,备注) values ('','马琳琳  ','女',to_date('02-10月-96','DD-MON-RR'),'通信工程    ',42,null);

--------------------------------------------------------

--  DDL for Index SYS_C0011202

--------------------------------------------------------

  CREATE UNIQUE INDEX "SCOTT"."SYS_C0011202" ON "SCOTT"."XSB" ("学号")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ;

--------------------------------------------------------

--  Constraints for Table XSB

--------------------------------------------------------

  ALTER TABLE "SCOTT"."XSB" MODIFY ("学号" NOT NULL ENABLE);

  ALTER TABLE "SCOTT"."XSB" MODIFY ("姓名" NOT NULL ENABLE);

  ALTER TABLE "SCOTT"."XSB" MODIFY ("性别" NOT NULL ENABLE);

  ALTER TABLE "SCOTT"."XSB" MODIFY ("出生时间" NOT NULL ENABLE);

  ALTER TABLE "SCOTT"."XSB" ADD PRIMARY KEY ("学号")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"  ENABLE;