oracle临时表的用法总结

oracle临时表的用法总结

1、前言

目前所有使用Oracle作为数据库支撑平台的应用，大部分数据量比较庞大的系统，即表的数据量一般情况下都是在百万级以上的数据量。

当然在Oracle中创建分区是一种不错的选择，但是当你发现你的应用有多张表关联的时候，并且这些表大部分都是比较庞大，而你关联的时候发现其中的某一张或者某几张表关联之后得到的结果集非常小并且查询得到这个结果集的速度非常快，那么这个时候我考虑在Oracle中创建“临时表”。

我对临时表的理解：在Oracle中创建一张表，这个表不用于其他的什么功能，主要用于自己的软件系统一些特有功能才用的，而当你用完之后表中的数据就没用了。Oracle的临时表创建之后基本不占用表空间，如果你没有指定临时表（包括临时表的索引）存放的表空的时候，你插入到临时表的数据是存放在ORACLE系统的临时表空间中（TEMP）。

2、临时表的创建

创建Oracle临时表，可以有两种类型的临时表：会话级的临时表事务级的临时表。

1）会话级的临时表因为这这个临时表中的数据和你的当前会话有关系，当你当前SESSION不退出的情况下，临时表中的数据就还存在，而当你退出当前SESSION的时候，临时表中的数据就全部没有了，当然这个时候你如果以另外一个SESSION登陆的时候是看不到另外一个SESSION中插入到临时表中的数据的。即两个不同的SESSION所插入的数据是互不相干的。当某一个SESSION退出之后临时表中的数据就被截断（truncatetable，即数据清空）了。会话级的临时表创建方法：CreateGlobalTemporaryTableTable_Name

(Col1Type1,Col2Type2...)OnCommitPreserveRows；举例：

createglobaltemporarytableStudent(Stu_idNumber(5),Class_idNumber(5),Stu_NameVarchar2(8),

Stu_Memovarchar2(200))onCommitPreserveRows;

2）事务级临时表是指该临时表与事务相关，当进行事务提交或者事务回滚的时候，临时表中的数据将自行被截断，其他的内容和会话级的临时表的一致（包括退出SESSION的时候，事务级的临时表也会被自动截断）。事务级临时表的创建方法：CreateGlobalTemporaryTableTable_Name

(Col1Type1,Col2Type2...)OnCommitDeleteRows；举例：

createglobaltemporarytableClasses(Class_idNumber(5),Class_NameVarchar2(8),

Class_Memovarchar2(200))onCommitdeleteRows;3）两中类型临时表的区别

会话级临时表采用oncommitpreserverows；而事务级则采用oncommitdeleterows；用法上，会话级别只有当会话结束临时表中的数据才会被截断，而且事务级临时表则不管是commit、rollback或者是会话结束，临时表中的数据都将被截断

4）什么时候使用临时表

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

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

3．例子：略

4．临时表的不足之处

1）不支持lob对象，这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时就无法使用临时表了。2）不支持主外键关系

所以，由于以上原因，我们可以自己创建临时表，以弥补oracle临时表的不足之处

上面的都是本人经过测试的，但下面是在网上搜索到的方法，本人具体没有测试过，不过觉得可行性很强，有时间测试下

创建方法：

1、以常规表的形式创建临时数据表的表结构，但要在每一个表的主键中加入一个SessionID列以区分不同的会话。（可以有lob列和主外键）

2、写一个用户注销触发器，在用户结束会话的时候删除本次会话所插入的所有记录(SessionID等于本次会话ID的记录)。

3、程序写入数据时，要顺便将当前的会话ID(SessionID)写入表中。4、程序读取数据时，只读取与当前会话ID相同的记录即可。功能增强的扩展设计：

1、可以在数据表上建立一个视图，视图对记录的筛选条件就是当前会话的SessionID。2、数据表中的SessionID列可以通过Trigger实现，以实现对应用层的透明性。3、高级用户可以访问全局数据，以实现更加复杂的功能。扩展临时表的优点：

1、实现了与Oracle的基于会话的临时表相同的功能。2、支持SDO_GEOMETRY等lob数据类型。

3、支持表间的主外键连接，且主外键连接也是基于会话的。4、高级用户可以访问全局数据，以实现更加复杂的功能

扩展阅读：Oracle中的临时表用法汇总

