• 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 ('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;
  • 相关阅读:
    spoj LCS2
    spoj SUBLEX
    spoj NSUBSTR
    bzoj 2882: 工艺【SAM】
    poj 3294 Life Forms【SA+二分】
    poj 3415 Common Substrings【SA+单调栈】
    poj 2774 Long Long Message【SA】
    poj 2406 Power Strings【kmp】
    poj 1743 Musical Theme【二分+SA】
    hdu 3622 Bomb Game【二分+2-SAT+tarjan】
  • 原文地址:https://www.cnblogs.com/rocky-AGE-24/p/5699949.html
Copyright © 2020-2023  润新知