Oracle临时表（Session临时表与Transaction临时表）
一、临时表与临时表分类
临时表是在Oracle 8i中引入一种新的表的种类。Oracle数据库中的临时表是一种特殊的表，它可以作为临时保存数据的一种方式。Session级临时表是指在一个会话周期内都数据都是存在的，而一个Transaction级临时表是在Commit或Rollback之后，数据才会被清除。当然，Session结束时，Transaction级临时表数据也会被清除。
二、Session级临时表
创建Session级临时表的代码如下：
create global temporary table <临时表名>(字段列表) on commit preserve rows
大家会有疑问，这个语句里面没有Session这个关键词，怎么知道这个创建临时表的Oracle SQL语句是创建的Session级临时表呢？那么洪哥告诉你，preserve这个关键词就表示为Session级临时表。如果这里的preserve换成了delete，那就成了Transaction级临时表了。
可以做一个实验来演示一下commit提交之后，Session级临时表中的数据不会丢失。
1）创建Session级临时表，表名mytable
会话级别的临时表创建：
| 代码如下 | 复制代码 |
|
( 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）往mytalbe表中插入一条数据
| 代码如下 | 复制代码 |
| insert into mytable values('hello') |
|
3）提交commit
4）查询临时表mytable中的数据
| 代码如下 | 复制代码 |
| select * from mytalbe | |
我们可以看到，最后mytable中是有数据的。这说明commit之后，mytable这个Session级临时表中数据并没有消失。但我们结束Session，重新登录，再查询数据select * from mytable，这时候记录已不存在了，因为系统在结束Session时自动清除了记录。
三、Transaction级临时表
创建Transaction级临时表的代码如下：
| 代码如下 | 复制代码 |
|
create global temporary table <临时表名>(字段列表) on commit delete rows |
|
大家看看，这里与Session级临时表创建时的唯一的区别就是把preserve换成了delete。
在测试环节，大家可以像上面Session级临时表一样，创建一个Transaction级临时表，再往里面写入数据。然后在commit之后再查看一下表中是否内容还存在。正确的结果应该是Transaction级临时表在commit之后，数据被清除了。
事务级临时表的创建方法：
| 代码如下 | 复制代码 |
|
( 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 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
| 代码如下 | 复制代码 |
|
( 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临时表存在数据。
两中类型临时表的区别
会话级临时表采用 on commit preserve rows ;而事务级则采用 on commit delete rows ;用法上，会话级别只有当会话结束临时表中的数据才会被截断，而且事务级临时表则不管是 commit 、 rollback 或者是会话结束，临时表中的数据都将被截断
4 )什么时候使用临时表
1 )、当某一个 SQL 语句关联的表在 2 张及以上，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中
2 )、程序执行过程中可能需要存放一些临时的数据，这些数据在整个程序的会话过程中都需要用的等等。
3 . 例子：略
4 .临时表的不足之处
1 )不支持 lob 对象，这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时就无法使用临时表了。
2 )不支持主外键关系
所以，由于以上原因，我们可以自己创建临时表，以弥补 oracle 临时表的不足之处
上面的都是本人经过测试的，但下面是在网上搜索到的方法，本人具体没有测试过，不过觉得可行性很强，有时间测试下。
创建方法：
1 、以常规表的形式创建临时数据表的表结构，但要在每一个表的主键中加入一个 SessionID 列以区分不同的会话。(可以有 lob 列和主外键)
2 、写一个用户注销触发器，在用户结束会话的时候删除本次会话所插入的所有记录 (SessionID 等于本次会话 ID 的记录 ) 。
3 、程序写入数据时，要顺便将当前的会话 ID(SessionID) 写入表中。
4 、程序读取数据时，只读取与当前会话 ID 相同的记录即可。
功能增强的扩展设计：
1 、可以在数据表上建立一个视图，视图对记录的筛选条件就是当前会话的SessionID 。
2 、数据表中的SessionID 列可以通过Trigger 实现，以实现对应用层的透明性。
3 、高级用户可以访问全局数据，以实现更加复杂的功能。
扩展临时表的优点：
1 、实现了与Oracle 的基于会话的临时表相同的功能。
2 、支持SDO_GEOMETRY 等lob 数据类型。
3 、支持表间的主外键连接，且主外键连接也是基于会话的。
4 、高级用户可以访问全局数据，以实现更加复杂的功能
