Oracle临时表on commit preserver rows和on commit delete rows区别

转载

mb5ff591cb6ec96 2015-02-13 11:46:00

文章标签 sql 数据 oracle 存储过程软件系统 文章分类 Java 后端开发

Oracle 临时表：在 Oracle 中创建一张表，这个表不用于其他的什么功能，主要用于自己的软件系统一些特有功能才用的，而当你用完之后表中的数据就没用了。 Oracle 的临时表创建之后基本不占用表空间，如果你没有指定临时表(包括临时表的索引)存放的表空的时候，你插入到临时表的数据是存放在 ORACLE 系统的临时表空间中( TEMP )。
1 、临时表的创建
创建Oracle 临时表，可以有两种类型的临时表：
会话级的临时表
事务级的临时表。
1) 会话级的临时表因为这这个临时表中的数据和你的当前会话有关系，当你当前SESSION 不退出的情况下，临时表中的数据就还存在，而当你退出当前SESSION 的时候，临时表中的数据就全部没有了，当然这个时候你如果以另外一个SESSION 登陆的时候是看不到另外一个SESSION 中插入到临时表中的数据的。即两个不同的SESSION 所插入的数据是互不相干的。当某一个SESSION 退出之后临时表中的数据就被截断(truncate table ，即数据清空)了。会话级的临时表创建方法：

Create Global Temporary Table Table_Name (Col1 Type1,Col2 Type2...) On Commit Preserve Rows ;

举例：

create global temporary table Student (
Stu_id Number(5),
Class_id Number(5),
Stu_Name Varchar2(8),
Stu_Memo varchar2(200)) on Commit Preserve Rows ;

2) 事务级临时表是指该临时表与事务相关，当进行事务提交或者事务回滚的时候，临时表中的数据将自行被截断，其他的内容和会话级的临时表的一致(包括退出SESSION 的时候，事务级的临时表也会被自动截断)。事务级临时表的创建方法：

Create Global Temporary Table Table_Name (Col1 Type1,Col2 Type2...) On Commit Delete Rows ;

举例：
create global temporary table Classes (
Class_id Number(5),
Class_Name Varchar2(8),
Class_Memo varchar2(200)) on Commit delete Rows ;

3) 两中类型临时表的区别
会话级临时表采用 on commit preserve rows ;而事务级则采用 on commit delete rows ;用法上，会话级别只有当会话结束临时表中的数据才会被截断，而且事务级临时表则不管是 commit 、 rollback 或者是会话结束，临时表中的数据都将被截断
4 )什么时候使用临时表
1 )、当某一个 SQL 语句关联的表在 2 张及以上，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中
2 )、程序执行过程中可能需要存放一些临时的数据，这些数据在整个程序的会话过程中都需要用的等等。
2 .临时表的不足之处
1 )不支持 lob 对象，这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时就无法使用临时表了。
2 )不支持主外键关系。

--基于会话的临时表
对于基于会话的临时表，断开连接后，临时表中的数据被清空。

C:\Users\Administrator>SQLPLUS lxj/rusky@rusky

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 2月 13 11:29:12 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create global temporary table temp_table_session (id int, value varchar2(10)) on commit preserve rows;
Table created

SQL> insert into temp_table_session values(1,111); ---插入数据
1 row inserted

SQL> insert into temp_table_session values(2,112);
1 row inserted


SQL> select * from temp_table_session; --查看数据
ID VALUE
--------------------------------------- ----------
1 111
2 112

SQL> commit; --commit提交
Commit complete

SQL> select * from temp_table_session;
ID VALUE
--------------------------------------- ----------
1 111
2 112

SQL> exit --退出当前会话，重新连接
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

C:\Users\Administrator>SQLPLUS lxj/rusky@rusky

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 2月 13 11:31:22 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from temp_table_session;

未选定行

--基于事务的临时表
对于基于会话的临时表，断开连接后，临时表中的数据被清空。

SQL> create global temporary table temp_table_transaction(id int, value varchar2(10)) on commit delete rows;
Table created

SQL> insert into temp_table_transaction values(1,333);

已创建 1 行。

SQL> insert into temp_table_transaction values(2,444);

已创建 1 行。

SQL> select * from temp_table_transaction;

ID VALUE
---------- ----------
1 333
2 444

SQL> commit;

提交完成。

SQL> select * from temp_table_transaction;

未选定行

SQL>