SqlServer——临时表
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 ); End declare @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('00'+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('00'+convert(varchar(2),yf),2)=RIGHT('00'+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('00'+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;
如图，可以正常查询。
- 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。
