Oracle和SQLServer的“临时表”应用

Oracle中，

      在复杂的业务逻辑中，我们经常会用到临时表，临时表生成很少的日志，每种数据库又都会对其进行特定的处理，使它很适合保存事务或会话期间的中间结果集。Oracle临时表保存的数据只对当前会话可见，所有会话都看不到其他会话的数据。即使当前会话已经提交了数据，别的会话也看不到。对于临时表，不存在多用户并发问题，因为一个会话不会因为使用一个临时表也阻塞另一个会话。

      Oracle的临时表是从当前登录用户的临时表空间分配存储空间，而在创建时不涉及存储空间的分配。Oracle中的临时表是全局临时表，是和其它表一样应该提前建好的，而不是在存储过程中创建、删除的。 

      Oracle中的临时表有两种一种是事务级别的临时表它在事务结束的时候自动清空记录，另一种是会话级的它在我们访问数据库是的一个会话结束后自动的清空。关于临时表多用户并行不是问题，一个会话从来不会阻止另一个会话使用临时表。即使“锁定”临时表，一个会话也不会阻止其他会话使用它们的临时表。 例子如下：

首先创建一全局临时表（session），

create global temporary table BANKVAL_TBL
(
  BANKNO       VARCHAR2(64),
  BANKNAME     VARCHAR2(64),
  CNTACNTNO    VARCHAR2(64),
  BANKACNTNO   VARCHAR2(64),
  BANKACNTNAME VARCHAR2(64),
  OPENBANKNAME VARCHAR2(64),
  BALANCE      NUMBER,
  QUOTA        NUMBER,
  FLAG         INTEGER,
  ISLEAF       INTEGER
)
on commit preserve rows;

然后，就可以在存储过程中使用了，

CREATE OR REPLACE PROCEDURE Ns_Qry_GetBankVal_New(
 bankno  in varchar2,
 bankacntno varchar2,
 bankacntname varchar2,
 bankSort varchar2,--帐户性质
 bankKind varchar2,--帐户分类
 cur_OUT    IN OUT GLOBALPKG.RCT1)
is
PRAGMA AUTONOMOUS_TRANSACTION;

begin
  execute immediate 'delete from Bankval_TBL';
  if  bankno is not null and bankno <> ' ' then
    insert into Bankval_TBL
    select a.bankno,'(' || a.bankno || ')' || w.bankname as bankname, '(' ||  v.No  ||  ')' ||  v.name as CntAcntNo ,a.AcntNo as No,a.AcntName As Name,a.OpenBankName,a.Balance,a.Quota,1,1
    from vw_bp_account  a left outer join vw_cntacnt v on a.acntNo=v.No inner join bp_bank w on a.bankno=w.bankno
    and (w.bankno = Ns_Qry_GetBankVal_New.bankno OR NVL(Ns_Qry_GetBankVal_New.bankno,' ') = ' ')
    and a.acntno like '%' ||  nvl(bankacntno,'%')  || '%'
    and a.acntname like '%' ||  nvl(bankacntname,'%')  ||  '%'
    order by a.bankno,CntAcntNo ;
  end if;
  insert into Bankval_TBL
        select bankno,bankname,'银行小计','银行小计','(银行小计)','',sum(balance),sum(quota),0,1
        from Bankval_TBL group by bankno,bankname;

  insert into Bankval_TBL
        select '99',' ',' ',' ','总计','',sum(balance),sum(quota),9,1
        from Bankval_TBL where flag=0;

  commit;
   OPEN cur_OUT FOR
   select nvl(bankno,' ') as  bankno,
       nvl(bankname,' ') as bankname  ,
       nvl(CntAcntNo,' ') as CntAcntNo,
       nvl(Bankacntno,' ') as Bankacntno  ,
       nvl(Bankacntname,' ') as Bankacntname,
       nvl(openbankname,' ') as openbankname ,
       nvl(balance,0) as balance,
       nvl(quota,0) as quota ,
       nvl(flag,0) as flag,
       nvl(isleaf,0) as isleaf from  Bankval_TBL order by bankno,CntAcntNo,flag;
end;

SQL Server：

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
        对象名 '#tmp_local' 无效。

说明执行完存储过程后本地临时表就被SQL Server自动清除了

在另一个查询分析器乙中
        select * from #tmp_local
        服务器: 消息 208，级别 16，状态 1，行 1
        对象名 '#tmp_local' 无效。

在查询分析器甲中
        drop procedure tmp_table
        命令已成功完成。

