文档章节

insert all 语句 用法详解

underA
 underA
发布于 2013/03/25 17:19
字数 2144
阅读 202
收藏 5
INSERT ALL语句的一些使用技巧。


首先描述一下需求,其实要进行的操作很简单:从一张表取数据插入到另一张表中,插入的目标表做了一个应用系统级的日志表,也就是说在插入目标表的同时,还需要将相同的数据插入到日志表中。

这个操作其实并不复杂,但是想找到一个效率最高、并发性最好的方法并不容易。

最普通的方法是两个INSERT INTO SELECT语句。这种方法编码最简单,但是存在着两次插入的数据不一致的情况。如果要解决这个问题,必须通过人为加锁的方式,这样又会影响并发性。

还有一种方式是通过临时表的方式。第一次将数据放到临时表中,然后通过临时表把数据分别插入目标表和日志表。这种方法虽然解决了并发性问题,但是效率比较低。相同的数据需要查询三次,插入三次。

PL/SQL的语法RETURNING语句其实很适合这种情况,可惜的是RETURNING语句只支持INSERT INTO VALUES语句,不支持INSERT INTO SELECT语句。

如果数据量不大的话,还可以考虑使用SELECT BULK COLLECT INTO和FOR ALL INSERT语句配合。如果数据量比较大的话,可以考虑在上面的基础上加上LIMIT语句限制一次处理的数据量大小。这种方法不但解决了并发性而且只需要读取一次插入两次,执行效率相对比较高。唯一的缺点是,需要将数据放到内存的变量中,不但需要额外的内存空间,而且这种数据在内存中的中转必然要比数据从源表直接插入到目标表效率要低一些。而且这种方法需要的编码量相对较大。

最后想到了使用INSERT ALL语法。INSERT ALL语法是9i的新功能,使用INSERT ALL语法可以说是解决这个方法的最佳途径了,只需要读取一次,就可以完成两次插入,没有并发性问题,不需要额外的存储空间,编码简单,只需要一条SQL语句就可以搞定。

从上面种种方面看,INSERT ALL语句简直就是这个问题的完美解决方案,但是问题才刚刚开始。

首先,碰到的第一个问题就是,INSERT ALL的子查询中不支持序列。而在将源表数据插入到目标表的过程中需要使用序列来构造ID。

不过这个问题被我们通过建立函数的方法绕过去了。

下面这个例子简单描述了这种情况:

