今天在网上看到一个贴子，用一条语句，将数据同时插入多个表中，觉得很新奇，就自己练了一下，将语句记录下来
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');
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30150152/viewspace-1449913/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
ORACLE 删除数据文件模拟灾难恢复
下一篇：
ORACLE 11G静默安装
请登录后发表评论
登录
全部评论
最新文章
- ORA-01111,ORA-01110,ORA-01157,ORA-01111,ORA-01110
- tar:implausibly old time stamp 1970-01-01 08:00:00
- ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O
- ORA-16139: media recovery required
- SSD 下的 MySQL IO 优化
- dataguard的switchover和failover
- ORACLE ddl触发器创建同义词
- pt-table-checksum使用实践
- MySQL主库添加从库
- 修改innodb_file_per_table
