ORACLE SQL一条语句同时插入多个表

php中文网

发布时间：2016-06-07 15:24:25

2029人浏览过

来源于php中文网

原创

今天在网上看到一个贴子，用一条语句，将数据同时插入多个表中，觉得很新奇，就自己练了一下，将语句记录下来 STEP1：创建三个表，用于实验测试： CREATE TABLE T_TABLE(OBJECT_ID NUMBER, OBJECT_NAME VARCHAR2(128), OBJECT_TYPE VARCHAR2(19));CREATE TAB

今天在网上看到一个贴子，用一条语句，将数据同时插入多个表中，觉得很新奇，就自己练了一下，将语句记录下来

STEP1：创建三个表，用于实验测试：

CREATE TABLE T_TABLE(OBJECT_ID NUMBER, OBJECT_NAME VARCHAR2(128), OBJECT_TYPE VARCHAR2(19));
CREATE TABLE T_INDEX(OBJECT_ID NUMBER, OBJECT_NAME VARCHAR2(128), OBJECT_TYPE VARCHAR2(19));
CREATE TABLE T_VIEW(OBJECT_ID NUMBER, OBJECT_NAME VARCHAR2(128), OBJECT_TYPE VARCHAR2(19));

STEP2：向三个表中插入数据

INSERT ALL 
      INTO T_TABLE(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
      VALUES(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
      INTO T_INDEX(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
      VALUES(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
      INTO T_VIEW(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
      VALUES(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
      SELECT OBJECT_ID, OBJECT_NAME,OBJECT_TYPE 
      FROM DBA_OBJECTS WHERE OBJECT_TYPE IN('VIEW','TABLE','INDEX');

STEP3：清空三张表，分类插入数据，即带条件插入

TRUNCATE TABLE T_TABLE;
TRUNCATE TABLE T_INDEX;
TRUNCATE TABLE T_VIEW;

将数据分类插入

INSERT ALL 
WHEN   OBJECT_TYPE='TABLE' THEN 
       INTO T_TABLE(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
       VALUES(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
WHEN   OBJECT_TYPE='VIEW' THEN 
       INTO T_INDEX(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
       VALUES(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
ELSE   INTO T_VIEW(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
       VALUES(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('VIEW','TABLE','INDEX');

STEP4：清空三张，INSERT FIRST，即带条件插入

INSERT FIRST 
WHEN   OBJECT_ID<1000 THEN 
       INTO T_TABLE(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
       VALUES(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
WHEN   OBJECT_ID>=1000 AND OBJECT_ID<2000 THEN 
       INTO T_INDEX(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
       VALUES(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
ELSE   INTO T_VIEW(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
       VALUES(OBJECT_ID, OBJECT_NAME,OBJECT_TYPE)
SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('VIEW','TABLE','INDEX');