temporary table.docx
《temporary table.docx》由会员分享，可在线阅读，更多相关《temporary table.docx（41页珍藏版）》请在冰豆网上搜索。
temporarytable
临时表创建及介绍
ORACLE的临时表在应用系统中有很大的作用，它可以让用户只能够操作各自的数据中而互不干扰，不用担心会破坏或影响其他SESSION/TRANSACTION的数据，这也是数据安全的一种解决方法
临时表分为SESSION、TRANSACTION两种，SESSION级的临时表数据在整个SESSION都存在，直到结束此次SESSION；而TRANSACTION级的临时表数据在TRANACTION结束后消失，即COMMIT/ROLLBACK或结束SESSION都会清除TRANACTION临时表数据。
两种临时表的语法：
createglobaltemporarytable临时表名oncommitpreserve|deleterows
用preserve时就是SESSION级的临时表，用delete就是TRANSACTION级的临时表
例：
1、SESSION级临时表
--建立临时表
createglobaltemporarytabletemp_tbl（col_avarchar2（30））oncommitpreserverows
--插入数据
insertintotemp_tblvalues（'testsessiontable'）
--提交commit
--查询数据
select*fromtemp_tbl
可以看到数据'testsessiontable'记录还在
--结束SESSION，重新登录，再查询数据select*fromtemp_tbl，这时候记录已不存在，因为系统在结束SESSION时自动清除记录
2、TRANSACTION级临时表
--建立临时表
createglobaltemporarytabletemp_tbl（col_avarchar2（30））oncommitdeleterows
--插入数据
insertintotemp_tblvalues（'testtransactiontable'）
--提交
commit
--查询数据
select*fromtemp_tbl
这时候可以看到刚才插入的记录'testtransactiontable'已不存在了；同样，如果不提交而直接结束SESSION，重新登录记录也不存在。
临时表管理需要注意的地方
临时表相对与其他表来说，是一种比较特殊的表结构，但是，作用又比较大，Oraclee数据库若没有这种表的话，还真是不行。
为了管理好这种特殊的表，我们需要注意几个细节。
一是要注意临时表不能永久的保存数据。
只所以称为临时表，就是因为该表中的内容只是临时存在的。
当一个会话或者事务结束时，该表中的内容就会被自动清空。
所以，在临时表中，一般不要保存永久数据。
在实务中，有个不好的操作习惯，就是有些人在测试数据库的时候，喜欢把测试的数据放在临时数据表中。
其实，这是对Oralce临时数据表认识的错误。
若我们在数据库中，把要测试的数据，如销售定单的内容放在数据库的临时表中的话，则在其他功能中，如要测试销售定单日报表的功能时，就会找不到相关的定单内容。
因为离开特定的会话或者事务的话，临时表中的内容就会不存在了。
所以，Oralce数据库中所讲的临时表不是给我们来存储测试数据的。
二是临时表中的数据不会备份、恢复，对其的修改也不会有任何的日志信息。
若我们在操作数据库的时候，往数据库的临时表中存入了一些信息。
此时突然服务器出现当机。
此时，我们想通过数据库备份文件恢复数据库临时表中的内容，或者查看临时表的日志信息，都是无法实现的。
也就是说，当服务器以外死机重新启动后，临时表中的内容就会被清空。
在数据库的任何地方，如数据库备份文件或者日志信息中，都查不到在重新启动之前数据库临时表中保存了哪些内容，就好象根本没有对临时表进行操作一样。
三是临时表表空间的管理。
临时表在Oraclee数据库中，也是表的一种，其也有对应的表空间。
在创建临时表的时候，若我们不指定表空间的话，默认的表空间是SYSTEM。
对于临时表的表空间管理的话，我们需要注意一个小的细节。
若我们把临时表的表空间归属为SYSTEM的话，也就是说，在创建临时表的时候不具体指定具体的表空间，则这个默认的表空间是不能被删除的。
而若我们在创建临时表表空间的时候，指定为SYSTEM以外的表空间的话，则在不需要这表空间的时候，我们可以删除。
所以，为了后续管理的方便，笔者还是建议大家在创建临时表的时候，要指定表空间。
四是要注意一个问题，临时表只是数据是临时的，而表仍然是永久的。
也就是说，当一个会话结束或者一个事务完成时，其临时表中的数据虽然删除了，但是，临时表本身仍然是存在的。
也就是说。
Oraclee数据库中的临时表表是全局的，只是数据是临时的。
这跟SQLServer数据库系统具有比较大的区别。
其实，这两个数据库在临时表的处理上有很大的不同，各有各的特色。
在以后的文章中，我会专门叙述这两种数据库在临时表管理机制上的不同，欢迎大家关注。
五是要注意Oraclee数据库在给临时表填入数据的时候，不会对相应的记录加锁。
也就是说，当在临时表上执行DML语句的操作时，不会给记录加锁，也不会将数据的变化内容写到重做（REDO）日志中。
所以不能用临时表保存永久的数据，也不能对临时表进行共同的操作。
这是新手在管理数据库临时表经常会碰到的问题。
六是临时表与普通表之间不能相互转换。
在一般情况下，临时表建立后，该表就不能被转换成永久表。
所以，这也说明一个道理，利用临时表作为数据库设计时候的测试表不合适。
这个临时表可能跟我们按字面意思理解的临时表有误，不是我们所认为的为了测试表结构而建立的临时表。
这一点是我们在刚开始接触OracleE数据库时，经常会犯的错误。
如何使用临时表？
5.4.1无法显示的数据设计师'style='text-decoration:
underline;color:
blue;'target=_blank>设计师L告诉程序员M在项目中需要使用到临时表。
由于使用的是PostgreSQL数据库，L还告诉M，在PostgreSQL中使用临时表需要利用Java的JDBC来建表。
M虽然并不理解为什么需要利用JDBC来建表，但是他深信以当前他的所知这不是一件困难的事，于是程序员M模拟了一个简单场景，对Room实体的新增和查询动作。
M写下了如下的代码，见例5.18：
例5.18：
TestTempTableDAONoManager.java
packagedao.jdbc;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importentity.Room;
publicclassTestTempTableDAONoManager{
//针对temproom表的操作
privatefinalStringCREAT_ROOM_TABLE="CREATEGLOBALTEMPTABLEtemproom"
"（idint8NOTNULL,"
"addressvarchar（255）,"
"numbervarchar,"
"CONSTRAINTroom_pkeyPRIMARYKEY（id））"
"ONCOMMITDELETEROWS;";
privatefinalStringINSERT_ROOM_TABLE="insertintotemproom（id,address,number）values（?
?
?
）";
privatefinalStringFIND_ROOM_BY_KEY="select*fromtemproomwhereid=?
";
//取得连接
privateConnectiongetConnection（）throwsSQLException{
try{
finalStringurl="jdbc:
postgresql:
//localhost/TESTDB";
finalStringuser="sa";
finalStringpassword="1111";
Class.forName（"org.postgresql.Driver"）;
Connectionconnection=DriverManager.getConnection（url,user,
password）;
returnconnection;
}catch（ClassNotFoundExceptione）{
thrownewSQLException（e.getMessage（））;
}
}
//创建临时表
publicvoidcreateTable（）{
//提供一个连接
Connectioncon=null;
//提供一个创建预编译SQL语句的变量
PreparedStatementps=null;
try{
con=this.getConnection（）;
ps=con.prepareStatement（this.CREAT_ROOM_TABLE）;
ps.executeUpdate（）;
}catch（SQLExceptionex）{
ex.printStackTrace（）;
}finally{
//必须进行的处理，关闭PreparedStatement、ResultSet、Connection
try{
if（ps!
=null）
ps.close（）;
if（con!
=null）
con.close（）;
}catch（SQLExceptionex）{
ex.printStackTrace（）;
}
}
}
//插临时表
publicvoidinsertRoom（Roomroom）throwsException{
//提供一个连接
Connectioncon=null;
//提供一个创建预编译SQL语句的变量
PreparedStatementps=null;
//提供一个返回SQL查询结果的ResultSet接口变量
//ResultSet带有游标可以指向返回结果中的某条记录
ResultSetrs=null;
try{
//取得JDBC连接
con=this.getConnection（）;
//预编译SQL语句并执行insertSql
ps=con.prepareStatement（this.INSERT_ROOM_TABLE）;
ps.setLong（1,room.getId（））;
ps.setString（2,room.getAddress（））;
ps.setString（3,room.getNumber（））;
//若新增失败
if（ps.executeUpdate（）!
=1）{
thrownewException（"更新失败"）;
}
}catch（SQLExceptionex）{
ex.printStackTrace（）;
}finally{
//必须进行的处理，关闭PreparedStatement、ResultSet、Connection
try{
if（rs!
=null）
rs.close（）;
if（ps!
=null）
ps.close（）;
if（con!
=null）
con.close（）;
}catch（SQLExceptionex）{
ex.printStackTrace（）;
}
}
}
/**
*根据Room表的主键返回Room实体
*/
publicRoomfindRoom（Longid）{
//提供一个连接
Connectioncon=null;
//提供一个创建预编译SQL语句的变量
PreparedStatementps=null;
//提供一个返回SQL查询结果的ResultSet接口变量
//ResultSet带有游标可以指向返回结果中的某条记录
ResultSetrs=null;
//提供一个Room实体的变量
Roomroom=null;
try{
//取得JDBC连接
con=this.getConnection（）;
//预编译SQL语句并执行findSql
ps=con.prepareStatement（this.FIND_ROOM_BY_KEY）;
ps.setLong（1,id）;
rs=ps.executeQuery（）;
//当返回结果集中无记录时返回null
if（!
rs.next（））{
returnnull;
}
//以下的情况将保证在结果集中有记录时的应用
//创建Room实体的实例以作处理
room=newRoom（）;
room.setId（rs.getLong（"id"））;
room.setAddress（rs.getString（"address"））;
room.setNumber（rs.getString（"number"））;
}catch（SQLExceptionex）{
ex.printStackTrace（）;
}finally{
//必须进行的处理，关闭PreparedStatement、ResultSet、Connection
try{
if（rs!
=null）
rs.close（）;
if（ps!
=null）
ps.close（）;
if（con!
=null）
con.close（）;
}catch（SQLExceptionex）{
ex.printStackTrace（）;
}
}
returnroom;
}
publicstaticvoidmain（String[]args）{
TestTempTableDAONoManagertestTempTableDAONoManager=newTestTempTableDAONoManager（）;
//创建表temproom
testTempTableDAONoManager.createTable（）;
//新建Room实体
Roomroom=newRoom（）;
room.setId（1L）;
room.setNumber（"001"）;
room.setAddress（"RWRoom"）;
try{
//插表temproom
testTempTableDAONoManager.insertRoom（room）;
}catch（Exceptionex）{
ex.printStackTrace（）;
}
//显示结果
RoomshowRoom=newRoom（）;
showRoom=testTempTableDAONoManager.findRoom（1L）;
System.out.println（"-----Roomid:
"showRoom.getId（））;
System.out.println（"-----RoomAddress:
"showRoom.getAddress（））;
System.out.println（"-----RoomNumber:
"showRoom.getNumber（））;
}
}
在这段代码中，M实现了三个主要的DAO方法：
（1）createTable（），此方法用以创建临时表temproom
（2）insertRoom（），此方法用以为临时表temproom插入一条记录
（3）findRoom（），此方法用以取得insertRoom（）方法所插入的一条，将以Room实体返回。
此外，为了简单起见，M给出了getConnection（）方法，三个DAO的主要方法都将调用getConnection（）来取得数据库的JDBC连接。
很快代码实现了，于是M又构造了main（）方法来对实现结果做测试。
当M满心欢喜的以为结果将如他所料时，一个意想不到的情况发生了。
这段看起来完全正确的代码居然抛出了异常：
java.sql.SQLException:
ERROR:
relation"temproom"doesnotexist
这是怎么一回事呢？
5.4.2理解临时表M的代码如果用在与非临时表的表交互时自然没有错，但是用在临时表上显然就错了。
原因就出在临时表上。
要解决这个“无法显示的数据”问题，就必须搞清楚什么是临时表。
绝大多数关系型数据库都有临时表，这在SQL-92中也是一个标准。
临时表的特性在于分布式运用，也即任何一个用户连接到数据库，即使使用的是同名的临时表，这个用户的所有操作也对另一连接的用户不可见。
换句话说，就是“临时表多用户并行不是问题”。
在标准的SQL-92中，临时表的定义是这样的：
（1）使用CREATETEMPORARYTABLE…定义临时表。
（2）定义临时表的结尾部分可以存在ONCOMMITDELETEROWS子句或ONCOMMITPRESERVEROWS子句。
（3）若缺省ONCOMMIT子句的情况下，将使用ONCOMMITDELETEROWS子句所提供的行为。
通过ONCOMMITDELETEROWS子句定义的临时表它的特性在于：
“临时表的所有数据将在一次事务提交后被全部删除”
通过ONCOMMITPRESERVEROWS子句定义的临时表它的特性在于：
“临时表的所有数据在一次事务提交后将依旧保留”
但是无论使用哪种ONCOMMIT子句定义的临时表，它在一次数据库连接结束后都将被删除所有数据。
请注意：
一次数据库连接和一次事务提交是两个概念，前者读者可以简单的理解为Connection连接的关闭，也即Java中“connection.close（）”方法的调用；后者读者可以理解为Connection连接中的事务提交，也即Java中“mit（）”方法的调用。
每一种数据库对于临时表的定义都存在着兼容性的问题，在SQL-92编码规则中临时表创建后即使连接结束也不会被drop掉，符合这个标准的数据库具有代表性的就是Oracle，但是有些数据库则定义临时表在连接结束后将连同整个表都会被drop掉，PostgreSQL就是其中的一种，MySQL也是如此。
因此若要使用临时表，则必须在项目启动后对该项目所使用的数据库文档进行必要的了解。
由于临时表的先天特性（多用户并行无关性），在项目中使用临时表是很常见的。
5.4.3查找问题在理解了临时表的相关特性后，不难看出M的代码所存在的问题。
（1）首先，L要求M使用JDBC来创建临时表的Schema是没有问题的，由于PostgreSQL每次连接结束都将drop临时表，因此必须手动创建临时表（调用createTable（））。
（2）M在这段代码中最大的问题在于getConnection（），在例5.18中可以看到任何一个DAO方法都会调用getConnection（），而每个DAO方法的finally部分又会关闭Connection。
这样的话，客户端在调用createTable（）方法结束后已经关闭了数据库连接。
按照临时表的特性，此时临时表中的数据已经被自动删除了。
5.4.4提供一个ConnectionManager问题
（2）是整段代码引起错误的主要原因，解决这个问题有多种方案。
最容易想到的就是整个DAO全局共享一个Connection，可是如果就简单的提供一个单例类是有问题的。
（1）因为临时表本身的特性虽然是多用户并行无关性，但是这个无关性的前提是每个用户一个连接。
假设提供一个单例类，那么在整个运行期所有客户端都将使用这个Connection，如此的结果必然导致多个客户共用一个Connection。
（2）对于Connection连接提供单例类，必然导致一个长连接不被释放，对于任何一个系统来说这都是无法接受的。
有鉴于此，单例类的实现被否定了。
深入的再想一下，不难发现，其实对于临时表的操作需要的是以下两个条件：
（1）提供一种方式，让多个操作临时表的方法共享一个连接。
（2）而这样一个连接对于任何请求都将是独立的。
假设仅以J2EE模型来说，这是很容易实现的。
因为从Servlet请求到来的每一个客户端都只发生在自己的线程中。
这就给实现两个条件带来了契机，只需要利用Java的ThreadLocal类。
提供一个ConnectionManager类，该类将使用ThreadLocal类来管理Connection连接，以保证该Connection连接对于一次请求的线程是独立的。
请见例5.19：
例5.19：
ConnectionManager.java
packagedao.jdbc;
importjava.sql.Connection;
publicclassConnectionManager{
//静态变量＂当前线程＂，用以管理Connection
privatestaticfinalThreadLocalcurrentConnection=newThreadLocal（）;
//静态方法取得＂当前线程＂所使用的Connection
publicstaticConnectiongetConnection（）{
return（Connection）currentConnection.get（）;
}
//将＂当前线程＂与＂当前连接＂绑定
staticConnectionsetCurrentConnection（Connectionconnection）{
ConnectionpriorConnection=（Connection）currentConnection.get（）;
currentConnection.set（connection）;
returnpriorConnection
升级会员 