【SQL】全局临时表 GLOBAL TEMPORARY TABLE

最新推荐文章于 2024-09-27 10:41:14 发布

原创最新推荐文章于 2024-09-27 10:41:14 发布 · 4.4k 阅读

10 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#数据库 #oracle

SQL 专栏收录该内容

27 篇文章

订阅专栏

本文详细介绍了Oracle中的两种临时表类型：oncommitdeleterows事务级临时表，会在事务提交时自动删除数据；以及oncommitpreserverows会话级临时表，仅在会话结束时清除。通过实例展示了它们的创建、操作和删除过程。

定义

on commit delete rows 事务级临时表

on commit preserve rows 会话级临时表

删除临时表

定义

临时表是用来保留临时或者中间数据的表，属于数据哭对象，有对应编号

它可以像普通表一样使用，并为每个会话提供专有数据，会话之间不受影响

临时表的数据会在会话完成或事物结束时自动清除数据

它存在于temp表空间内并不占用用户表空间

对于临时表的操作，不生成日志和回滚数据，没有锁，不备份，不维护

on commit delete rows 事务级临时表

在用户提交(commit)时清除数据，也叫事务级临时表

由sys用户创建全局临时表

SQL> create global temporary table tb_temp01 on commit delete rows as select * from scott.salgrade;

Table created.

SQL> select * from tb_temp01;

no rows selected

create 属于ddl会隐式提交，因此表中没有数据

在该会话的临时表中添加数据

SQL> insert into tb_temp01 select * from scott.salgrade;

5 rows created.

SQL> select * from tb_temp01;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

新建一个会话查看临时表中的内容

SQL> show user
USER is "SYS"
SQL> select * from tb_temp01;

no rows selected

看不到数据，因为临时表的数据仅能在当前会话中看到

在原来的会话中提交数据看一下

SQL> select * from tb_temp01;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

SQL> commit;

Commit complete.

SQL> select * from tb_temp01;

no rows selected

提交数据后，反而看不到数据，这是因为 on commit delete rows的限制

on commit preserve rows 会话级临时表

此关键字意为当前会话结束时，丢弃数据，也叫会话级临时表

还是用sys用户建立会话集临时表

SQL> create global temporary table tb_temp02 on commit preserve rows as select * from scott.salgrade;

Table created.

SQL> select * from tb_temp02;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

会话级临时表在ddl之后依旧保留数据

那么在另一个会话中能不能看到数据呢

SQL> conn / as sysdba
Connected.
SQL> select * from tb_temp02;

no rows selected

显然不能

在前一个会话中对表进行增删改等dml操作并提交

SQL> insert into tb_temp02 values(9,9999,9999);

1 row created.

SQL> delete tb_temp02 where grade=1;

1 row deleted.

SQL> update tb_temp02 set grade=88 where grade=4;

1 row updated.

SQL> select * from tb_temp02;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         2       1201       1400
         3       1401       2000
        88       2001       3000
         5       3001       9999
         9       9999       9999

SQL> commit;

Commit complete.

SQL> select * from tb_temp02;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         2       1201       1400
         3       1401       2000
        88       2001       3000
         5       3001       9999
         9       9999       9999

数据依然存在

结束会话或切换用户之后数据消失

SQL> select * from tb_temp02;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         2       1201       1400
         3       1401       2000
        88       2001       3000
         5       3001       9999
         9       9999       9999

SQL> conn tiger/scott;
Connected.
SQL> conn / as sysdba
Connected.
SQL> select * from tb_temp02;

no rows selected

删除临时表

事物级临时表可以直接用drop语句删除

SQL> select * from tb_temp01;

no rows selected

SQL> insert into tb_temp01 select * from scott.salgrade;

5 rows created.

SQL> select * from tb_temp01;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

SQL> drop table tb_temp01;

Table dropped.

因为drop也属于DDL操作，进行隐式提交，提交后清除数据

但是会话级临时表不可以必须要对表进行截断或者清除数据，再或者退出会话之后才能删除

SQL> select * from tb_temp02;

no rows selected

SQL> insert into tb_temp02 select * from scott.salgrade;

5 rows created.

SQL> drop table tb_temp02;
drop table tb_temp02
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use

SQL> commit;

Commit complete.

SQL> drop table tb_temp02;
drop table tb_temp02
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use

SQL> truncate table tb_temp02;

Table truncated.

SQL> delete from tb_temp02;

0 rows deleted.

SQL> drop table tb_temp02;

Table dropped.