Oracle与SQL Server临时表的实践小结

cuicai6848

于 2007-06-30 19:32:31 发布

阅读量71

点赞数

[@more@]

在写非常复杂的业务逻辑时，不免用到临时表。临时表生成很少的日志，每种数据库又都会对其进行特定的处理，使它很适合保存事务或会话期间的中间结果集。
Oracle 临时表保存的数据只对当前会话可见，所有会话都看不到其他会话的数据。即使当前会话已经提交了数据，别的会话也看不到。对于临时表，不存在多用户并发问题，因为一个会话不会因为使用一个临时表也阻塞另一个会话。Oracle的临时表是从当前登录用户的临时表空间分配存储空间，而在创建时不涉及存储空间的分配。Oracle中的临时表是全局临时表，是和其它表一样应该提前建好的，而不是在存储过程中创建、删除的。Oracle的临时表分为两种：基于会话的临时表和基于事务的临时表。基于会话的临时表，其中存储的数据可以跨越事务，但是断开连接后数据就没有了。而基于事务的临时表，提交之后数据就没有了。在临时表的自动清除过程中不存在开销。

以下是我在SQL*Plus中的试验：
在会话甲中
创建一个用于测试的表 t
SQL> create table t
2 (x int
3 );
表已创建。
创建一个基于会话的临时表 tmp_sess
SQL> create global temporary table tmp_sess
2 (x int
3 ) on commit preserve rows;
表已创建。
创建一个基于事务的临时表 tmp_tran
SQL> create global temporary table tmp_tran
2 (x int
3 ) on commit delete rows;
表已创建。
然后向表t中插入三条数据
SQL> insert into t values(1);

已创建 1 行。

SQL> insert into t values(2);

已创建 1 行。

SQL> insert into t values(3);

已创建 1 行。

SQL> commit;

提交完成。
再创建一个存储过程 tmp_table，先使用基于事务的临时表
SQL> create or replace procedure tmp_table
2 is
3 begin
4 insert into tmp_tran
5 select sum(x) from t;
6 commit;
7 end;
8 /

过程已创建。

SQL> call tmp_table();

调用完成。

SQL> select * from tmp_tran;

未选定行

说明提交时基于事务的临时表中的数据已经被清除了。
然后打开另一会话乙：
SQL> select * from tmp_tran;
未选定行

在会话甲中
将存储过程tmp_table中的临时表改为基于会话的临时表tmp_sess
SQL> create or replace procedure tmp_table
2 is
3 begin
4 insert into tmp_sess
5 select sum(x) from t;
6 commit;
7 end;
8 /

过程已创建。

SQL> call tmp_table();

调用完成。

SQL> select * from tmp_sess;

X
----------
6

SQL> call tmp_table();

调用完成。

SQL> select * from tmp_sess;

X
----------
6
6

第一次调用存储过程tmp_table，然后查询得到一条记录；第二次调用，然后再查询得到两条记录。说明基于会话的临时表是跨越事务的。
在会话乙中
SQL> select * from tmp_sess;

未选定行

说明基于会话的临时表里的数据是不能被另一个会话看到的。
在会话甲中
SQL> disconnect;
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开

SQL> connect cuixz/cuixz@cuixz;
已连接。
SQL> select * from tmp_sess;

未选定行

说明基于会话的临时表断开时数据已经被Oracle自动清除了

SQL Server的临时表是存储在tempdb中，应该在存储过程中创建或者删掉的。它也分为两种：本地临时表和全局临时表。本地临时表以#开头，仅对当前连接有效，当与SQL Server连接断开时此表即被删除，如果是在存储过程中创建的，则存储过程执行完此表即被删除。当不同的用户创建本地临时表名相同时，SQL Server会自己在每个用户创建的临时表透明的加一下数据后辍（加此后辍是透明的，不影响各个用户程序对此临时表的使用）。因此本地临时表也不会发生并发问题。全局临时表以##开头，对所有会话都可见，只有所有引用该表的会话都断开连接时，才将此表删除。如果是在存储过程中创建的，则调整用此存储过程的会话断开后，此全局临时表即被删除。全局临时表中的数据会被其它会话看到，因此和普通表一样，存在多用户并发问题。
以下是我在SQL Server查询分析器里试验的结果
在查询分析器甲中
创建测试用的表t
create table t
(x int
);
命令已成功完成。

向表t中添加三条数据
insert into t values(1);
insert into t values(2);
insert into t values(3);

（所影响的行数为 1 行）

（所影响的行数为 1 行）

（所影响的行数为 1 行）

再创建一个存储过程 tmp_table，使用本地临时表
create procedure tmp_table
as
create table #tmp_local
(x int)
insert into #tmp_local
select sum(x) from t

命令已成功完成。

exec tmp_table
（所影响的行数为 3 行）

select * from #tmp_local
服务器: 消息 208，级别 16，状态 1，行 1
对象名