Temporary Table临时表:
临时表对于应用程序来说有时是非常有用的,它把结果集临时的缓存到表里.

这有一个典型的临时表应用:
一个基于网页的航空票务预订系统,它允许客户创建许多可选的航班.每个被选的航班都由临时表里的一行来表示,对预选航班的更改就是对临时表里数据的更改.一旦客户选定了航班,将会把临时表里的数据删除,并把选定的航班插入到固定表里.

创建临时表有两个选项:
DELETE ROWS: 此选项创建一个临时表只在单个transaction有效.在transaction提交后所有数据都将被删除.

PRESERVE ROWS: 此选项创建一个临时表在真个会话期间都有效. 在会话结束后删除所有数据.

举个实例来帮助理解:
SQL> CREATE GLOBAL TEMPORARY TABLE temp1 (a number, b number) ON COMMIT DELETE ROWS;

Table created.

SQL> CREATE GLOBAL TEMPORARY TABLE temp2 (a number, b number) ON COMMIT PRESERVE ROWS;

Table created.

SQL> select * from temp1;

no rows selected

SQL> select * from temp2;

no rows selected

SQL> insert into temp1 values(1,1);

1 row created.

SQL> select * from temp1;

         A          B
---------- ----------
         1          1

SQL> insert into temp1 values(2,2);

1 row created.

SQL> select * from temp1;

         A          B
---------- ----------
         1          1
         2          2

SQL> commit;

Commit complete.

SQL> select * from temp1;

no rows selected

SQL> insert into temp2 values(1,1);

1 row created.

SQL> insert into temp2 values(2,2);

1 row created.

SQL> select * from temp2;

         A          B
---------- ----------
         1          1
         2          2

SQL> commit;

Commit complete.

SQL> select * from temp2;

         A          B
---------- ----------
         1          1
         2          2

SQL> rollback;

Rollback complete.

SQL> select * from temp2;

         A          B
---------- ----------
         1          1
         2          2

SQL> conn liang/oracle
Connected.
SQL> select * from temp2;

no rows selected

通过实例发现temp1( DELETE ROWS)在结束transanction时删除数据, temp2(PRESERVE ROWS)在结束会话时删除数据.

ITPub博客

Temporary Table临时表

最新文章