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

来源：动视网责编：小采时间：2020-11-09 15:42:55

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

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

推荐度：

点击下载本文 文档为doc格式

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

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

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

推荐度：

点击下载本文 文档为doc格式

标签：查询今天一条

热门焦点

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

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

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

最新推荐

猜你喜欢

热门推荐