临时表

oracle的临时表应提前创建，而不是运行时。

ORACLE 数据库除了可以保存永久表外，还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。

临时表概念

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

临时表是建立在系统 临时文件夹中的表，如果使用得当，完全可以像普通表一样进行各种操作，在 VFP退出时自动被释放。

临时表在SQL SERVER2005中存放在系统库 tempdb库的临时表中。

当数据库工作在十分大的表上时，在实际操作中你可能会需要运行很多的相关查询，来获得一个大量数据的小的子集，不是对整个表运行这些查询。而是让 MsSQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些，然后对这些表运行查询。

创建MySQL临时表很容易，给正常的CREATE TABLE语句加上TEMPORARY关键字

Sql代码

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL, value INTEGER NOT NULL )

应用范围

一、当某一个 SQL语句关联的表在2张及以上，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。

二、程序执行过程中可能需要存放一些临时的数据，这些数据在整个程序的会话过程中都需要用的等等。

用法

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

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

两种临时表的语法：

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

临时表语法

临时表分类 

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

1）ON COMMIT DELETE ROWS

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

2）ON COMMIT PRESERVE ROWS

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

一、会话级临时表

1:会话级的临时表指临时表中的数据只在会话生命周期之中存在。当前SESSION不退出的情况下，临时表中的数据就还存在，，当用户退出当前SESSION，会话结束的时候， Oracle自动清除临时表中数据。（TRUNCATE TABLE）

格式：

Create Global Temporary Table Table_Name(

Col1 Type1,

Col2 Type2

...

)

On Commit Preserve Rows;

或 CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWS AS SELECT * FROM TEST;

二、事务级临时表

指临时表中的数据只在事务生命周期中存在。

事务级的临时表（默认），这种类型的临时表与事务有关，当进行事务提交或者事务回滚的时候，临时表的数据将自行截断，即当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;

当一个事务结束（commit or rollback），Oracle自动清除临时表中数据。

三、在Oracle 10g中演示临时表的创建与使用

1、事务级临时表

1.创建事务级临时表，插入一条数据，并查询：

--建立临时表

create global temporary table transaction_temp_tb (col1 varchar(20)) on commit delete rows;

--插入数据

insert into transaction_temp_tb values('test');

--查询数据

select * from transaction_temp_tb;

 

2.执行commit或者rollback操作，临时表内数据就被清空，但表结构依然存在：同样，如果不提交而直接结束 SESSION，重新登录记录也不存在

2、会话级临时表

1、创建一个会话级临时表，插入一条数据，并查询：

  create global temporary table session_temp_tb (col1 varchar(20)) on commit preserve rows;

insert into session_temp_tb values('test');

select * from session_temp_tb;

2、执行commit或者rollback操作，表内数据依然存在，新建一个命令窗口（相当于开启了一个新的会话），表内的数据就查询不到了：

3、如果创建会话临时表的会话没有结束，则无法删除此临时表，因为临时表，还在使用之中，但是结束会话（关闭创建会话级临时表的命令窗口）后就可以删除了：

结束SESSION，重新登录，再查询数据select *from temp_tbl，这时候记录已不存在，因为系统在结束 SESSION时自动清除记录

临时表与永久表区别

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 ------------- -------------- ------- --------- ----------- ---------TEST TBS_EDS_DATA YES N YESTMP_TEST NO Y SYS$SESSION NO

 

如上所示，临时表是存储在临时表空间里面的，但是上面脚本可以看出，临时表在数据字典中没有指定其表空间，临时表是NOLOGGING，DURATION为SYS$SESSION

临时表的DML操作速度比较快，但同样也是要产生 Redo Log ，只是同样的DML语句，比对 PERMANENT 的DML 产生的Redo Log 少其实在应用中，往往会创建一个NOLOGGING的永久表（中间表）来保存中间数据，从而代替临时表，至于这这两者有啥优劣，真是很难说清道明(欢迎大家探讨)。

注意事项

1 ) 不支持 lob 对象

这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时就无法使用临时表了。 这点网上很多资料都这么说，我没有追查到底是那个版本不支持lob对象，至少在ORACLE 10g这个版本中，临时表是支持lob对象的.

SQL>CREATE GLOBAL TEMPORARY TABLE TMP_TEST (

ID NUMBER ,

NAME CLOB

) ON COMMIT PRESERVE ROWS;

Table created

SQL>

SQL> INSERT INTO TMP_TEST SELECT 1, 'ADF' FROM DUAL;

1 row inserted

SQL> SELECT * FROM V$VERSION;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

2 ) 不支持主外键关系

3 ）临时表不能永久的保存数据。

4 ）临时表的数据不会备份，恢复，对其的修改也不会有任何日志信息

5 ）临时表不会有DML 锁

DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table, because each session has its own private data.

