创建和使用临时表详解

创建和使用临时表详解：

 

ORACLE的临时表在应用系统中有很大的作用，它可以让用户只能够操作各自的数据中而互不干扰，不用担心会破坏或影响其他SESSION/TRANSACTION的数据，这也是数据安全的一种解决方法。

临时表包含所有会话都可以访问的定义，但其中的行是插入行的会话专用的。编程人员可以将它作为私有存储区用于操纵大量的数据，语法如下：

Create global  temporary table temp_tab_name

(column datatype[,clomndatatype])

[on commit {delete| preserve } rows]

 

列定义和普通表没有差别，但确实可以通过子查询来提供。末尾的可选子句确定插入任何行的生命周期；默认方式：在插入行的事务完成之时，删除相应的行，但也可以更改此行行为，以便将他们保留到插入行的会话结束为止，无论选择那个选项，数据都专用于每个会话：不同用户可以将自己的行插入自己的表的副本中，谁都看不到其他人的行；

 

临时表都与永久表类似。二者区别在于：临时表的数据是临时的，专用于相应的会话，针对其执行的所有sql命令的速度远比针对永久表的命令快;

针对其执行的所有sql命令的速度远比针对永久表的命令快：

（1）   临时表不是永久表空间里的段，理想状态下他们仅存在于使用他们的PGA会话中，因此不涉及磁盘活动，甚至不涉及数据库缓冲区缓存活动。如果PGA无法扩大到存储临时表的程度（如果插入数百万行，可能出现这种情况，但在复杂报表的生成过程中，这极少出现），此后表将写出到用户临时表空间的临时段中。临时表空间上的I/O远比永久表空间上的I/O速度快，原因是临时表空间不使用数据库缓冲区缓存，而全部由会话的服务器进程直接在磁盘上执行；

（2）   针对临时表的DML不生成重做数据，由于数据尽在会话生存周期（可能仅是事务期间）保留，就没必要生成重做数据。这带来了双重好处：针对表的会话的DML更快。

 

临时表分为SESSION、TRANSACTION两种，SESSION级的临时表数据在整个SESSION都存在，直到结束此次SESSION；而TRANSACTION级的临时表数据在TRANACTION结束后消失，即COMMIT/ROLLBACK或结束SESSION都会清除TRANACTION临时表数据。

两种临时表的语法：

    create global temporary table 临时表名 on commit preserve|delete rows 用preserve时就是SESSION级的临时表，用delete就是TRANSACTION级的临时表

一、SESSION级临时表

1、建立临时表

create globaltemporary table temp_test1(a varchar2(30)) 

on commit preserverows 

2、插入数据

insert into temp_testvalues('test session') 

3、提交commit；

4、查询数据

select *from temp_test

测试数据'test session'还在，因为该临时表是在session级别的。

结束SESSION，打开另一个session，再查询数据select *from temp_test1，这时候记录已不存在，因为系统在结束SESSION时自动清除记录

 

二、TRANSACTION级临时表

1、建立临时表

Sql代码

create globaltemporary table temp_test2 (a varchar2(30)) 

on commit deleterows 

2、插入数据

insert into temp_test2values('test transaction ') 

3、提交

    commit

4、查询数据

select *fromtemp_test2 

 

这个时候事务已经结束（commit），可以可以看到刚才插入的记录'test transaction'已经不存在；当然，如果不提交而直接结束SESSION，重新登录该记录同样不存在，因为session结束会有一个默认commit操作，导致事务结束！！！

 

 

实验：

1.     以hr用户连接到数据库

23:34:29 sys@felixSQL>conn  hr/oracle

Connected.

 

2.     以以下方式建表

23:34:34 hr@felix SQL>createglobal temporary table tmp_emps on commit preserve rows as select * fromemployees where 1=2;

 

Table created.

3.     插入一些行

23:36:55 hr@felix SQL>insert intotmp_emps select * from employees where department_id=30;

 

6 rows created.

 

23:39:29 hr@felixSQL>commit;

 

4.     Commit complete.以hr用户再打开一个会话

[oracle@felix ~]$ sqlplushr/oracle

5.     在第二个绘画中确认第一个会话中的第一个插入已经提交但是不可见：

 

23:37:47 hr@felixSQL>select count(*) from tmp_emps;                    

 

  COUNT(*)

----------

         0

 

23:38:41 hr@felixSQL>insert into tmp_emps select * from employees where department_id=50;

 

45 rows created.

 

23:39:29 hr@felixSQL>commit;

 

Commit complete.

 

6.     第一个会话中，将表截断：

 

23:40:31 hr@felixSQL>truncate table tmp_emps;

 

Table truncated.

 

7.     在第二个会话中，确认表的会话副本仍然包含行：

23:39:33 hr@felix SQL>select *from tmp_emps;

……..

……..

……..

45 rows selected.

8.     在第二个会话中演示会话的终止并不会清除行，这需要断开连接并再次连接：

23:42:25 hr@felix SQL>disconnect;

Disconnected from Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

23:42:47 hr@felix SQL>connect hr/oracle

Connected.

 

23:42:55 hr@felixSQL>select count(*) from tmp_emps;

 

  COUNT(*)

----------

         0

 

9.     在两个会话中通过删除表来清理

23:43:19 hr@felixSQL>truncate table tmp_emps;

 

Table truncated.

 

23:54:39 hr@felixSQL>drop table tmp_emps purge;

 

Table dropped.

 

23:55:02 hr@felixSQL>