Chinaunix首页 | 论坛 | 博客
  • 博客访问: 895655
  • 博文数量: 185
  • 博客积分: 50
  • 博客等级: 民兵
  • 技术积分: 3930
  • 用 户 组: 普通用户
  • 注册时间: 2007-09-11 13:11
个人简介

iihero@ChinaUnix, ehero.[iihero] 数据库技术的痴迷爱好者. 您可以通过iihero AT qq.com联系到我 以下是我的三本图书: Sybase ASE in Action, Oracle Spatial及OCI高级编程, Java2网络协议内幕

文章分类

全部博文(185)

文章存档

2014年(4)

2013年(181)

分类: 数据库开发技术

2013-07-24 10:10:55

虽然SQL92, 99, 2003, 2008标准都有推出,但并不是所有商家严格按照标准行事。痛苦的是使用和应用不同数据库的DBA和开发人员。
这里以几种主流数据库为例,分别介绍一下临时表的使用:

1. PostgreSQL (以9.x为例)

使用的是比较标准的语法: 
create [global | local] temp table t ( id int primary key) on commit delete | preserve rows
其中,global和local是摆设,一样的效果。都是会话级别的。当前会话退出,表即删除。
可以创建与当前模式相同的表名,即可以创建同名的表t,drop table t时,会先删除临时表。如下例所示:


  1. "font-size:16px;">iihero=# create global temp table t(id int primary keyon commit delete rows;  
  2. NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"  
  3. CREATE TABLE  
  4. iihero=# insert into t values(1);  
  5. INSERT 0 1  
  1. "font-size:16px;">iihero=# select * from t;  
  2.  id  
  3. ----  
  4. (0 rows)  
  5.   
  1. "font-size:16px;">iihero=# begin;  
  2. BEGIN  
  3. iihero=# insert into t values(1);  
  4. INSERT 0 1  
  5. iihero=# select * from t;  
  6.  id  
  7. ----  
  8.   1  
  9. (1 row)  
  10.   
  11.   
  12.   
  13.   
  14. iihero=# commit;  
  15. COMMIT  
  16. iihero=# select * from t;  
  17.  id  
  18. ----  
  19. (0 rows)  
  20.   

2. DB2 9.x


1). DB2的临时表需要用命令Declare Temporary Table来创建, 并且需要创建在用户临时表空间上;
2). DB2在数据库创建时, 缺省并不创建用户临时表空间, 如果需要使用临时表, 则需要用户在创建临时表之前创建用户临时表空间;
3). 临时表的模式为SESSION,SESSION即基于会话的,且在会话之间是隔离的。当会话结束时,临时表的数据被删除,临时表被隐式卸下。对临时表的定义不会在SYSCAT.TABLES中出现 .;
4). 缺省情况下, 在Commit命令执行时, DB2临时表中的所有记录将被删除; 这可以通过创建临时表时指定不同的参数来控制;
5). 运行ROLLBACK命令时, 用户临时表将被删除;
下面是DB2临时表定义的一个示例:
DECLARE GLOBAL TEMPORARY TABLE results
    (   
        RECID     VARCHAR(32)     ,    --id
        XXLY      VARCHAR(100),        --信息来源
        LXDH      VARCHAR(32 ),        --信息来源联系电话
        FKRQ      DATE           --反馈时间
    ) ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED;


3. MySQL 5.X

基本上就是会话级的,与PG类似,但没有PG语法完整。

建临时表很容易,给正常的CREATE TABLE语句加上TEMPORARY关键字:
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)
临时表将在你连接MySQL期间存在。当你断开时,MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。
DROP TABLE tmp_table
如果在你创建名为tmp_table临时表时名为tmp_table的表在数据库中已经存在,临时表将有必要屏蔽(隐藏)非临时表tmp_table。
如果你声明临时表是一个HEAP表,MySQL也允许你指定在内存中创建它:
 CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP
因为HEAP表存储在内存中,你对它运行的查询可能比磁盘上的临时表快些。然而,HEAP表与一般的表有些不同,且有自身的限制。

4. Sybase ASE15.X

基本上就是比较裸露的语法了:
create table tempdb..t(id int primary key), 这个等价于global temp table t, server级的, 各连接都可以访问,需要显式的drop
create table #t(id int primary key), 这个等价于local temp table t,  会话级的,连接断开时会自动删除

5. MS SQL Server 200X
与Sybase类似,但多出一个变种
create table ##t(id int primary key), 这个等价于global temp table t, server级的, 各连接都可以访问,需要显式的drop
create table #t(id int primary key), 这个等价于local temp table t,  会话级的,连接断开时会自动删除

最后,Sybase ASE以及MS SQL Server使用中,需要注意会话的自动产生及断开
比如一个变量:

declare @vsql varchar(4096)
set @vsql="select * into #t123 from emp"
execute (@vsql)
set @vsql="select top 3 * from #t123"
execute (@vsql)
当你执行这个段时,你会发现,最后提示#t123不存在,因为execute在执行完时,会话即退出。要想执行成功,有两种方法:
1. 将两个sql合成一段来执行 : set @vsql = @vsql || "  select top 3 * from #t123"
2. 使用global temp table, 即tempdb..t123或者SQLServer中的##t123

临时表,在ASE, SQLServer中使用频率非常高,经常出现在存储过程当中。

6. Oracle9.x and later
语法与PG一样,还提供on commit preserve rows, 和on commit delete rows功能。
但是只提供global功能,意即表本身不会自动删除。 只是提供是否保留数据的功能。这样,preserve rows相当于会话级,而on commit delete rows则相当于事务级的临时表了。用处还是蛮大的。
缺点还有:
1)不支持lob对象,这也许是设计者基于运行效率的考虑,但实际应用中确实需要此功能时就无法使用临时表了。
2)不支持主外键关系

总之,临时表的实现和使用,还真没有一个十全十美的商家。多留心一点就是了。

阅读(3228) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册