内容来自 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.XSBSET DEFINE OFF;Insert into SCOTT.XSB (学号,姓名,性别,出生时间,专业,总学分,备注) values ('151114','周何骏 ','男',to_date('25-9月 -98','DD-MON-RR'),'计算机 ',90,null);Insert into SCOTT.XSB (学号,姓名,性别,出生时间,专业,总学分,备注) values ('151101','王林 ','男',to_date('02-10月-97','DD-MON-RR'),'计算机 ',50,null);Insert into SCOTT.XSB (学号,姓名,性别,出生时间,专业,总学分,备注) values ('151103','王燕 ','女',to_date('10-6月 -96','DD-MON-RR'),'计算机 ',50,null);Insert into SCOTT.XSB (学号,姓名,性别,出生时间,专业,总学分,备注) values ('151202','王林 ','男',to_date('29-1月 -96','DD-MON-RR'),'通信工程 ',40,'有一门课不及格，待补考');Insert into SCOTT.XSB (学号,姓名,性别,出生时间,专业,总学分,备注) values ('151108','林一帆 ','男',to_date('08-5月 -96','DD-MON-RR'),'计算机 ',52,'已提前修完一门课');Insert into SCOTT.XSB (学号,姓名,性别,出生时间,专业,总学分,备注) values ('151204','马琳琳 ','女',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;