将存储过程tmp_table中的临时表改为全局临时表##tmp_global
        create procedure tmp_table
        as
               create table ##tmp_global
               (x int)
               insert into ##tmp_global
               select sum(x) from t
命令已成功完成。
        exec tmp_table
        （所影响的行数为 1 行）
        select * from ##tmp_global
        6

在另一个查询分析器乙中
        select * from ##tmp_global
        6

在查询分析器甲中
        exec tmp_table
        服务器: 消息 2714，级别 16，状态 6，过程 tmp_table，行 3
        数据库中已存在名为 '##tmp_global' 的对象。

在查询分析器乙中
        exec tmp_table
        服务器: 消息 2714，级别 16，状态 6，过程 tmp_table，行 3
        数据库中已存在名为 '##tmp_global' 的对象。
在不同的会话中全局临时表表现的都一样。当把查询分析器甲关闭后，在查询分析器乙中执行：
        select * from ##tmp_global
        服务器: 消息 208，级别 16，状态 1，行 1
        对象名 '##tmp_global' 无效。
        exec tmp_table
        （所影响的行数为 1 行）
        select * from ##tmp_global
        6

说明当在存储过程中创建的全局临时表，在被调用的会话连接被断开后，其创建的全局临时表即被SQL Server自动删除

例子如下：

ALTER PROCEDURE [dbo].[Ns_Fix_CtgryStat]
@fromdate datetime,
@todate datetime,
@Opdate datetime
AS
create table #t (
 CtgryNm char(28) not null,/*资产类别名称*/
 Zcyjqc numeric (28,2) null,/*期初原价*/
 Zcyjqm numeric (28,2) null,/*期末原价*/
 Ljzjqc numeric (28,2) null,/*期初累计折旧*/
 Ljzjqm numeric (28,2) null,/*期末累计折旧*/
 Bqzjl numeric (15,6) null,/*本期折旧率*/
 Bqzje numeric (28,2) null,/*本期折旧额*/
 Zcjzqc numeric (28,2) null,/*期初净值*/
 Zcjzqm numeric (28,2) null /*期末净值*/

)
declare @CtgryNm char(28)/*资产类别名称*/
declare @Zcyjqc numeric (28,2)/*期初原价*/
declare @Zcyjqm numeric (28,2)/*期末原价*/
declare @Ljzjqc numeric (28,2)/*期初累计折旧*/
declare @Ljzjqm numeric (28,2)/*期末累计折旧*/
declare @Bqzjl numeric (15,6)/*本期折旧率*/
declare @Bqzje numeric (28,2)/*本期折旧额*/
declare @Zcjzqc numeric (28,2)/*期初净值*/
declare @Zcjzqm numeric (28,2)/*期末净值*/
declare @count int
declare @orgval numeric (28,2) /*资产原价*/
declare @orgchange1 numeric (28,2) /*资产原价变动1*/
declare @orgchange2 numeric (28,2) /*资产原价变动2*/

declare Ctgry_cursor cursor for
 select CtgryNm from FixCtgry
        where ctgryno in (select distinct(ctgryno) from fixcard where mkcrddt < =@todate )

