SQL Server暫存資料表 實用大全

標籤：

我們今天是要和大家一起討論的是SQL Server暫存資料表的實用大全，如果你對SQL Server暫存資料表的實用大全不是很瞭解的話，下面的文章就是對其相關內容的具體介紹，希望會給你帶來一些協助在此方面。

引子：

臨時資料表格，我們在儲存的時候經常遇見。

用戶端可以實用Delphi的ClientDataSet的記憶體表，但是ClientDataSet類似TABLE，不是支援SQL語言的。

當然也可以實用暫存資料表。有時我們避免SQL Server暫存資料表的生命週期的麻煩，更多的使用實際表臨時用用。起個什麼tempXXX類似的告訴我們他們是暫存資料表。建立釋放也沒有問題。

那麼暫存資料表的周期是什麼樣子的呢？有什麼特殊的用法沒有？

1.全域暫存資料表和暫存資料表的區別：視野不同。

 

全域暫存資料表當然就是使用##打頭的表格，普通的暫存資料表格為#打頭。它們的周期應該隨一個串連也就是Connection的誕生而生，隨著Connection的斷開而死亡。它們的區別不同的地方也就是視野不同。

 

 

全域表，全部授權的Connection都可以看見。但是普通SQL Server暫存資料表（局域暫存資料表）僅僅建立的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暫存資料表的儲存和建立。

 

估計大家都知道，暫存資料表或者全域表都是在SQL Server的tempdb資料庫儲存，表我們訪問的時候使用#，但是實際在進程裡面是以在tempdb裡面的使用者表以#xxx_________00000000xxx方式儲存（是否是進程標識，不大知道，也不想知道）。可以通過isql.exe的tempdb裡面看見它們。

 

3。頭痛初始化表格

初始化表格，為什麼頭痛，畢竟使用Drop什麼的。或者判斷存在比較麻煩。其實也很簡單。另外建立暫存資料表，都是無聊的SQL 陳述式，怎麼處理。

方法一：當然是使用預存程序了。可以直接寫SQL語句或者寫exec (@v_sql)。

方法二：把SQL Server暫存資料表儲存到資料裡面MEMO裡面，讀取，直接執行。

判斷方法一：可以採用常用的exists函數。

 

例如：

1. if exists (select * from tempdb..sysobjects where id = object_id(@sTmpWareA) and type = "U") 

 

判斷方法二：

 

1. if object_id(‘tempdb.dbo.##‘[email protected]_userid) is null 

應用方法一：（感謝阿滿，迷糊）

一個統計的SQL Server暫存資料表格，使用TADOPROC執行預存程序，如下：

 

1. CREATE PROCEDURE InitCreateCoawardTable AS  
2. BEGIN  
3. IF not (object_id(‘tempdb.dbo.#Tmp_Detail‘) is null)  
4. drop table tempdb.dbo.#Tmp_Detail  
5. CREATE TABLE tempdb.dbo.#Tmp_Detail (  
6. [id] [int] IDENTITY (1, 1) NOT NULL , 

 

 

[單據類別] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

 

[單據編號] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

 

（。。這裡多複雜都可以。。。。。省略。。。）

 

 

1. select * from tempdb.dbo.#Tmp_Detail  
2. END  
3. GO 

 

 

在用戶端使用TADOPROC執行這個預存程序，

 

1. with sproc do  
2. begin  
3. try  
4. Close;  
5. ProcedureName:=spName;  
6. Parameters.Refresh;  
7. Prepared;  
8. ExecProc; 

 

 

Open;//這句不能省略要不，就出現忽略了資料庫名稱 ‘，將引用 tempdb 中的對象錯誤。

 

 

1. except  
2. Close;  
3. Exit;  
4. end;  
5. Result:=True;  
6. end; 

 

 

這樣，你的TADOPROC就可以任意使用APPEND,INSERT語句了，更好的是不需要清理，而且方法靈活。更好的有點非常有效處理並發。當然有些人還在使用建立實際表格代替它，使用會話處理該問題，你還得回收。相當麻煩。而且靈活度差多了。

以下是應用範例轉載：

 

利用SQL Server的全域暫存資料表防止使用者重複登入

 

在我們開發商務軟體的時候，常常會遇到這樣的一個問題：怎樣防止使用者重複登入我們的系統？特別是對於銀行或是財務部門，更是要限制使用者以其工號身份多次登入。

可能會有人說在使用者資訊表中加一欄位判斷使用者工號登入的狀態，登入後寫1，退出時寫0，且登入時判斷其標誌位是否為1，如是則不讓該使用者工號登入。但是這樣那勢必會帶來新的問題：如發生象斷電之類不可預知的現象，系統是非正常退出，無法將標誌位置為0，那麼下次以該使用者工號登入則不可登入，這該怎麼辦呢？

或許我們可以換一下思路：有什麼東西是在connection斷開後可以被系統自動回收的呢？對了，SQL Server的暫存資料表具備這個特性！但是我們這裡的這種情況不能用局部暫存資料表，因為局部暫存資料表對於每一個connection來說都是一個獨立的對象，因此只能用全域SQL Server暫存資料表來達到我們的目的。

好了，情況已經明朗話了，我們可以寫一個象下面這樣簡單的預存程序:

 

1. 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:已經登入

 

1. as  
2. declare @v_sql varchar(100)  
3. if object_id(‘tempdb.dbo.##‘[email protected]_userid) is null  
4. begin  
5. set @v_sql = ‘create table ##‘[email protected]_userid+‘(userid varchar(6))‘  
6. exec (@v_sql)  
7. set @i_out = 0 
8. end  
9. else  
10. set @i_out = 1 

 

在這個過程中，我們看到如果以使用者工號命名的全域SQL Server暫存資料表不存在時過程會去建立一張並把out參數置為0，如果已經存在則將out參數置為1。

這樣，我們在我們的應用程式中調用該過程時，如果取得的out參數為1時，我們可以毫不客氣地跳出一個message告訴使用者說”對不起，此工號正被使用！”

判斷方法範例：

 

1. select @sTmpWareA="tempdb..[##MARWareA"+ @ComputerName+"]"  
2. if exists (select * from tempdb..sysobjects where id = object_id(@sTmpWareA) and type = "U")   
3. begin   
4. set @sTmpWareA="[##MARWareA"+ @ComputerName+"]"   
5. exec( "drop table " [email protected] )   
6. end   
7. else   
8. set @sTmpWareA="[##MARWareA"+ @ComputerName+"]"  

 

 

 

@sTmpWareA 就是SQL Server暫存資料表的名稱，過程中使用exec來操作

 

From:http://database.51cto.com/art/201007/213431.htm

SQL Server暫存資料表 實用大全