insert all/first 使用与区别简介
来源：互联网 发布：淘宝下单微信返现 编辑：程序博客网 时间：2024/06/06 13:57
/*insert all与insert first多表插入数据需要注意和说明的地方： 一、针对insert all 只能对表执行多表插入语句，不能对视图或物化视图执行； 不能对远端表执行多表插入语句； 不能使用表集合表达式； 不能超过999个目标列； 在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时，多表插入语句不能并行执行； 多表插入语句不支持执行计划稳定性； 多表插入语句中的子查询不能使用序列。 二、insert all与insert first 有条件与无条件的区别 all:不考虑先后关系，只要满足条件，就全部插入； first:考虑先后关系，如果有数据满足第一个when条件又满足第二个when条件，则执行第一个then插入语句，第二个then就不插入第一个then已经插入过的数据了。 其区别也可描述为，all只要满足条件，可能会作重复插入；first首先要满足条件，然后筛选，不做重复插入 同时，insert all可以实现行列转换功能（insert all的旋转功能） */
---无条件插入insert allinto t1(object_name,object_id)into t2(object_name,object_id)select * from t;commit;select count(*) from t1; COUNT(*)--------- 10select count(*) from t2; COUNT(*)--------- 10---切记，如下分成2条语句的写法不等价：insert into t1(object_name,object_id) select * from t;insert into t2(object_name,object_id) select * from t;commit;
---带条件插入/*insert first/all 是对每一行来进行判断两者区别：insert first：对于每一行数据，只插入到第一个when条件成立的表，不继续检查其他条件。insert all ： 对于每一行数据，对每一个when条件都进行检查，如果满足条件就执行插入操作。 */--insert alltruncate table t1;truncate table t2;insert allwhen object_id < 5 theninto t1(object_name,object_id)when object_id >=5 theninto t2(object_name,object_id)select * from t;commit;--insert firsttruncate table t1;truncate table t2;--前面等于1的条件被<=5含在内，FIRST就表示前面插入了，后面不会再插入了。insert firstwhen object_id = 1 theninto t1(object_name,object_id)when object_id <=5 then into t2(object_name,object_id)select * from t;commit;select * from t1;OBJECT_NAME OBJECT_ID--------------------------------- ---ICOL$ 1select * from t2;OBJECT_NAME OBJECT_ID--------------------------------- ---I_USER1 2CON$ 3UNDO$ 4C_COBJ# 5--可比较如下：truncate table t1;truncate table t2;insert allwhen object_id = 1 theninto t1(object_name,object_id)when object_id <=5 then into t2(object_name,object_id)select * from t;commit;SQL> select * from t1;OBJECT_NAME OBJECT_ID--------------------------------- ---ICOL$ 1SQL> select * from t2;OBJECT_NAME OBJECT_ID--------------------------------- ---ICOL$ 1I_USER1 2CON$ 3UNDO$ 4C_COBJ# 5SQL> select * from t; OBJECT_NAME OBJECT_ID--------------------------------- --ICOL$ 1I_USER1 2CON$ 3UNDO$ 4C_COBJ# 5I_OBJ# 6PROXY_ROLE_DATA$ 7I_IND1 8I_CDEF2 9I_OBJ5 10
---行转列插入(pivoting insert)-- 现在要将上表的数据转换到下表中，insert allinto sales_info values(employee_id,week_id,sales_mon)into sales_info values(employee_id,week_id,sales_tue)into sales_info values(employee_id,week_id,sales_wed)into sales_info values(employee_id,week_id,sales_thur)into sales_info values(employee_id,week_id,sales_fri)select employee_id,week_id,sales_mon,sales_tue,sales_wed,sales_thur,sales_frifrom sales_source_data;select * from sales_source_data;EMPLOYEE_ID WEEK_ID SALES_MON SALES_TUE SALES_WED SALES_THUR SALES_FRI----------- ---------- ---------- ---------- ---------- ---------- ---------- 176 6 2000 3000 4000 5000 6000 select * from sales_info;EMPLOYEE_ID WEEK SALES----------- ---------- ---------- 176 6 2000 176 6 3000 176 6 4000 176 6 5000 176 6 6000
/*多表插入语句的限制条件: 1. 只能对表执行多表插入语句，不能对视图或物化视图执行； 2. 不能对远端表执行多表插入语句； 3. 不能使用表集合表达式； 4. 不能超过999个目标列； 5. 在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时，多表插入语句不能并行执行； 6. 多表插入语句不支持执行计划稳定性； 7. 多表插入语句中的子查询不能使用序列。*/---------------------------------------------------------------------------------------------------------------------------------------------------------------insert all 无法支持目标是视图的情况set autotrace offdrop table t ;drop table t1;drop table t2;create table t as select object_name,rownum as object_id from dba_objects where rownum<=10;create table t1 as select * from t where 1=2;create table t2 as select * from t where 1=2;create or replace view t1_v as select * from t1;create or replace view t2_v as select * from t2;insert allinto t1_v(object_name,object_id)into t2_v(object_name,object_id)select * from t;第 3 行出现错误: ORA-01702: 视图不适用于此处---------------------------------------------------------------------------------------------------------------------------------------------------------------insert all 无法支持执行计划大纲固定set autotrace offdrop table t ;drop table t1;drop table t2;create table t as select object_name,rownum as object_id from dba_objects where rownum<=10;create table t1 as select * from t where 1=2;create table t2 as select * from t where 1=2;create or replace outline outline_insert_all for category mycategory on insert allinto t1(object_name,object_id)into t2(object_name,object_id)select * from t; 第 5 行出现错误:ORA-00600: 内部错误代码, 参数: [kkmatr_0], [], [], [], [], [], [], [], [], [], [], []---------------------------------------------------------------------------------------------------------------------------------------------------------------insert all 无法支持序列插入，会导致两边不一致。drop table t purge;drop table t1 purge;drop table t2 purge;drop sequence seq_t ;create table t as select rownum as object_id,object_name from dba_objects where rownum<=10;create table t1 as select * from t where 1=2;create table t2 as select * from t where 1=2;CREATE SEQUENCE SEQ_T;CREATE OR REPLACE FUNCTION F_GETSEQ RETURN NUMBER AS V_SEQ NUMBER; BEGIN SELECT SEQ_T.NEXTVAL INTO V_SEQ FROM DUAL; RETURN V_SEQ; END;/INSERT ALL INTO T1 (object_id,object_name) INTO T2 (object_id,object_name) SELECT F_GETSEQ ID, object_name FROM T ; set linesize 1000 SELECT * FROM T1;SQL> SELECT * FROM T1; OBJECT_ID OBJECT_NAME---------- ------------- 4 ICOL$ 6 I_USER1 8 CON$ 10 UNDO$ 12 C_COBJ# 14 I_OBJ# 16 PROXY_ROLE_DATA$ 18 I_IND1 20 I_CDEF2 22 I_OBJ5 已选择10行。 SELECT * FROM T2;SQL> SELECT * FROM T2; OBJECT_ID OBJECT_NAME---------- -------------------- 5 ICOL$ 7 I_USER1 9 CON$ 11 UNDO$ 13 C_COBJ# 15 I_OBJ# 17 PROXY_ROLE_DATA$ 19 I_IND1 21 I_CDEF2 23 I_OBJ5已选择10行。-------------------------------------------------------------------------------------------------------------------------------------------------------------
0 0
- insert all/first 使用与区别简介
- insert all/first 使用与区别简介
- insert all与insert first
- INSERT FIRST和INSERT ALL的区别
- insert first&insert all的区别
- Oracle Insert first & Insert all 的区别
- insert first&insert all的区别
- INSERT ALL和INSERT FIRST的区别
- insert all insert first
- oracle insert all 和insert first 的区别
- oracle数据库insert all 和 insert first用法和区别
- Oracle多表插入insert all/insert first的区别
- ORACLE中的INSERT ALL和INSERT FIRST使用
- INSERT ALL和INSERT FIRST
- INSERT FIRST和INSERT ALL
- insert all/ insert first/ pivoting insert
- insert/insert all/insert first详解
- INSERT ALL和INSERT FIRST语法
- s2sh中beanFactory的初始化
- 网络
- eclipseRCP深入浅出（学习总结）2015.08.25
- 【java关键字-Thread】Thread.Join()的用法，及其实现线程顺序执行的原理
- 一段datepickerdialog与自定义Timepickerdialog限定时间范围的逻辑
- insert all/first 使用与区别简介
- BeautifulSoup
- 矩阵 ACdream1213 Matrix Multiplication
- Linux下VIM文本内容替换
- Groovy入门教程
- UITableView的registerClass forCellReuseIdentifier用法详解
- C++实现两个已经排序的链表进行合并
- python 设置linux环境变量
- Java中加载图片方法
