正文

SqlServer——临时表

IP云
IP云V管理员/5 阅读
07/26
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

1、表的类型：

SqlServer数据库中分为两个表：永久表、临时表；通过表名的前缀区分。

  • 永久表：与物理文件、C# 中的静态类 类似，任何用户均可对其执行操作并且相互影响；
  • 临时表:简单的说就是使用时创建，断开连接即自动 drop。

2、临时表的分类

临时表又分为本地临时表、全局临时表；临时表创建在 tempdb 中。

  • 本地临时表：作用与oracle的会话临时表相似。以 # 开头；打开连接后手动创建，其他用户无法访；，当其他用户创建同名的本地临时表时，SqlServer数据库自动添加后缀以区分。避免相互干扰；当前用户断开连接时自动drop。
  • 全局临时表：表名以 ## 开头；打开连接后手动创建，之后与永久表类似，其他用户也可对其操作。当前用户断开连接后，此后新连接的用户不再被允许操作。当之前访问的用户全部断开连接之后再自动drop。

3、创建临时表：

方法一：
     create table #table_name (字段1 约束条件, 字段2 约束条件)
        方法二：
     select * into #table_name from table1;

4、判断临时表是否存在

if object_id('tempdb..#cxxsybbyd_jb_lbSZQ') is null Begin
CREATE TABLE #cxxsybbyd_jb_lbSZQ(
[f_FL] [varchar](20) NULL,
[f_XH] [varchar](20) NULL,
[f_dwmc] [varchar](100) NULL,
[f_xmmc] [varchar](20) NULL,
[F_QDEM] [float] NULL,
[F_hksrM] [float] NULL,
[F_hkmlM] [float] NULL,
[F_QDEs] [float] NULL,
[F_hksrs] [float] NULL,
[F_hkmls] [float] NULL
);
print 1;
End
else
begin
print 2;
end

判断临时表是否存在

5、临时表的使用

使用步骤如下：

  • 1)、判断临时表是否存在，不存在则创建；
  • 2)、删除临时表数据；
  • 3)、将中间数据存入临时表中；
  • 4)、从临时表中获取数据；

例：

ALTER procedure [LC00019999].[cx_xsybb_yd_jb_lb]
@cxnd varchar(10),
@cxyf varchar(10),
@cxrq varchar(10),
@cylb varchar(20)
-- [cx_xsybb_yd_jb_lb] '2015','12','7','产业单位'
as
begin--没有则创建
if object_id('tempdb..#cxxsybbyd_jb_lbSZQ') is null Begin
CREATE TABLE #cxxsybbyd_jb_lbSZQ(
[f_FL] [varchar](20) NULL,
[f_XH] [varchar](20) NULL,
[f_dwmc] [varchar](100) NULL,
[f_xmmc] [varchar](20) NULL,
[F_QDEM] [float] NULL,
[F_hksrM] [float] NULL,
[F_hkmlM] [float] NULL,
[F_QDEs] [float] NULL,
[F_hksrs] [float] NULL,
[F_hkmls] [float] NULL
);
Enddeclare @yf varchar(2);select @yf= case when substring(convert(varchar(10),getdate(),112),5,2)<4
then convert(varchar(2),substring(convert(varchar(10),getdate(),112),5,2)+12)
else substring(convert(varchar(10),getdate(),112),5,2) end ;delete from #cxxsybbyd_jb_lbSZQ; --使用之前必须delete。
--月度目标
insert into #cxxsybbyd_jb_lbSZQ(f_FL,f_XH,f_dwmc,f_xmmc, F_QDEM,F_hksrM ,F_hkmlM)
select BZ1,BZ2,cymc,RIGHT(''+convert(varchar(2),yf),2),sum(cast(qde as float))as qde,sum(cast(hksr as float)) as hksr,sum(cast(hkml as float)) as hkml
from view_mb,v_xsrb_dwgl WHERE DWMC=CYMC and NF=@cxnd AND RIGHT(''+convert(varchar(2),yf),2)=RIGHT(''+convert(varchar(2),@CXYF),2) group by cymc,BZ1,BZ2,YF;--月度实际
insert into #cxxsybbyd_jb_lbSZQ(f_FL,f_XH,f_dwmc,f_xmmc, F_QDEs,F_hksrs,F_hkmls)
select BZ1,BZ2,v_xsrb_dwgl.DWMC,RIGHT(''+convert(varchar(2),yf),2),sum(cast(qde as float))as qde,sum(cast(hksr as float)) as hksr,sum(cast(hkML as float)) as hkML
from view_yb ,v_xsrb_dwgl WHERE NF=@cxnd and view_yb.DWMC=v_xsrb_dwgl.DWMC and CAST(yf AS int)=CAST(@cxyf AS int)
group by BZ1,BZ2,v_xsrb_dwgl.DWMC,yf; ---结果展示
select f_fl,sum(F_QDEM)QDEM ,SUM(F_QDEs) QDES, SUM(F_hksrM) HKSRM,SUM(F_hksrs) hksrs,
SUM(F_hkmlM)hkmlM ,SUM(F_hkmls) hkmls from #cxxsybbyd_jb_lbSZQ where f_fl like '%'+@cylb+'%'
group by f_fl;
truncate table #cxxsybbyd_jb_lbSZQ;
END

临时表使用示例

问题：临时表在连接断开后自动 drop ，理论上下次连接打开就不存在该临时表，为何还要判断是否存在呢。

解：

1)、理论上连接断开，临时表自动 drop 掉，可以按如下步骤验证：

  • a、在SqlServer管理器中新建查询（姑且认为是查询A），先后执行创建临时表和查询临时表，代码如下：

--1、创建临时表
CREATE TABLE #cxxsybbyd_jb_lbSZQ(
[f_FL] [varchar](20) NULL,
[f_XH] [varchar](20) NULL,
[f_dwmc] [varchar](100) NULL,
[f_xmmc] [varchar](20) NULL,
[F_QDEM] [float] NULL,
[F_hksrM] [float] NULL,
[F_hkmlM] [float] NULL,
[F_QDEs] [float] NULL,
[F_hksrs] [float] NULL,
[F_hkmls] [float] NULL
);
--2、访问临时表
select * from #cxxsybbyd_jb_lbSZQ;

临时表声明周期验证1

如图，可以正常查询。

  • b、此时关闭当前查询A,再次新建查询B，或者不关闭当前查询A，直接新建查询B，执行查询，结果如下：

可以看到刚才创建的临时表不存在。

不同的查询 tap 页，对应着不同的数据库连接，彼此之间创建的临时表相互隔绝。就是说查询A中创建的临时表在查询B中是无法访问的。而且查询A tap页关闭时，对应的数据库连接也就关闭了，临时表drop,再次新建连接也就无法查询到临时表了。

2)、正如1)中所说，数据库连接断开即自动将临时表 drop 掉，数据就更不可能存在。但我们依然必须在创建临时表前判断是否存在并 delete临时表中的数据。原因在于存储过程是在程序中调用执行的。以C#为例，由于与数据库的连接每次打开、关闭要占用一部分时间，为了提高效率，.NET平台提供了连接池的机制。连接池中保存着几条一直处于连接状态（即open）的数据库连接。假设有连接A、B、C 三条连接。我们使用的数据库连接是从 连接池 中获取的连接A，。当我们显式的调用 close() 方法时，实际上仅仅是将连接A放回连接池，并没有关闭连接，该连接创建的临时表也就一直存在。当下一次获取连接时，就有可能获取到之前的连接A，此时你就可以访问到之前创建的临时表，临时表中的数据也存在。为了避免干扰，所以必须要delete。