跨越Oracle和MSSQL关系数据库开发 -- 06 临时表

来源：互联网发布：c2c网络平台有哪些编辑：程序博客网时间：2024/04/24 15:42

1. 概述

在编写存储过程时，我们经常用到临时表。临时表可以简化查询语句，能作为计算的中间结果，有时还能减少计算量，提高效率。

但是Oracle和MSSQL在临时表的使用上存在较大差异。尤其对于从MSSQL转向Oracle的开发人员，一开始往往对这种差异难以接受。

本文将详细介绍MSSQL和Oracle的临时表用法，并介绍不同数据库的差异和一些常用技巧。

2. 在MSSQL中使用临时表

2.1 MSSQL的临时表概念

在MSSQL中，所谓临时表，即是临时的表。我们可以在T-SQL的任何地方临时地创建一个表，使用完毕之后就可以把这个表删除掉，删除可以让MSSQL自动完成，也可以编码进行删除。

MSSQL的临时表又分为局部临时表和全局临时表。局部临时表在表名前用一个井号(#)，全局临时表在表名前用两个井号(##)。

下面谈谈MSSQL是如何自动删除临时表的。

假如我们在一个存储过程中创建临时表，当存储过程完成时，将自动删除在存储过程中创建的局部临时表。由创建表的存储过程执行的所有嵌套存储过程都可以引用此表。但调用创建此表的存储过程的其他存储过程或者外部进程无法引用此表。

如果不是在存储过程中创建局部临时表，那么局部临时表将在会话结束的时候被自动删除。

对于全局临时表，由于可以被创建会话之外的作用域所引用，所以只有在创建会话结束，并且没有其他会话引用时才被自动删除。

在MSSQL中，临时表的表名长度不能超过116，这包括了前面的井号。

2.2 在存储过程中使用临时表

现在，让我们来看看如何在存储过程中使用临时表。

为了说明问题，我们首先定义一个表。这个表是一个交易流水表。

create table fundjour

(

serial_no numeric(8) not null, /*流水号 */

client_account char(16) not null, /*客户帐号*/

fare_kind char(1) not null, /*金额类别*/

balance numeric(19,2) not null /*交易金额*/

)

insert into fundjour values(1, '100008', '1', 18000)

insert into fundjour values(2, '100300', '2', 34080)

insert into fundjour values(3, '203810', '2', 900100.08)

insert into fundjour values(4, '008194', '3', 11219.4)

insert into fundjour values(5, '500800', '1', 8709)

insert into fundjour values(6, '200300', '1', 49555)

insert into fundjour values(7, '110783', '3', 4545)

insert into fundjour values(8, '108000', '3', 92320)

现在，我准备用一个存储过程来返回对该表进行的一个统计。我要统计出各个交易金额类别的交易笔数和交易金额，还要报告所有金额类别的总计，并且要求返回一个格式化好的表格。显然，用单个的SQL语句是有一定难度的——当然，本例不够复杂，或者有的读者能够用一个单一的SQL语句完成上述任务——这时，我们就有机会来显示临时表的威力了。

首先，让我们来看存储过程代码：

create procedure sp_tot_fundjour

set nocount on

set rowcount 0

create table #jour

(

line_no int null,

tot_str varchar(64) null,

count_num int null,

sum_balance numeric(19,2) null

)

insert into #jour values(0, '| 金额类别 | 交易笔数 | 交易金额 |', 0, 0)

insert into #jour values(1, '|----------|----------|----------|', 0, 0)

insert into #jour values(3, '|----------|----------|----------|', 0, 0)

insert into #jour

select 2,

(case fare_kind

when '1' then '柜台'

when '2' then '转帐'

when '3' then '汇款'

when '4' then '网上支取'

else '未知'

end),

count(*),

sum(balance)

from fundjour

group by fare_kind

update #jour set tot_str = '|' + convert(char(10), tot_str) +

'|' + convert(char(10), count_num) +

'|' + convert(char(10), sum_balance) + '|'

where line_no = 2

insert into #jour

select 4,

'| 合计|',

sum(count_num),

sum(sum_balance)

from #jour

where line_no = 2

update #jour set tot_str = tot_str +

convert(char(10), count_num) + '|' +

convert(char(10), sum_balance) + '|'

from #jour

where line_no = 4

select tot_str from #jour order by line_no

然后，我们在查询分析器中执行这个存储过程：

exec sp_tot_fundjour

得到的结果是：

| 金额类别 | 交易笔数 | 交易金额 |

|----------|----------|----------|

|柜台 |3 |76264.00 |

|转帐 |2 |934180.08 |

|汇款 |3 |108084.40 |

|----------|----------|----------|

| 合计|8 |1118528.48|

哇！这就是一个很好的报表嘛！上面的存储过程是如何做到这一点呢？让我们一起来看个究竟：首先，我们需要一个临时表，我们给它的名字为#jour。注意其中的line_no字段，这是我们引入的一个工作列，用于标识符合某种条件的记录，并且在最后返回结果集的时候起排序作用，以便控制格式。然后，我们向临时表中插入格式控制记录。再接下来，我们插入单个金额类型的统计结果，并且对这些结果进行格式化。最后，我们插入合计统计记录并对此进行格式化。

上面的例子中，我们向临时表插入了数据，又读取其中的数据进行处理，然后又插入或者修改其中的数据。这种反复进出数据正是引入临时表概念的最好理由。

细心的读者可能会提问，前面说到局部临时表在存储过程结束时将被自动删除掉，那么客户端在调用完存储过程后怎么可能获取一个基于不存在的表的结果集呢？

如果你也问到了这个问题，那么恭喜你，你已经开始进入现代数据库的一个基本设计理念了。这就是：数据库同时允许同一数据的多个版本存在并被访问。这说起来似乎有点拗口。关于这个话题，可能也得用好多页才能说清楚。不过我不想细谈这个问题，有兴趣的读者可以去参考《Expert_One-on-One Oracle》。

这里我举一个简单的例子。如果一个用户正在删除某个记录，但是事务正在处理尚未提交的同时，另一个用户对该记录进行了查询，那么另一个用户得到的是什么记录呢？应该是删除事务开始之前的那条记录！

回到我们的问题上来。在临时表被自动删除之前，我们已经用查询语句获得了结果集，此结果集被创建出来，并且存放在某个地方，我们不用去管，但是数据库保证了即使表被删除，这个结果集还是能够存在，直到使用结束才被解散。

现在让我们来试验以下几种情况，以便加深对上述描述的理解：

(1)在最后一个查询语句后加上删除记录语句

delete from #jour

此时，结果不会有任何改变。

(2)在最后一个查询语句后加上删除表语句

drop table #jour

此时，结果同样不会有任何改变。

(3)在最后一个查询语句前加上删除记录语句

此时，结果集变成空。

(4)在最后一个查询语句前加上删除表语句

此时，结果报错：对象名 '#jour' 无效。

　　在使用完临时表后，将其显式drop掉是一种好的经验。这可以避免在同一上下文中使用同名表时发生冲突。

2.2 使用临时表的一些经验

2.2.1 不插入大量数据

通常我们建临时表的时候是不建索引的。对于没有索引的表，如果其中有大量数据，那么做操作时的开销就比较大了。操作包括排序、统计、按条件查询等。因此，从经验上来说，不要向临时表插入成千上万条记录。

如果要有条件的选择临时表中的数据，我们也可以给临时表建立索引。然而建立索引是一种比较耗时的操作，不适合在短小事务内使用。

例如在上面的例子中，我们可以在建立临时表后在line_no列上建立索引，因为我们后面的查询中使用该列作为条件。

create index idx_temp_jour on #jour(line_no)

在临时表被删除时，相关的索引也会被自动删除。

2.2.2 显式指明列的空值属性

在创建临时表时，最好显式指明列是否可以为空。在大多数情况下，应该允许列为空。为什么呢？第一，临时表中的数据通常时经过运算得来的，这些运算很可能得到空值。第二，MSSQL中建表时列的缺省空值属性是可以配置的，如果不显式指明，结果可能是允许空，也可能不允许空。

2.2.3 不在高性能要求的联机事务中使用临时表

建立临时表的语句属于DDL，这比DML要耗费更多的时间和资源。在并发性能要求很高的事务中使用临时表会大大降低应用系统的效率。

2.2.4 不能在分支中创建同名临时表

MSSQL在处理临时表时并没有成功使用滞后编译的能力，但是对常规表却做的很好。我们可以对比以下两个短小的存储过程的编译结果。

create procedure sp1

declare @choice char(1)

select @choice = '1'

if @choice = '1'

create table #blabla(col_a int)

else

create table #blabla(col_a char(10))

create procedure sp2

declare @choice char(1)

select @choice = '1'

if @choice = '1'

create table blabla(col_a int)

else

create table blabla(col_a char(10))

我们发现，第一个存储过程的编译结果是：

数据库中已存在名为 '#blabla' 的对象。

而第二个存储过程却成功被编译。而与第一个相比，仅仅是将临时表换成了常规表。

看来，MSSQL在判断临时表时，仅仅判断同名临时表是否在前面的代码中出现过，而不进行语义上的检查。

好在这种缺陷对开发人员几乎没有什么影响。读者只要知道这个事情，一般不必想办法去解决之。当然，如果一定要解决，还是有办法的。办法之一是使用动态语句，办法之二是在在选择语句开始前就创建临时表，然后在分支语句中使用alter table语句，这样也能通过编译。

create procedure sp1

declare @choice char(1)

select @choice = '1'

create table #blabla(col_a char(1))

if @choice = '1'

alter table #blabla alter column col_a int

else

alter table #blabla alter column col_a char(10)

3. 在Oracle中使用临时表

3.1 Oracle的临时表概念

在Oracle中，临时表的概念与MSSQL完全不同。实际上，Oracle并不存在临时的表，在Oracle中，所谓的临时表包含两个概念：一是永久的表结构，二是临时的数据。在MSSQL中，无论是表结构还是数据，都是临时的。

Oracle的临时表既然拥有永久的表结构，那就意味着不需要在存储过程中创建之，而可以像创建常规表那样预先创建之。

Oracle的临时表用于存放临时的数据。这种数据的存在范围分成两种情况。一是仅存于事务生命周期内，二是仅存在于会话生命周期内。

临时表的定义除了特殊的定义关键字之外，其余建表特性都可以被起用，包括在临时表上可以建立索引。

3.2 在存储过程中使用临时表

　　我们使用与介绍MSSQL时同样的表来介绍如何使用Oracle的临时表。

create table fundjour

(

serial_no number(8) not null, /*流水号 */

client_account char(16) not null, /*客户帐号*/

fare_kind char(1) not null, /*金额类别*/

balance number(19,2) not null /*交易金额*/

);

insert into fundjour values(1, '100008', '1', 18000);

insert into fundjour values(2, '100300', '2', 34080);

insert into fundjour values(3, '203810', '2', 900100.08);

insert into fundjour values(4, '008194', '3', 11219.4);

insert into fundjour values(5, '500800', '1', 8709);

insert into fundjour values(6, '200300', '1', 49555);

insert into fundjour values(7, '110783', '3', 4545);

insert into fundjour values(8, '108000', '3', 92320);

commit;

在创建存储过程前，我们就可以将临时表建好。下面是建表的脚本：

create global temporary table temp_jour

(

line_no number(8) null,

tot_str varchar2(64) null,

count_num number(8) null,

sum_balance number(19,2) null

) on commit delete rows;

好，现在一切都准备好了，下面是存储过程的代码。为了方便读者实验，我包含了一个定义游标引用的包。

create or replace package CustomType is

TYPE T_CURSOR IS REF CURSOR;

end CustomType;

create or replace procedure sp_tot_fundjour

(

p_cursor out CustomType.T_CURSOR

)

begin

insert into temp_jour values(0, '| 金额类别 | 交易笔数 | 交易金额 |', 0, 0);

insert into temp_jour values(1, '|----------|----------|----------|', 0, 0);

insert into temp_jour values(3, '|----------|----------|----------|', 0, 0);

insert into temp_jour

select 2,

decode(fare_kind,

'1', '柜台',

'2', '转帐',

'3', '汇款',

'4', '网上支取',

'未知'),

count(*),

sum(balance)

from fundjour

group by fare_kind;

update temp_jour set tot_str = '|' || rpad(tot_str, 10) ||

'|' || rpad(to_char(count_num), 10) ||

'|' || rpad(to_char(sum_balance), 10) || '|'

where line_no = 2;

insert into temp_jour

select 4,

'| 合计|',

sum(count_num),

sum(sum_balance)

from temp_jour

where line_no = 2;

update temp_jour set tot_str = tot_str ||

rpad(to_char(count_num), 10) || '|' ||

rpad(to_char(sum_balance), 10) || '|'

where line_no = 4;

open p_cursor for

select tot_str from temp_jour order by line_no;

end sp_tot_fundjour;

如果运行上面的存储过程，我们能够得到本文中前面所提到的带格式化的统计结果。

我们高兴的发现，我们不再需要在存储过程中关心如何创建表结构。这无疑简化了存储过程的编写。

读者可能会问，上面的存储过程如果被多个用户同时调用，那么数据会不会乱套呢？这种担心是多余的，Oracle已经替你考虑好了一切，您尽管放心使用。而我在下一小节中，就会更加详细地讨论数据的生存周期问题。

3.3临时表中数据的生存周期

在创建临表时，我们可以指定数据的生存周期。Oracle允许两种生存周期：事务内有效，会话内有效。分别用on commit delete rows和on commit preserve rows表示。

在上一小节中我们使用的是第一种临时表，即数据仅在事务内有效。因为存储过程结束时事务也自动结束，因此存储过程结束是在游标被打开之后，故而游标中有数据返回。

我们可以试验一下，在游标打开前执行一下commit：

create or replace procedure sp_tot_fundjour

(

p_cursor out CustomType.T_CURSOR

)

begin

insert into temp_jour values(0, '| 金额类别 | 交易笔数 | 交易金额 |', 0, 0);

insert into temp_jour values(1, '|----------|----------|----------|', 0, 0);

insert into temp_jour values(3, '|----------|----------|----------|', 0, 0);

insert into temp_jour

select 2,

decode(fare_kind,

'1', '柜台',

'2', '转帐',

'3', '汇款',

'4', '网上支取',

'未知'),

count(*),

sum(balance)

from fundjour

group by fare_kind;

update temp_jour set tot_str = '|' || rpad(tot_str, 10) ||

'|' || rpad(to_char(count_num), 10) ||

'|' || rpad(to_char(sum_balance), 10) || '|'

where line_no = 2;

insert into temp_jour

select 4,

'| 合计|',

sum(count_num),

sum(sum_balance)

from temp_jour

where line_no = 2;

update temp_jour set tot_str = tot_str ||

rpad(to_char(count_num), 10) || '|' ||

rpad(to_char(sum_balance), 10) || '|'

where line_no = 4;

commit;

open p_cursor for

select tot_str from temp_jour order by line_no;

end sp_tot_fundjour;

执行该存储过程，此时，我们发现没有数据被返回，因为在commit时，事务结束，上面查入的所有数据都被删除了。

如果我们将临时表的定义修改成第二种：

create global temporary table temp_jour

(

line_no number(8) null,

tot_str varchar2(64) null,

count_num number(8) null,

sum_balance number(19,2) null

) on commit preserve rows;

再执行含有commit语句的sp_tot_fundjour过程，我们发现又能查询到数据了。这是因为在事务结束时，会话还在继续，因此数据被保留下来了。

3.4使用动态语句生成临时表

有些从MSSQL转到Oracle的开发人员一开始对Oracle的临时机制不了解，用起来非常不习惯，因此他们有时采用一种强硬的手段，模拟MSSQL的机制来生成所谓的临时表，即数据和表结构都是临时的，存储过程结束后一切都被删除。

这并非不可能。但是由于PL/SQL通常不允许将DDL书写在存储过程代码中，因此要使用动态语句来生成，并且由于在存储过程编译时，并不知道表是否存在，因此对这些表的操作都必须使用动态语句执行。在使用完毕之后，还要将用动态语句将表删除掉。否则该存储过程第二次执行时即报错。

很不幸，这种做法是极不明智的。除了代码书写无比麻烦之外，效率之低下更是无法形容。

3.5使用表函数替代临时表

在3.4节中我们指出在Oracle中模拟MSSQL的临时表是不明智的。但是Oracle的临时表有时显得不够灵活：事先建立表结构有时是很麻烦的，这等于是要求改变schema。

在Oracle中有一种增强的功能叫做“表函数”，可以用来减少对临时表的使用。本书中该种函数的说明在“存储过程和包”一章。更详细定义可参考Oracle的手册。

下面是相关的代码：

CREATE TYPE Line_t AS OBJECT

( line_no number(8) null,

tot_str varchar2(64) null,

count_num number(8) null,

sum_balance number(19,2) null

);

CREATE TYPE LineSet_t AS TABLE OF Line_t;

create or replace function sp_tot_fundjour_2

return LineSet_t pipelined as

out_line Line_t := Line_t(NULL, NULL, NULL, NULL);

begin

out_line.line_no := 0;

out_line.tot_str := '| 金额类别 | 交易笔数 | 交易金额 |';

out_line.count_num := 0;

out_line.sum_balance := 0;

PIPE ROW(out_line);

out_line.line_no := 1;

out_line.tot_str := '|----------|----------|----------|';

PIPE ROW(out_line);

out_line.line_no := 3;

PIPE ROW(out_line);

for r in (select 2 as line_no,

decode(fare_kind,

'1', '柜台',

'2', '转帐',

'3', '汇款',

'4', '网上支取',

'未知') as tot_str,

count(*) as count_num,

sum(balance) as sum_balance

from fundjour

group by fare_kind)

loop

out_line.line_no := r.line_no;

out_line.tot_str := r.tot_str;

out_line.count_num := r.count_num;

out_line.sum_balance := r.sum_balance;

out_line.tot_str := '|' || rpad(out_line.tot_str, 10) ||

'|' || rpad(to_char(out_line.count_num), 10) ||

'|' || rpad(to_char(out_line.sum_balance), 10) || '|';

PIPE ROW(out_line);

end loop;

out_line.line_no := 4;

out_line.tot_str := '| 合计|';

select count(*), sum(balance) into out_line.count_num, out_line.sum_balance

from fundjour;

out_line.tot_str := out_line.tot_str ||

rpad(to_char(out_line.count_num), 10) || '|' ||

rpad(to_char(out_line.sum_balance), 10) || '|';

PIPE ROW(out_line);

RETURN;

end sp_tot_fundjour_2;

需要获取数据时，只要简单的使用以下代码即可：

select t.tot_str from TABLE(sp_tot_fundjour_2) t order by t.line_no;

此时，就可以获取与前面一模一样的结果：

TOT_STR

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

| 金额类别 | 交易笔数 | 交易金额 |

|----------|----------|----------|

|柜台 |3 |76264 |

|汇款 |3 |108084.4 |

|转帐 |2 |934180.08 |

|----------|----------|----------|

| 合计|8 |1118528.48|

7 rows selected

上面的代码中，虽然没有使用临时表，但是必须事先定义两个类型。在代码中使用PIPE ROW函数时实际上是一行一行的放数据。这比起临时表来有时要复杂一点，因为临时表中很容易获取前面步骤中放进去的数据，但是在表函数中这是比较困难的。