6 ）尽管对临时表的DML操作速度比较快，但同样也是要产生 Redo Log ，只是同样的DML语句，比对 PERMANENT 的DML 产生的Redo Log 少。请见官方文档：

DML statements on temporary tables do not generate redo logs for the data changes.However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination,either when the user logs off or when the session terminates abnormally such as during a session or instance failure.

7 ） 临时表可以创建临时的索引、视图、触发器。

8 ) 如果要DROP会话级别临时表，并且其中包含数据时，必须先截断其中的数据。否则会报错。

SQL> DROP TABLE TMP_TEST PURGE;

ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引

SQL> TRUNCATE TABLE TMP_TEST;

Table truncated

SQL> DROP TABLE TMP_TEST PURGE;

Table dropped

使用场景

什么时候使用临时表？

在SQL Server的性能调优中,如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集,表变量和临时表是两种选择。

临时表是有它的作用的,但不能滥用,当一个查询涉及很多表时,查询的笛卡尔积是非常大的,单纯的用索引可能解决不了查询时间的问题.这个时候可以用临时表,将一个查询分成多步完成,减少输出查询结果时的笛卡尔积,会对提高查询效率有很大的帮助.比如

查询的主表有上百W条数据,条件都是针对主表字段,然后1:M:M关联两个子表,如果直接关联查询,那么笛卡尔积可能为100W*M*100W*n*100W,可能就会造成耗时很长或查询超时.用临时表先筛选出主表的数据,比如筛选取主表后还有1000条数据满足条件要求,这时再用临时表来关联两个子表,笛卡尔积就会下降一个几何积,会明显提高查询速度.

什么时候用临时表,取决于查询的复杂成度和数据量,简单的查询用临时表就得不偿失了. 

如利用临时表来组织数据，比普通表会更加的简洁、紧凑。这主要是在临时表中可以实现很多的特性。如可以进行预处理计算。如当发现基本标中的索引不怎么合适，也可以在数据库临时表中重新创建索引以优化原有的索引。特别是当需要多次访问某个表或者视图的时候，利用临时表来组织数据是一个提高效率的好方法。即使只是一个简单的查询，其效率的提升也是很明显的。为此，使用临时表最明显的一个好处就似乎可以提高数据库的性能，特别是查询的性能。

　　另外使用临时表还可以减少中间表的产生。在进行某些操作时，本来往往需要一些中间表的帮助才可以完成。而现在数据库管理员可以让数据库在需要时自动生成中间表，并在用完后进行自动删除。如此的话，中间表的建立与删除就不需要数据库管理员人为的管理了。所以，使用临时表可以减少数据库系统中的垃圾表，也可以降低用户的工作量。

什么时候使用临时表？用临时表和用中间表有啥区别呢？

对于一个电子商务类网站，不同消费者在网站上购物，就是一个独立的 SESSION，选购商品放进购物车中，最后将购物车中的商品进行结算。也就是说，必须在整个SESSION期间保存购物车中的信息。同时，还存在有些消费者，往往最终结账时放弃购买商品。如果，直接将消费者选购信息存放在最终表（PERMANENT）中，必然对最终表造成非常大的压力。因此，对于这种案例，就可以采用创建临时表(ON COMMIT PRESERVE ROWS)的方法来解决。数据只在 SESSION 期间有效，对于结算成功的有效数据，转移到最终表中后，ORACLE自动TRUNCATE 临时数据；对于放弃结算的数据，ORACLE 同样自动进行 TRUNCATE ，而无须编码控制，并且最终表只处理有效订单，减轻了频繁的DML操作的压力。

1：当处理某一批临时数据，需要多次DML操作时（插入、更新等），建议使用临时表。

2：当某些表在查询里面，需要多次用来做连接时。（为了获取目标数据需要关联A、B、C， 同时为了获取另外一个目标数据，需要关联D、B、C....）

关于临时表和中间表（NOLOGGING，保存中间数据，使用完后删除）那个更适合用来存储中间数据，我个人更倾向于使用临时表，而不建议使用中间表。

存储过程示例：在存储过程中使用临时表

