ORACLE临时表总结 - 数据库编程

临时表概念

临时表就是用来暂时保存临时数据（亦或叫中间数据）的一个数据库对象，它和普通表有些类似，然而又有很大区别。它只能存储在临时表空间，而非用户的表空间。ORACLE临时表是会话或事务级别的，只对当前会话或事务可见。每个会话只能查看和修改自己的数据。

临时表语法

clip_image002

临时表分类

ORACLE临时表有两种类型：会话级的临时表和事务级的临时表。

1）ON COMMIT DELETE ROWS

它是临时表的默认参数，表示临时表中的数据仅在事物过程（Transaction）中有效，当事物提交（COMMIT）后，临时表的暂时段将被自动截断（TRUNCATE），但是临时表的结构以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。

2）ON COMMIT PRESERVE ROWS

它表示临时表的内容可以跨事物而存在，不过，当该会话结束时，临时表的暂时段将随着会话的结束而被丢弃，临时表中的数据自然也就随之丢弃。但是临时表的结构以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。

1:会话级的临时表的数据和你当前会话有关系，当前SESSION不退出的情况下，临时表中的数据就还存在，临时表的数据只有当你退出当前SESSION的时候才被截断（TRUNCATE TABLE），如下所示：

会话级别的临时表创建：

CREATE GLOBAL TEMPORARY TABLE TMP_TEST

(

　　　　ID NUMBER ,

　　　　NAME VARCHAR2(32)

) ON COMMIT PRESERVE ROWS;

或

CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWS

AS

SELECT * FROM TEST;

操作示例：

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST

 (

　　 ID NUMBER ,

 　　NAME VARCHAR2(32)

 ) ON COMMIT PRESERVE ROWS;

Table created

SQL> INSERT INTO TMP_TEST

    SELECT 1, 'kerry' FROM DUAL;

1 row inserted

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　NAME

---------- ----------------

1 　　　　　　　　kerry

SQL> INSERT INTO TMP_TEST

 　　SELECT 2, 'rouce' FROM DUAL;

1 row inserted

SQL> ROLLBACK;

Rollback complete

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　NAME

---------- ----------------------

1 　　　　　　　　　　kerry

SQL>

2:事务级的临时表（默认），这种类型的临时表与事务有关，当进行事务提交或者事务回滚的时候，临时表的数据将自行截断，即当COMMIT或ROLLBACK时，数据就会被TRUNCATE掉，其它的特性和会话级的临时表一致。

事务级临时表的创建方法：

CREATE GLOBAL TEMPORARY TABLE TMP_TEST

(

　　　　ID NUMBER ,

　　　　NAME VARCHAR2(32)

) ON COMMIT DELETE ROWS;

或

CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
 　　　　ID NUMBER ,
 　　　　NAME VARCHAR2(32)

 ) ON COMMIT DELETE ROWS;

Table created

SQL> INSERT INTO TMP_TEST

 　　SELECT 1, 'kerry' FROM DUAL;

1 row inserted

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　NAME

---------- ----------------------

1 　　　　　　　　　　kerry

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　  NAME

---------- ------------------------

SQL>

3:关于临时表只对当前会话或事务可见。每个会话只能查看和修改自己的数据。

用DM用户登录数据库，打开SESSION 1后，创建临时表TMP_TEST

CREATE GLOBAL TEMPORARY TABLE TMP_TEST

(

　　　　ID NUMBER ,

　　　　NAME VARCHAR2(32)

) ON COMMIT DELETE ROWS;

或

CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST

 (

 　　　　ID NUMBER ,

 　　　　NAME VARCHAR2(32)

 ) ON COMMIT DELETE ROWS;

Table created

SQL> INSERT INTO TMP_TEST

　　 SELECT 1, 'kerry' FROM DUAL;

1 row inserted

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　NAME

---------- ---------------------

1 kerry

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　NAME

---------- -----------------------

SQL>

用sys用户登录数据库，打开SESSION 2

SELECT * FROM DBA_TABLES WHERE TABLE_NAME='TMP_TEST' --可以查到临时表数据

SELECT * FROM DM.TMP_TEST; --查不到数据，即使TMP_TEST临时表存在数据。

临时表与永久表区别

SQL> SELECT TABLE_NAME, TABLESPACE_NAME,"LOGGING",

　　  "TEMPORARY", DURATION, "MONITORING"

　　 FROM DBA_TABLES WHERE TABLE_NAME IN ('TMP_TEST', 'TEST') ;

TABLE_NAME   TABLESPACE_NAME LOGGING TEMPORARY DURATION    MONITORING

------------

ORACLE临时表总结(一)

临时表概念