insert all和insert first测试

最新推荐文章于 2024-09-15 08:03:51 发布

clt3617

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量62

点赞数

文章标签：数据库

oracle从9i支持insert all和insert first，对涉及到多个表的批量数据插入有很大帮助。下面看看基本语法以及他们之间的区别。[@more@]

C:>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 木 10月 20 08:39:58 2011

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
--===============================
--不带条件的insert all测试
SQL> create table t_tab(table_name varchar2(30),tablespacce_name varchar2(30)) tablespace users;

Table created.

SQL> create table t_idx(table_name varchar2(30),tablespacce_name varchar2(30),index_name varchar2(30)) tablespace users;

Table created.

SQL> insert all
2 into t_tab values(table_name,tablespace_name)
3 into t_idx values(table_name,tablespace_name,index_name)
4 select * from dba_indexes;

2996 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from dba_indexes;

COUNT(*)
----------
1498

SQL> select count(*) from t_tab;

COUNT(*)
----------
1498

SQL> select count(*) from t_idx;

COUNT(*)
----------
1498

SQL>
--======================================
--带条件的insert all测试
SQL> truncate table t_tab;

Table truncated.

SQL> truncate table t_idx;

Table truncated.

SQL> alter table t_tab add object_type varchar2(30);

Table altered.

SQL> alter table t_idx add object_type varchar2(30);

Table altered.

SQL> create table t_others(object_name varchar2(30),object_type varchar2(30)) tablespace users;

Table created.

SQL> desc t_tab
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
TABLESPACCE_NAME VARCHAR2(30)
OBJECT_TYPE VARCHAR2(30)

SQL> desc t_idx
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
TABLESPACCE_NAME VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
OBJECT_TYPE VARCHAR2(30)

SQL> insert all
2 when (object_type='TABLE') then
3 into t_tab values(object_name,null,object_type)
4 when (object_type='INDEX') then
5 into t_idx values(null,null,object_name,object_type)
6 else
7 into t_others
8 select object_name,object_type from dba_objects;

11399 rows created.

SQL> select object_type,count(*) from
2 (
3 select decode(object_type,'TABLE','TABLE','INDEX','INDEX','OTHERS') object_type
4 from dba_objects
5 )
6 group by rollup(object_type)
7 ;

OBJECT COUNT(*)
------ ----------
INDEX 1345
OTHERS 8794
TABLE 1260
11399

SQL>
SQL> select count(*) from t_tab;

COUNT(*)
----------
1260

SQL> select count(*) from t_idx;

COUNT(*)
----------
1345

SQL> select count(*) from t_others;

COUNT(*)
----------
8794

SQL> commit;

Commit complete.

SQL>
--=====================================
--insert first测试
SQL> truncate table t_tab;

Table truncated.

SQL> truncate table t_idx;

Table truncated.

SQL> truncate table t_others;

Table truncated.

SQL> insert first
2 when (object_name in ('T_TAB','T_IDX','T_OTHERS') ) then
3 into t_tab values(object_name,null,object_type)
4 when (object_name in ('T_TAB','T_IDX','T_OTHERS')) then
5 into t_idx values(null,null,object_name,object_type)
6 else
7 into t_others
8 select object_name,object_type from dba_objects;

11399 rows created.

SQL> set linesize 200
SQL> select *from t_tab;

TABLE_NAME TABLESPACCE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------
T_TAB TABLE
T_IDX TABLE
T_OTHERS TABLE

SQL> select *from t_idx;

no rows selected

SQL> select count(*) from t_others;

COUNT(*)
----------
11396

SQL> commit;

Commit complete.

SQL>
--=================================
--条件和上面执行的insert first完全一样，只不过把all换成了first来测试一下
SQL> truncate table t_tab;

Table truncated.

SQL> truncate table t_idx;

Table truncated.

SQL> truncate table t_others;

Table truncated.

SQL> insert all
2 when (object_name in ('T_TAB','T_IDX','T_OTHERS') ) then
3 into t_tab values(object_name,null,object_type)
4 when (object_name in ('T_TAB','T_IDX','T_OTHERS')) then
5 into t_idx values(null,null,object_name,object_type)
6 else
7 into t_others
8 select object_name,object_type from dba_objects;

11402 rows created.

SQL> select * from t_tab;

TABLE_NAME TABLESPACCE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------
T_TAB TABLE
T_IDX TABLE
T_OTHERS TABLE
--insert first时t_idx里没有插入数据，insert all时t_idx里有和t_tab完全一样的3条数据
SQL> select * from t_idx;

TABLE_NAME TABLESPACCE_NAME INDEX_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ ------------------------------
T_TAB TABLE
T_IDX TABLE
T_OTHERS TABLE

SQL> select count(*) from t_others;

COUNT(*)
----------
11396

SQL>
--==================================
结论：insert all只要满足条件就执行,insert first是满足第一个条件执行，以后相同条件的不再
执行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1055821/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1055821/