数据库临时表的使用

一、什么是临时表

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

临时表的分类:
事务级 （On Commit Delete Rows）
数据在 Transaction 期间有效一旦COMMIT后，rollback,断开连接,数据就被自动 TRUNCATE

session级 （On Commit Preserve Rows）
数据在 Session 期间有效一旦关闭了Session 或 Log Off 后，数据就被自动 TRUNCATE

二、创建临时表

1.事务级临时表

事务级临时表:
手动建立:

SQL> create global temporary table t_tmp_tab (id number,name varchar2(20)) on commit delete rows;

Table created.

SQL> desc t_tmp_tab
Name Null  Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
NAME VARCHAR2(20)

SQL> insert into t_tmp_tab select empno,ename from emp where deptno=10;

3 rows created.

SQL> select * from t_tmp_tab;

ID NAME
---------- --------------------
7782 CLARK
7839 KING
7934 MILLER

SQL> commit;

Commit complete.

#事务提交后，再次查询，数据已经被清空
SQL> select * from t_tmp_tab;

no rows selected

SQL>

2.会话级临时表

SQL> create global temporary table s_tab_tmp (id number,name varchar2(20)) on commit preserve rows;

Table created.

SQL> insert into s_tab_tmp select empno,ename from emp where deptno=10;

3 rows created.

SQL> commit;

Commit complete.

SQL> select * from s_tab_tmp;

ID NAME
---------- --------------------
7782 CLARK
7839 KING
7934 MILLER

SQL> exit


#重新登录
#断开会话，重新连接后，数据被清空
SQL> select * from s_tab_tmp;

no rows selected

SQL>

---

三、应用场景

临时表具有多用户操作的独立性和数据的临时性等特点，可以保证多个用户在对同一张临时表操作进行交叉操作时，多个用户操作的并发性和独立性。
临时表的使用常见非常多以下列举几个场景：
1、in操作，in中可以传递的变量是有上限的，太多的in值可能造成SQL报错，同时给sql的执行带来灾难性的影响，如果把in值插入到一张事务临时表中，通过子查询将值传递到in 中，是一种比较理想的解决方案。
2、大表关联的优化，多个非常大的表关联的情况下, 但是需要检索的是少量的数据的时候.可以先把大表的数据进行过滤， 将结果级插入到临时表中, 通过临时表进行关联。
3、各种临时数据储存，比如搜索记录、中转数据，操作日志等一些不重要仅需要临时使用的数据存储场景。