【SQL】全局临时表 GLOBAL TEMPORARY TABLE

目录

定义

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.

总之来说临时表删除的时候表内不能有数据