SQL临时表

浏览数：19 / 时间：2015年06月12日

1、MS SQLSERVER      SQL Server 支持临时表。临时表就是那些名称以井号 (#) 开头的表。如果当用户断开连接时没有除去临时表，SQL Server 将自动除去临时表。临时表不存储在当前数据库内，而是存储在系统数据库 tempdb 内。
     临时表有两种类型：
     本地临时表：本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 Microsoft SQL Server 2000 实例断开连接时被删除。
     全局临时表：全局临时表的名称以数学符号 (##) 打头，创建后对任何用户都是可见的。如果在创建全局临时表的连接断开前没有显式地除去这些表，那么只要所有其它任务停止引用它们，这些表即被除去。当创建全局临时表的连接断开后，新的任务不能再引用它们。当前的语句一执行完，任务与表之间的关联即被除去；因此通常情况下，只要创建全局临时表的连接断开，全局临时表即被除去。
     例如，如果创建名为 employees 的表，则任何人只要在数据库中有使用该表的安全权限就可以使用该表，除非它已删除。如果创建名为 #employees 的本地临时表，只有您能对该表执行操作且在断开连接时该表删除。如果创建名为 ##employees 的全局临时表，数据表中的任何用户均可对该表执行操作。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果该表在您创建后有其他用户使用，则 SQL Server在所有用户断开连接后删除该表。
     现在，临时表的许多传统用途可由具有 table 数据类型的变量替换。

1.SQL SERVER临时表实用大全
老蓝笔记整理：
引子：
            临时数据表格，我们在存储的时候经常遇见。
　　客户端可以实用Delphi的ClientDataSet的内存表，但是ClientDataSet类似TABLE，不是支持SQL语言的。
　　当然也可以实用临时表。有时我们避免临时表的生命周期的麻烦，更多的使用实际表临时用用。起个什么tempXXX类似的告诉我们他们是临时表。创建释放也没有问题。
　　那么临时表的周期是什么样子的呢？有什么特殊的用法没有？
1.全局临时表和临时表的区别：视野不同。
　　　全局临时表当然就是使用##打头的表格，普通的临时表格为#打头。它们的周期应该随一个连接也就是Connection的诞生而生，随着Connection的断开而死亡。它们的区别不同的地方也就是视野不同。
　　　全局表，全部授权的Connection都可以看见。但是普通临时表（局域临时表）仅仅创建的Connection可以看见。特别说明的是sql server的isql.exe每个连接就是一个Connection。
　　　例如：一个应用程序app.exe有仅adoconnection连接SQL SERVER数据，那么这个连接中的adoquery或者存储过程建立的全局表##temp1，另外的一个app.exe或者其它的appx.exe都可以使用该表格。
　　　不同的是普通临时表，由app.exe中的连接connection比如adoconnection的Adoquery建立的临时表#temp，在所有该连接的该程序的app.exe都可以看见并且访问，但是appx.exe或者其它的就不能看见。
陷阱：如果一个应用程序app.exe有存储过程和TADOQUERY连接同一个ADOCONNECTION.切记使用存储过程建立的临时表（非全局），就不能被这个连接下的TADOQUERY访问。原因是：存储过程本身是服务器端执行，应该是服务器的CONNECTION。
2.全局表或者临时表的存储和建立。
　　　估计大家都知道，临时表或者全局表都是在sql server的tempdb数据库存储，表我们访问的时候使用#，但是实际在进程里面是以在tempdb里面的用户表以#xxx_________00000000xxx方式存储（是否是进程标识，不大知道，也不想知道）。可以通过isql.exe的tempdb里面看见它们。
3。头痛初始化表格
　　初始化表格，为什么头痛，毕竟使用Drop什么的。或者判断存在比较麻烦。其实也很简单。另外建立临时表，都是无聊的SQL 语句，怎么处理。
方法一：当然是使用存储过程了。可以直接写SQL语句或者写exec (@v_sql)。
方法二：把临时表保存到数据里面MEMO里面，读取，直接执行。
　　判断方法一：可以采用常用的exists函数。
　　　例如：if        exists        (select        *        from        tempdb..sysobjects        where        id        =        object_id(@sTmpWareA)        and        type        =        "U")
　　判断方法二：
　　if object_id(‘tempdb.dbo.##‘+@v_userid) is null
应用方法一：（感谢阿满，迷糊）
    一个统计的临时表格，使用TADOPROC执行存储过程，如下：
CREATE PROCEDURE InitCreateCoawardTable AS
BEGIN
    IF not (object_id(‘tempdb.dbo.#Tmp_Detail‘) is null)
     drop table tempdb.dbo.#Tmp_Detail
     CREATE TABLE tempdb.dbo.#Tmp_Detail (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[单据类别] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[单据编号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
（。。这里多复杂都可以。。。。。省略。。。）
    select * from tempdb.dbo.#Tmp_Detail
END
GO
在客户端使用TADOPROC执行这个存储过程，
    with sproc do
    begin
      try
       Close;
       ProcedureName:=spName;
       Parameters.Refresh;
       Prepared;
       ExecProc;
       Open;//这句不能省略要不，就出现忽略了数据库名称 ‘，将引用 tempdb 中的对象错误。
       except
         Close;
         Exit;
       end;
       Result:=True;
    end;
这样，你的TADOPROC就可以任意使用APPEND,INSERT语句了，更好的是不需要清理，而且方法灵活。更好的有点非常有效的处理并发。当然有些人还在使用建立实际表格代替它，使用会话处理该问题，你还得回收。相当麻烦。而且灵活度差多了。

以下是应用范例转载：
利用SQL Server的全局临时表防止用户重复登录　　　
在我们开发商务软件的时候，常常会遇到这样的一个问题：怎样防止用户重复登录我们的系统？特别是对于银行或是财务部门，更是要限制用户以其工号身份多次登入。
        可能会有人说在用户信息表中加一字段判断用户工号登录的状态，登录后写1，退出时写0，且登录时判断其标志位是否为1，如是则不让该用户工号登录。但是这样那势必会带来新的问题：如发生象断电之类不可预知的现象，系统是非正常退出，无法将标志位置为0，那么下次以该用户工号登录则不可登入，这该怎么办呢？
        或许我们可以换一下思路：有什么东西是在connection断开后可以被系统自动回收的呢？对了，SQL Server的临时表具备这个特性！但是我们这里的这种情况不能用局部临时表，因为局部临时表对于每一个connection来说都是一个独立的对象，因此只能用全局临时表来达到我们的目的。
        好了，情况已经明朗话了，我们可以写一个象下面这样简单的存储过程:

create procedure gp_findtemptable -- 2001/10/26 21:36 zhuzhichao in nanjing
/* 寻找以操作员工号命名的全局临时表
* 如无则将out参数置为0并创建该表,如有则将out参数置为1
* 在connection断开连接后,全局临时表会被SQL Server自动回收
* 如发生断电之类的意外,全局临时表虽然还存在于tempdb中,但是已经失去活性
* 用object_id函数去判断时会认为其不存在. */
@v_userid varchar(6), -- 操作员工号
@i_out int out -- 输出参数 0:没有登录 1:已经登录
as
declare @v_sql varchar(100)
if object_id(‘tempdb.dbo.##‘+@v_userid) is null
begin
        set @v_sql = ‘create table ##‘+@v_userid+‘(userid varchar(6))‘
        exec (@v_sql)
        set @i_out = 0
end
else
        set @i_out = 1
        在这个过程中，我们看到如果以用户工号命名的全局临时表不存在时过程会去创建一张并把out参数置为0，如果已经存在则将out参数置为1。
        这样，我们在我们的应用程序中调用该过程时，如果取得的out参数为1时，我们可以毫不客气地跳出一个message告诉用户说”对不起，此工号正被使用！”
判断方法范例：
select        @sTmpWareA="tempdb..[##MARWareA"+        @ComputerName+"]"

if        exists        (select        *        from        tempdb..sysobjects        where        id        =        object_id(@sTmpWareA)        and        type        =        "U")
            begin
                  set        @sTmpWareA="[##MARWareA"+        @ComputerName+"]"
                  exec(        "drop        table        "        +@sTmpWareA        )
            end
else
                  set        @sTmpWareA="[##MARWareA"+        @ComputerName+"]"

@sTmpWareA        就是临时表的名称，过程中使用exec来操作