SQL> CREATE TABLE A (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE LOG_A (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> CREATE SEQUENCE SEQ_TEST;

序列已创建。

SQL> INSERT ALL INTO A (ID, NAME) VALUES (ID, TNAME) 
2 INTO LOG_A (ID, NAME) VALUES (ID, TNAME) 
3 SELECT SEQ_TEST.NEXTVAL ID, TNAME FROM TAB;
SELECT SEQ_TEST.NEXTVAL ID, TNAME FROM TAB
*第 3 行出现错误:
ORA-02287: 此处不允许序号

Oracle的文档上也明确描述了不能在子查询中使用序列,但是通过测试发现,如果将序列封装在函数中是可以骗过Oracle的。

SQL> CREATE OR REPLACE FUNCTION F_GETSEQ RETURN NUMBER AS
2 V_SEQ NUMBER;
3 BEGIN
4 SELECT SEQ_TEST.NEXTVAL INTO V_SEQ FROM DUAL;
5 RETURN V_SEQ;
6 END;
7 /

函数已创建。

SQL> INSERT ALL INTO A (ID, NAME) VALUES (ID, TNAME) 
2 INTO LOG_A (ID, NAME) VALUES (ID, TNAME) 
3 SELECT F_GETSEQ ID, TNAME FROM TAB;

已创建48行。

问题似乎解决了,但是更大的问题出现了,观察A表和LOG_A表发现居然得到的结果是不一样的:

SQL> SELECT * FROM A;

ID NAME
---------- ------------------------------
1 DEPT
3 EMP
5 BONUS
7 SALGRADE
9 DUMMY
11 TEST
13 DOCS
15 DR$MYINDEX$I
17 DR$MYINDEX$K
19 DR$MYINDEX$R
21 DR$MYINDEX$N
23 TEST_CLOB
25 FACT
27 MLOG$_DIM_A
29 MLOG$_DIM_B
31 MLOG$_FACT
33 MV_FACT
35 MLOG$_MV_FACT
37 RUPD$_MV_FACT
39 A
41 LOG_A
43 TEST_TAB
45 DIM_A
47 DIM_B

已选择24行。

SQL> SELECT * FROM LOG_A;

ID NAME
---------- ------------------------------
2 DEPT
4 EMP
6 BONUS
8 SALGRADE
10 DUMMY
12 TEST
14 DOCS
16 DR$MYINDEX$I
18 DR$MYINDEX$K
20 DR$MYINDEX$R
22 DR$MYINDEX$N
24 TEST_CLOB
26 FACT
28 MLOG$_DIM_A
30 MLOG$_DIM_B
32 MLOG$_FACT
34 MV_FACT
36 MLOG$_MV_FACT
38 RUPD$_MV_FACT
40 A
42 LOG_A
44 TEST_TAB
46 DIM_A
48 DIM_B

已选择24行。

SQL> ROLLBACK;

回退已完成。

感觉上Oracle居然似乎对源表进行了两次查询。但是从数据的分布情况上看又不像。个人感觉Oracle对于每条记录似乎是将取序列的函数执行了两次。

操作流程类似于

FOR ALL ROWID IN TAB LOOP

SELECT TNAME FROM TAB WHERE ROWID =:1;

INSERT INTO A (F_GETSEQ, TNAME);

INSERT INTO LOG_A (F_GETSEQ, TNAME);

END LOOP;

而同事又有了另一个发现,当包含了ROWNUM列时,得到的结果是正确的:

SQL> INSERT ALL INTO A (ID, NAME) VALUES (ID, TNAME) 
2 INTO LOG_A (ID, NAME) VALUES (ID, TNAME) 
3 SELECT ROWNUM RN, F_GETSEQ ID, TNAME FROM TAB;

已创建48行。

SQL> SELECT * FROM A;

ID NAME
---------- ------------------------------
49 DEPT
50 EMP
51 BONUS
52 SALGRADE
53 DUMMY
54 TEST
55 DOCS
56 DR$MYINDEX$I
57 DR$MYINDEX$K
58 DR$MYINDEX$R
59 DR$MYINDEX$N
60 TEST_CLOB
61 FACT
62 MLOG$_DIM_A
63 MLOG$_DIM_B
64 MLOG$_FACT
65 MV_FACT
66 MLOG$_MV_FACT
67 RUPD$_MV_FACT
68 A
69 LOG_A
70 TEST_TAB
71 DIM_A
72 DIM_B

已选择24行。

SQL> SELECT * FROM LOG_A;

ID NAME
---------- ------------------------------
49 DEPT
50 EMP
51 BONUS
52 SALGRADE
53 DUMMY
54 TEST
55 DOCS
56 DR$MYINDEX$I
57 DR$MYINDEX$K
58 DR$MYINDEX$R
59 DR$MYINDEX$N
60 TEST_CLOB
61 FACT
62 MLOG$_DIM_A
63 MLOG$_DIM_B
64 MLOG$_FACT
65 MV_FACT
66 MLOG$_MV_FACT
67 RUPD$_MV_FACT
68 A
69 LOG_A
70 TEST_TAB
71 DIM_A
72 DIM_B

已选择24行。

SQL> ROLLBACK;

回退已完成。

这次执行的结果是正确的。Tom在他的书中描述过ROWNUM的确定结果集的功能,也就是说受到ROWNUM的影响,ORACLE将处理流程变成了

FOR ALL ROWID IN TAB LOOP

SELECT ROWNUM RN, F_GETSEQ ID, TNAME FROM TAB WHERE ROWID =:1;

INSERT INTO A (ID, TNAME);

INSERT INTO LOG_A (ID, TNAME);

END LOOP;

由于存在ROWNUM,Oracle在执行查询的时候就运行了F_GETSEQ函数,因此F_GETSET函数对于每条记录只在查询的时候执行一次。

如果将函数改写一下,将ROWNUM作为输入参数,一样可以解决这个问题。

SQL> CREATE OR REPLACE FUNCTION F_GETSEQ (P_IN IN NUMBER) RETURN NUMBER AS
2 V_SEQ NUMBER;
3 BEGIN
4 SELECT SEQ_TEST.NEXTVAL INTO V_SEQ FROM DUAL;
5 RETURN V_SEQ;
6 END;
7 /

函数已创建。

SQL> INSERT ALL INTO A (ID, NAME) VALUES (ID, TNAME) 
2 INTO LOG_A (ID, NAME) VALUES (ID, TNAME) 
3 SELECT F_GETSEQ(ROWNUM) ID, TNAME FROM TAB;

已创建48行。

SQL> SELECT * FROM A WHERE ROWNUM < 5;

ID NAME
---------- ------------------------------
73 DEPT
74 EMP
75 BONUS
76 SALGRADE

SQL> SELECT * FROM LOG_A WHERE ROWNUM < 5;

ID NAME
---------- ------------------------------
73 DEPT
74 EMP
75 BONUS
76 SALGRADE

SQL> ROLLBACK;

回退已完成。

除了上面描述的方法,如果是Oracle10g的话,还可以建立一个DETERMINISTIC的函数。在10g中Oracle完全信任DETERMINISTIC声明,对于相同的输入,会采用相同的输出,而不去真正的执行函数。

例如,在9i下执行:

SQL> CREATE OR REPLACE FUNCTION F_GETSEQ RETURN NUMBER DETERMINISTIC AS
2 V_SEQ NUMBER;
3 BEGIN
4 SELECT SEQ_TEST.NEXTVAL INTO V_SEQ FROM DUAL;
5 RETURN V_SEQ;
6 END;
7 /

Function created.

SQL> SELECT F_GETSEQ FROM TAB;

F_GETSEQ
----------
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219

25 rows selected.

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

而10g中,上面的查询变成了:

SQL> CREATE OR REPLACE FUNCTION F_GETSEQ RETURN NUMBER DETERMINISTIC AS
2 V_SEQ NUMBER;
3 BEGIN
4 SELECT SEQ_TEST.NEXTVAL INTO V_SEQ FROM DUAL;
5 RETURN V_SEQ;
6 END;
7 /

函数已创建。

SQL> SELECT F_GETSEQ FROM TAB;

F_GETSEQ
----------
97
97
97
97
97
97
97
97
97
97
97
97
97
97
97
97
97
97
97
97
97
97
97
97

已选择24行。

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

因此,在10g中还可以通过建立一个DETERMINISTIC属性的函数来解决这个问题,在函数调用过程中输入主键或者ROWID来唯一标识每条记录。由于TAB是系统视图,不包含ROWID信息,需要建立一张新表:

SQL> CREATE TABLE TEST_TAB AS SELECT * FROM TAB;

表已创建。

SQL> CREATE OR REPLACE FUNCTION F_GETSEQ(P_ID IN ROWID) RETURN NUMBER DETERMINISTIC AS
2 V_SEQ NUMBER;
3 BEGIN
4 SELECT SEQ_TEST.NEXTVAL INTO V_SEQ FROM DUAL;
5 RETURN V_SEQ;
6 END;
7 /

函数已创建。

SQL> INSERT ALL INTO A (ID, NAME) VALUES (ID, TNAME) 
2 INTO LOG_A (ID, NAME) VALUES (ID, TNAME) 
3 SELECT F_GETSEQ(ROWID) ID, TNAME FROM TEST_TAB;

已创建48行。

SQL> SELECT * FROM A WHERE ROWNUM < 5;

ID NAME
---------- ------------------------------
98 DEPT
99 EMP
100 BONUS
101 SALGRADE

SQL> SELECT * FROM LOG_A WHERE ROWNUM < 5;

ID NAME
---------- ------------------------------
98 DEPT
99 EMP
100 BONUS
101 SALGRADE

至此,这个数据插入问题已经全部解决了。

本文转载自:http://hi.baidu.com/graceyan/item/80cbe89cfd590cbb82d295a7

underA
粉丝 13
博文 143
码字总数 41595
作品 0
海淀
程序员
私信 提问
mysql LAST_INSERT_ID详解

LASTINSERTID() LASTINSERTID(expr) 自动返回最后一个INSERT或 UPDATE 问询为 AUTO_INCREMENT列设置的第一个 发生的值。 mysql> SELECT LASTINSERTID(); -> 195 产生的ID 每次连接后保存在服...

刘德生
2013/09/11
296
0
Merge into的使用详解

Merge是一个非常有用的功能,类似于Mysql里的insert into on duplicate key. Oracle在9i引入了merge命令, 通过这个merge你能够在一个SQL语句中对一个表同时执行inserts和updates操作. 当然是...

mrliuze
2015/07/20
191
0
WordPress数据库操作函数详解

通过这个$wpdb对象,我们可以对WordPress数据库进行任何操作,包括建表、查询、删除、更新等。要注意的是,如果要在自定义函数中使用$wpdb,必须先将其全局化(global $wpdb;)。 下面就详细介...

郭奕峰
2015/05/29
914
0
PostgreSQL培训系列直播—第四章:应用开发者指南

内容概要 1、基本SQL语句用法 2、数据类型、操作符 3、数据库对象类型 4、内置函数 5、自定义函数sql, plpgsql 6、高级SQL用法与应用场景 7、事务隔离级别 8、锁 9、触发器、事件触发器、规则...

德哥
04/09
0
0
oracle-merge用法详解

转自 http://blog.chinaunix.net/u/12457/showart_346642.html oracle-merge用法详解 Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作. MERGE命令从一个...

晨曦之光
2012/04/25
136
0

没有更多内容

加载失败,请刷新页面

加载更多

川普给埃尔多安和内堪尼亚胡的信

任性 https://twitter.com/netanyahu/status/1186647558401253377 https://edition.cnn.com/2019/10/16/politics/trump-erdogan-letter/index.htm...

Iridium
8分钟前
3
0
golang-mysql-原生

db.go package mainimport ("database/sql""time"_ "github.com/go-sql-driver/mysql")var (db *sql.DBdsn = "root:123456@tcp(127.0.0.1:3306)/test?charset=u......

李琼涛
36分钟前
2
0
编程作业20191021092341

1编写一个程序,把用分钟表示的时间转换成用小时和分钟表示的时 间。使用#define或const创建一个表示60的符号常量或const变量。通过while 循环让用户重复输入值,直到用户输入小于或等于0的值...

1李嘉焘1
36分钟前
4
0
Netty整合Protobuffer

现在我们都知道,rpc的三要素:IO模型,线程模型,然后就是数据交互模型,即我们说的序列化和反序列化,现在我们来看一下压缩比率最大的二进制序列化方式——Protobuffer,而且该方式是可以跨...

算法之名
42分钟前
18
0
如何用C++实现栈

栈的定义 栈(stack)又名堆栈,它是一种运算受限的线性表。限定仅在表尾进行插入和删除操作的线性表。这一端被称为栈顶,相对地,把另一端称为栈底。向一个栈插入新元素又称作进栈、入栈或压...

BWH_Steven
今天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部