1 CREATE OR REPLACE PROCEDURE PRODUCT_TEMP_UPDATE_PRC AS 2 PC_DELESTR VARCHAR2( 50); --删除临时表记录语句 3 PC_CREATESTR VARCHAR2( 500); --创建临时表 4 TABEXT VARCHAR2( 10); --用于判断临时表是否存在中间变量 5 6 CUR_CTGY PRODUCTINFO.CATEGORY %TYPE; 7 CUR_PRTIFO PRODUCTINFO %ROWTYPE; 8 9 CURSOR CUR_CATEGORY --产品表中的产品类型游标 10 IS 11 SELECT CATEGORY FROM PRODUCTINFO GROUP BY CATEGORY; 12 13 CURSOR CUR_PROINFO(CTGY VARCHAR) IS 14 SELECT * 15 FROM ( SELECT * 16 FROM PRODUCTINFO 17 WHERE CATEGORY = CTGY 18 ORDER BY PRODUCTPRICE ASC) 19 WHERE ROWNUM < 2; 20 21 BEGIN 22 SELECT COUNT( 1) 23 INTO TABEXT 24 FROM ALL_TABLES 25 WHERE TABLE_NAME = 'productinfo_tmp'; 26 27 PC_DELESTR : = 'delete from productinfo_tmp'; 28 PC_CREATESTR : = 'create global temporary table productinfo_tmp 29 (productid varchar2(10) not null, 30 productname varchar2 (20), 31 productprice number(8,2), 32 quantity number(10), 33 category varchar2(10), 34 desperaction varchar2(1000), 35 origin varchar2(10))on commit preserve rows'; 36 37 IF TABEXT = 0 THEN 38 --不存在临时表就创建一个 39 EXECUTE IMMEDIATE PC_CREATESTR; 40 DBMS_OUTPUT.PUT_LINE( '创建临时表成功！'); 41 ELSE 42 EXECUTE IMMEDIATE PC_DELESTR; 43 DBMS_OUTPUT.PUT_LINE( '删除记录完成！'); 44 END IF; 45 OPEN CUR_CATEGORY; 46 LOOP 47 FETCH CUR_CATEGORY 48 INTO CUR_CTGY; 49 EXIT WHEN CUR_CATEGORY %NOTFOUND; 50 OPEN CUR_PROINFO(CUR_CTGY); 51 FETCH CUR_PROINFO 52 INTO CUR_PRTIFO; 53 IF CUR_PROINFO %FOUND THEN 54 IF CUR_PRTIFO.PRODUCTPRICE < 20 THEN 55 ---产品价格低于20 56 DBMS_OUTPUT.PUT_LINE( '产品ID' || CUR_PRTIFO.PRODUCTID || '产品名称' || 57 CUR_PRTIFO.PRODUCTNAME || '产品价格' || 58 CUR_PRTIFO.PRODUCTPRICE); 59 ELSE 60 --非低于20价格的产品输入到临时表productinfo_tmp 61 EXECUTE IMMEDIATE 'insert into productinfo_tmp( 62 productid,productname,productprice,quantity,category,desperaction,origin) values 63 (''' || CUR_PRTIFO.PRODUCTID || ''',''' || 64 CUR_PRTIFO.PRODUCTNAME || ''',''' || 65 CUR_PRTIFO.PRODUCTPRICE || ''',''' || 66 CUR_PRTIFO.QUANTITY || ''',''' || 67 CUR_PRTIFO.CATEGORY || ''',''' || 68 CUR_PRTIFO.DESPERACTION || ''',''' || 69 CUR_PRTIFO.ORIGIN || ''')'; 70 END IF; 71 END IF; 72 CLOSE CUR_PROINFO; 73 END LOOP; 74 COMMIT; 75 CLOSE CUR_CATEGORY; 76 EXECUTE IMMEDIATE 'update productinfo_tmp set desperaction = ''热销产品'''; 77 END;

第1行表示创建存储过程，名称为PRODUCT_TEMP_UPDATE_PRC 。

第2~7行表示声明变量。

第9~11行表示创建游标cur_category;

第13~19行表示创建游标CUR_PROINFO；该游标带有参数，其参数代表产品类型的编码。游标根据产品的类型不同，获取产品类型中价格最低的数据。

第22~25表示判断临时表productinfo_tmp是否存在。此处利用select into语句把结果放到变量tabext中，如果该表存在结果为1，否则为0.tabext变量将在第37行使用。

第27行表示为变量pc_delestr赋值，他的值是一条SQL语句，该SQL语句表示删除表productinfo_tmp中的数据。这种写法常常用在动态SQL语句上。

第28行表示为变量pc_createstr赋值。他的值是一条DDL语句，该语句用来创建临时表productinfo_tmp。

第37~44行完成分析步骤中的第一步：创建临时表productinfo_tmp。首先判断临时表是否存在，如果不存在，则创建，如果存在则删除表中数据。这里使用了execute immediate语句，利用它执行DDL语句及动态语句。

第45~49行表示打开游标cur_category，并进入流循环取值。当游标的%nofound属性为true时退出。

第50~53行表示打开游标cur_proinfo，它的参数是cur_category中的结果。

第54~58行表示判断价格是否低于20，如果低于20输出到屏幕。

第60~69行表示如果非低于20的插入表productinfo_tmp中。

第76行表示将productinfo_tmp表中的数据修改为热销产品。

【执行】

SQL>exec PRODUCT_TEMP_UPDATE_PRC ;