Oracle中的临时表用法汇总

1语法在Oracle中，可以创建以下两种临时表：1)会话特有的临时表

CREATEGLOBALTEMPORARY()ONCOMMITPRESERVEROWS;2)事务特有的临时表

CREATEGLOBALTEMPORARY()ONCOMMITDELETEROWS；

CREATEGLOBALTEMPORARYTABLEMyTempTable

所建的临时表虽然是存在的，但是如果insert一条记录然后用别的连接登上去select，记录是空的。

--ONCOMMITDELETEROWS说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）

--ONCOMMITPRESERVEROWS说明临时表是会话指定，当中断会话时ORACLE将截断表。

2动态创建createorreplaceprocedurepro_temp(v_col1varchar2,v_col2varchar2)asv_numnumber;begin

selectcount(*)intov_numfromuser_tableswheretable_name="T_TEMP";

--createtemporarytableifv_num

测试:

15:23:54SQL>setserveroutputon

15:24:01SQL>execpro_temp("11","22");11

PL/SQL过程已成功完成。

已用时间:00:00:00.7915:24:08SQL>desct_temp;ERROR：

ORA-04043:对象t_temp不存在

3特性和性能(与普通表和视图的比较)临时表只在当前连接内有效临时表不建立索引,

所以如果数据量比较大或进行多次查询时,不推荐使用数据处理比较复杂的时候时表快,

反之视图快点在仅仅查询数据的时候建议用游标:opencursorfor"sqlclause";

临时表的创建

创建Oracle临时表，可以有两种类型的临时表：会话级的临时表和事务级的临时表。

1）会话级的临时表因为这这个临时表中的数据和你的当前会话有关系，当

你当前SESSION不退出的情况下，临时表中的数据就还存在，而当你退出当前SESSION的时候，临时表中的数据就全部没有了，当然这个时候你如果以另外一个SESSION登陆的时候是看不到另外一个SESSION中插入到临时表中的数据的。即两个不同的SESSION所插入的数据是互不相干的。当某一个SESSION退出之后临时表中的数据就被截断（truncatetable，即数据清空）了。会话级的临时表创建方法：CreateGlobalTemporaryTableTable_Name(Col1Type1,Col2Type2...)OnCommitPreserveRows；

举例createglobaltemporarytableStudent(Stu_id

Number(5),Class_idNumber(5),Stu_NameVarchar2(8),Stu_Memovarchar2(200))onCommitPreserveRows;2）事务级临时表是指该临时表与事务相关，当进行事务提交或者事务回滚

的时候，临时表中的数据将自行被截断，其他的内容和会话级的临时表的一致（包括退出SESSION的时候，事务级的临时表也会被自动截断）。事务级临时表的创建方法：CreateGlobalTemporaryTable

Table_Name(Col1Type1,Col2Type2...)OnCommitDeleteRows；举例：createglobaltemporarytableClasses(Class_id

Number(5),Class_NameVarchar2(8),Class_Memovarchar2(200))onCommitdeleteRows;

3）、两种不通类型的临时表的区别：语法上，会话级临时表采用oncommitpreserverows而事务级则采用oncommitdeleterows；用法上，会话级别只有当会话结束临时表中的数据才会被截断，而且事务级临时表则不管是commit、rollback或者是会话结束，临时表中的数据都将被截断。

例子：

1）、会话级（Session关闭掉之后数据就没有了，当Commit的时候则数据还在，当Rollback的时候则数据也是一样被回滚）：

insertintostudent(stu_id,class_id,stu_name,stu_memo)values(1,1,"张三","福建");

insertintostudent(stu_id,class_id,stu_name,stu_memo)values(2,1,"华仔","福州");

insertintostudent(stu_id,class_id,stu_name,stu_memo)values(3,2,"S.H.E","厦门");

insertintostudent(stu_id,class_id,stu_name,stu_memo)values(4,2,"阿妹","厦门");

SQL>select*fromstudent;

STU_IDCLASS_IDSTU_NAMESTU_MEMO----------------------

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

11张三福建21华仔福州32S.H.E厦门42阿妹厦门

SQL>commit;

Commitcomplete

SQL>select*fromstudent;

STU_IDCLASS_IDSTU_NAMESTU_MEMO----------------------

--------------------------------------------------------------------------------11张三福建21华仔福州32S.H.E厦门42阿妹厦门

