ORACLESQL一条语句同时插入多个表

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');