/*对各资产类别逐个计算*/
open Ctgry_cursor
fetch next from Ctgry_cursor into @CtgryNm
while @@fetch_status=0
 begin 
         --期初原值
 select @Zcyjqc = isnull(sum(orgval),0) from FixCard
        where  MkCrdDt < @fromdate and CtgryNo = (select CtgryNo from FixCtgry where CtgryNm=@CtgryNm )
               and (CanclDt is null or cancldt >= @fromdate)
        if @Zcyjqc is null
           set @Zcyjqc = 0

         --期末原值
 select @Zcyjqm = isnull(sum(orgval),0) from FixCard
        where  MkCrdDt <= @todate and CtgryNo = (select CtgryNo from FixCtgry where CtgryNm=@CtgryNm )
               and ((CanclDt is null) or (cancldt >  @todate))
               --or (cancldt between @fromdate and @todate) )
        if @Zcyjqm is null
           set @Zcyjqm = 0

 /*期初累计折旧和期末累计折旧*/
 select @Ljzjqc=isnull(sum(val1),0) from VW_NsFixdpr
        where CtgryNm=@CtgryNm and Opdate < @fromdate and Optype='F00001' and (cancldt is null or cancldt >= @fromdate )
       and mkcrddt <  @fromdate
        if @Ljzjqc is null
           set @Ljzjqc = 0
        --其末累计折旧
 select @Ljzjqm=isnull(sum(val1),0) from VW_NsFixdpr
        where CtgryNm=@CtgryNm and Opdate <= @todate and Optype='F00001' and
         ((cancldt is null) or (cancldt > @todate) )  and  mkcrddt <=  @todate
   --or (cancldt between @fromdate and @todate)) -- or cancldt between @fromdate and @todate
        if @Ljzjqm is null
           set @Ljzjqm = 0

 /* 本期折旧额 */
 select @Bqzje=isnull(sum(val1),0) from VW_NsFixdpr where CtgryNm=@CtgryNm and  Opdate >= @fromdate and Opdate <= @todate and Optype='F00001'
        if @Bqzje is null
           set @Bqzje = 0

 /* 计算本期折旧率 */
 if month(@todate) <> 12
          if @Zcyjqc <> 0         
             set @Bqzjl = 100 * (@Bqzje/@Zcyjqc)
   else
      set @bqzjl = 0         
 else
          if @Zcyjqm <> 0         
             set @Bqzjl = 100 * (@Bqzje/@Zcyjqm)
   else
      set @bqzjl = 0         

 /*期初净值和期末净值*/
 set @Zcjzqc=@Zcyjqc-@Ljzjqc
 set @Zcjzqm=@Zcyjqm-@Ljzjqm

 /*输入到临时表中*/
 insert #t (CtgryNm,Zcyjqc,Zcyjqm,Ljzjqc,Ljzjqm,Bqzjl,Bqzje,Zcjzqc,Zcjzqm)
  values (@CtgryNm,@Zcyjqc,@Zcyjqm,@Ljzjqc,@Ljzjqm,@Bqzjl,@Bqzje,@Zcjzqc,@Zcjzqm)
 fetch next from Ctgry_cursor into @CtgryNm
 end

close Ctgry_cursor
deallocate Ctgry_cursor

declare @dataqc numeric(15,2)
declare @dataqm numeric(15,2)
declare @bqzj numeric(15,2),@data numeric(15,2)
select @dataqc = isnull(sum(zcyjqc),0),@dataqm = isnull(sum(zcyjqm),0),@bqzj = 100*isnull(sum(Bqzje),0) from #t
if month(@todate) <> 12
begin
  if abs(@dataqc) < 0.01
    set @data =0
  else
    set @data = @bqzj/@dataqc
  insert into #T(CtgryNm,Zcyjqc,Zcyjqm,Ljzjqc,Ljzjqm,Bqzjl,Bqzje,Zcjzqc,Zcjzqm)
  select '合计' as CtgryNm,sum(Zcyjqc),sum(Zcyjqm),sum(Ljzjqc),sum(Ljzjqm),@data,sum(Bqzje),sum(Zcjzqc),sum(Zcjzqm)
  from #T
end
else
  begin
  if abs(@dataqm) < 0.01
    set @data =0
  else
    set @data = @bqzj/@dataqm
  insert into #T(CtgryNm,Zcyjqc,Zcyjqm,Ljzjqc,Ljzjqm,Bqzjl,Bqzje,Zcjzqc,Zcjzqm)
  select '合计' as CtgryNm,sum(Zcyjqc),sum(Zcyjqm),sum(Ljzjqc),sum(Ljzjqm),@data,sum(Bqzje),sum(Zcjzqc),sum(Zcjzqm)
  from #T
end

select CtgryNm,Zcyjqc,Zcyjqm,Ljzjqc,Ljzjqm,Bqzjl,Bqzje,Zcjzqc,Zcjzqm from #t

关于Oracle与SQL Server临时表的几点考虑：
        1、DDL操作无论对于Oracle还是SQL Server都是很大的开销；
        2、写存储过程时大可以利用每种临时表的优点，避免使用缺点及重复做系统已经做了的工作；
        3、慎用临时表和其它大数据量表进行连接查询和修改；
        4、对于有大量数据的临时表，可以对此创建索引；
        5、在SQL Server中对于数据量比较少的，用表变量可以有更好的速度；
        6、对于SQL Server中的全局临时表，创建时要进行相应的策略，避免表名重复；
        7、尽量避免在Oracle临时表中作update操作，那样开销特别大；
        8、在Oracle中，不要把临时表作为一个分解大查询的办法，即拿到一个大查询，把它分解为几个较小的结果集，然后把这些结果集并在一起。这样速度会更慢。