SQL>insertintostudent(stu_id,class_id,stu_name,stu_memo)values(4,2,"阿妹","厦门");

1rowinserted

SQL>select*fromstudent;

STU_IDCLASS_IDSTU_NAMESTU_MEMO----------------------

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

11张三福建21华仔福州32S.H.E厦门42阿妹厦门42阿妹厦门

SQL>rollback;

Rollbackcomplete

SQL>select*fromstudent;

STU_IDCLASS_IDSTU_NAMESTU_MEMO----------------------

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

11张三福建21华仔福州32S.H.E厦门42阿妹厦门

SQL>

2）、事务级（Commit之后就删除数据）：本例子将采用以下的数据：insertintoclasses(Class_id,Class_Name,Class_Memo)values(1,"计算机","9608");

insertintoclasses(Class_id,Class_Name,Class_Memo)values(2,"经济","9602");

insertintoclasses(Class_id,Class_Name,Class_Memo)values(3,"经济","9603");在一个SESSION中（比如SQLPLUS登陆）插入上面3条记录，然后再以另外一个SESSION（用SQLPLUS再登陆一次）登陆，当你select*fromclasses;的时候，classes表是空的，而你再第一次登陆的SQLPLUS中select的时候可以查询到，这个时候你没有进行commit或者rollback之前你可以对刚才插入的3条记录进行update、delete等操作，当你进行commit或者rollback的时候，这个时候由于你的表是事务级的临时表，那么在插入数据的session也看不到数据了，这个时候数据就已经被截断了。运行结果如下：

SQL>insertintoclasses(Class_id,Class_Name,Class_Memo)values(1,"计算机","9608");

1rowinserted

SQL>insertintoclasses(Class_id,Class_Name,Class_Memo)values(2,"经济","9602");

1rowinserted

SQL>insertintoclasses(Class_id,Class_Name,Class_Memo)values(3,"经济","9603");

1rowinserted

SQL>updateclassessetclass_memo=""whereclass_id=3;

1rowupdated

SQL>select*fromclasses;

CLASS_IDCLASS_NAMECLASS_MEMO------------------

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

1计算机96082经济96023经济

SQL>deletefromclasseswhereclass_id=3;

1rowdeleted

SQL>select*fromclasses;

CLASS_IDCLASS_NAMECLASS_MEMO------------------

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

1计算机96082经济9602SQL>commit;

Commitcomplete

SQL>select*fromclasses;

CLASS_IDCLASS_NAMECLASS_MEMO------------------

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

SQL>

再重复插入一次（SQL>insertintoclasses(Class_id,Class_Name,Class_Memo)values(1,"计算","9608");

1rowinserted

SQL>insertintoclasses(Class_id,Class_Name,Class_Memo)values(2,"经济","9602");

1rowinserted

SQL>insertintoclasses(Class_id,Class_Name,Class_Memo)values(3,"经济","9603");

1rowinserted

），然后rollback。SQL>Rollback;

Rollbackcomplete

SQL>select*fromclasses;

CLASS_IDCLASS_NAMECLASS_MEMO------------------

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

SQL>

临时表的应用

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

2）、程序执行过程中可能需要存放一些临时的数据，这些数据在整个程序的会话过程中都需要用的等等。5、注意事项：

1）、临时表的索引以及对表的修改、删除等和正常的表是一致的。

2）、Oracle的临时表是Oracle8i才支持的功能特性，如果你的Oracle版本比较低的话，那么就可能没有办法用到了，如果你的Oracle版本是8i的话，你还需要把$ORACLE_HOME/admin/${ORACLE_SID}/pfile目录下的

init.ora初始参数配置文件的compatible修改为compatible="8.1.0"，我的服务器上就是这样子配置的。当然也可以修改为compatible="8.1.6"

友情提示：本文中关于《oracle临时表的用法总结》给出的范例仅供您参考拓展思维使用，oracle临时表的用法总结：该篇文章建议您自主创作。

来源：网络整理 免责声明：本文仅限学习分享，如产生版权问题，请联系我们及时删除。

《oracle临时表的用法总结》由互联网用户整理提供,转载分享请保留原作者信息,谢谢!
链接地址：http://www.bsmz.net/gongwen/747148.html

• 上一篇：学好Oracle的六条总结
• 下一篇：大二班下学期总结