SQL 临时表

 

 

转自：http://blog.163.com/im_foto/blog/static/49085060200942511938865/

 

1、MS SQLSERVER

     SQL Server 支持临时表。临时表就是那些名称以井号 (#) 开头的表。如果当用户断开连接时没有除去临时表，SQL Server 将自动除去临时表。临时表不存储在当前数据库内，而是存储在系统数据库 tempdb 内。

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

2、ORACLE
     Oracle支持临时表。临时表用来保存事务或会话期间的中间结果。在临时表中保存的数据只有对当前会话是可见的，任何会话都不能看到其他会话的数据，即使在当前会话COMMIT数据以后也是不可见的。多用户并行不是问题，一个会话从来不阻塞另一个会话使用临时表。即使锁定临时表，一个会话也不会阻塞其他会话使用临时表。临时表比正常表产生的REDO少得多，然而，由于临时表必须产生包含数据的UNDO信息，所以会产生一定数量的REDO日志。
     临时表将从用户临时表空间的的目前日志中分配空间，或者如果从有定义权的程序中访问，将使用程序所有者的临时表空间。全局临时表实际上只是表本身的模板。创建临时表的行为不包括存储空间的分配，也不包括INITIAL的分配。因此，在运行时当一个会话首先将数据放到临时表中时，这时将创建这个会话的临时段。由于每个会话获取自己的临时段，每个用户可能在不同的表空间中为临时表分配空间。USER1的default临时表空间为TEMP1，他的临时表将从 TEMP1中分配空间，USER2的default临时表空间为TEMP2，他的临时表将从TEMP2中分配空间。
     临时表在每个数据库中只需创建一次，不必在每个存储过程中创建。临时表总是存在的，除非手动的删除他。临时表作为对象存在数据字典中，并且总是保持为空，直到有会话在其中放入数据。Oracle允许创建基于临时表的视图和存储过程。
     临时表可以是以会话为基础的，也可以是以事务为基础的。ON COMMIT PRESERVE ROWS子句使临时表成为基于会话的模式。行将留在此表中，直到会话断开或通过DELETE或TRUNCATE从物理上删除这些行。ON COMMIT DELETE ROWS子句使临时表成为基于事务的模式。当会话提交后，行消失。这个临时表的自动清除过程不会有额外的开销。
     在oracle中，应用程序需要的临时表应该在程序安装时创建，而不是在程序运行时创建。（这是与ms sqlserver或sybase的使用的不同）
     在任何数据库中，临时表的一个缺点是：事实上优化器在临时表中没有真正的统计功能。然而，在oracle中，一系列较好的统计猜测可以通过DBMS_STATS包在临时表中设置。

3、DB2
    可使用 DECLARE GLOBAL TEMPORARY TABLE 语句来定义临时表。DB2的临时表是基于会话的，且在会话之间是隔离的。当会话结束时，临时表的数据被删除，临时表被隐式卸下。对临时表的定义不会在SYSCAT.TABLES中出现
下面是定义临时表的一个示例：
DECLARE GLOBAL TEMPORARY TABLE gbl_temp
LIKE empltabl
ON COMMIT DELETE ROWS
NOT LOGGED
IN usr_tbsp
此语句创建一个名为 gbl_temp 的用户临时表。定义此用户临时表 所使用的列的名称和说明与 empltabl 的列的名称和说明完全相同。隐式定义只包括列名、数据类型、可为空特性和列缺省值属性。未定义所有其他列属性，包括唯一约束、外部关键字约束、触发器和索引。执行 COMMIT 操作时，若未对该表打开 WITH HOLD 游标，则该表中的所有数据都被删除。不记录对用户临时表所作的更改。用户临时表被放在指定的用户临时表空间中。此表空间必须存在，否则此表的声明将失败。
户定义临时表不支持：
? LOB 类型的列（或基于 LOB 的单值类型列）
? 用户定义类型列
? LONG VARCHAR 列
? DATALINK 列



其创建方法:
create table TempTableName,
或
select [字段1,字段2,...,] into TempTableName from table ,如上所说.

而后便可像使用常规表一样使用它们.

临时表其实是放在数据库tempdb里的一个用户表
分两种:
一种是以#(局部)或##(全局)开头的表,这种表在会话期间存,会话结束则自动删除;
另一种,如果创建时不以#或##开头,而用tempdb.TempTable来命名它,则该表可在数据库重启前一直存在.
以上两种都可手动用
drop table TempTableName 来删除.


请参考－－－动态sql语句基本语法    
  1   :普通SQL语句可以用Exec执行    
   
  eg:       Select   *   from   tableName    
                    Exec('select   *   from   tableName')    
                    Exec   sp_executesql   N'select   *   from   tableName'         --   请注意字符串前一定要加N    
   
  2:字段名，表名，数据库名之类作为变量时，必须用动态SQL    
   
  eg:        
  declare   @fname   varchar(20)    
  set   @fname   =   'FiledName'    
  Select   @fname   from   tableName                             --   错误,不会提示错误，但结果为固定值FiledName,并非所要。    
  Exec('select   '   +   @fname   +   '   from   tableName')           --   请注意   加号前后的   单引号的边上加空格    
   
  当然将字符串改成变量的形式也可    
  declare   @fname   varchar(20)    
  set   @fname   =   'FiledName'   --设置字段名    
   
  declare   @s   varchar(1000)    
  set   @s   =   'select   '   +   @fname   +   '   from   tableName'    
  Exec(@s)                                 --   成功    
  exec   sp_executesql   @s       --   此句会报错    
   
   
   
  declare   @s   Nvarchar(1000)     --   注意此处改为nvarchar(1000)    
  set   @s   =   'select   '   +   @fname   +   '   from   tableName'    
  Exec(@s)                                 --   成功            
  exec   sp_executesql   @s       --   此句正确    
   
  3.   输出参数    
  declare   @num   int,    
                  @sqls   nvarchar(4000)    
  set   @sqls='select   count(*)   from   tableName'    
  exec(@sqls)    
  --如何将exec执行结果放入变量中？    
   
  declare   @num   int,    
                                @sqls   nvarchar(4000)    
  set   @sqls='select   @a=count(*)   from   tableName   '    
  exec   sp_executesql   @sqls,N'@a   int   output',@num   output    
  select   @num    



DECLARE   @fname   varchar(20),  
  @dyni_SQL   varchar(8000),  
  @pcur_day   varchar(20)  
   
  create   table   #tp_res(  
        f1   smalldatetime   NULL,  
        f2   smallint   NULL,  
        f3   float   NULL  
  )  
   
  set   @fname='f1'  
  set   @dyni_SQL='insert   into   #tp_res'   +   '   (   '   +   @fname   +   ')   values   ('''   +     @pcur_day   +   ''')'  
   
  exec   (@dyni_SQL)  
   
  select   *   from   #tp_res  
   
  drop   table   #tp_res  
   
   
  --需要注意，如果字段是数值型，不要用单引号，其他如字符型和日期型都要单引号即：  
  set   @dyni_SQL='insert   into   #tp_res'   +   '   (   '   +   @fname   +   ')   values   ('   +     cast(@pcur_day   as   varchar(50))   +   ')'  


示例:
select * into #tb_demo from employee
select * into #tb_demo1 from jobs
select * from #tb_demo left join #tb_demo1 on #tb_demo.job_id=#tb_demo1.job_id
drop table #tb_demo
drop table #tb_demo1