SQL Story（十）————遊標的應該與不應該 (轉)

SQL Story（十）————遊標的應該與不應該 (轉)[@more@] 

遊標概觀

相信很多員都寫過這樣的程式碼：

...

begin

MyDataSet.Open;

MyDataSet.Frist;

while not ( MyDataSet.BOF or MyDataSet.EOF) do

begin

...

end;

MyDataSet.Close;

end;

...

很久以來，我們習慣了用這樣的程式碼對返回的資料進行逐行操作。在用客戶端程式的程式碼開啟資料集之前，我們把它當做是一個無序集合。不過，在需要時，我們在端就可以直接以行操作形式處理資料集，這就是遊標。

遊標的的使用方法類似前面的Delphi程式碼，通常有如下四步：

一、宣告遊標：Declare Cursor，這一過程在前面的程式碼中沒有體現出來。然而我們都知道，在使用一個資料集之前，我們總要定義它的種種屬性，比如資料來源、語句、開啟方式等等。在遊標中，資料來源一般是不用指定了（因為就在當前資料庫中，MS 中也可以透過SQL語言來讀取異構資料來源）。不過我們要為它指定一個資料集，還可以為它指定各種開啟方式的設定，比如是否允許寫操作，是否可以隨機讀取等等。一般來說，資料庫預設的遊標是隻讀、單向、逐行讀取的。

二、開啟遊標：Open Cursor，一個Delphi（或其它開發平臺）的資料集，在指定了各種必要屬性後，還一定要用一個開啟指令（如前面的MyDataSet.Open）來開啟它，才能得到我們所需要的資料集，對於遊標，我們也一樣需要一個Open指令來開啟它，才能使用。

三、運算元據：這一步驟通常包括移動當前遊標、讀取當前資料、操作程式碼三部分。前面設定的遊標屬性，很大一部分是關於這裡面所能進行什麼樣操作的。比如，對於雙向遊標，我們可以向前或向後一行，而隨機遊標，我們甚至可以以隨機方式指定遊標的操作位置，但最常見的遊標，是單向、只讀的那一種。而對於當前行資料，我們能常是透過定義一些變數來讀取，或移動到適當位置再進行寫操作，這一點和一般的開發工具是一樣的。至於操作程式碼，這雖然不是SQL的強項，但一般的資料庫系統也提供基本的過程化編碼能力，可以讓我們完成操作。

四、關閉結果集：Close Cursor 做事要有始有終，在Delphi中，如果忘了關閉自己開啟的資料集，會帶來很多麻煩，而在資料庫系統中，如果開啟一個遊標沒有關上（想想吧，遊標操作是要對資料上鎖的），如果有很多都在這段有問題的程式碼……所以，在遊標的標準語法中，有明確的語句，用來關閉資料集，並釋放所佔用的各種資源。這一點更像是OO語言中的析構，比Delphi的資料集元件的Close方法要做的事情要多一些。

以上各個步驟，SQL標準都約定了相應的實現程式碼。但具體到各個MS平臺的實現，卻是大同小異。問題就在於這一點不同之處足以把人煩死。所以，我在這裡不具體寫出實現方法。讀者完全可以查閱自己使用的系統所帶的幫助，看看自己用的資料庫是如何實現的遊標，實現了哪些功能。

不合理的存在

我們可以看到，遊標與SQL語言的其它部分有相當大的差別。它的實現和操作很複雜，而且由於要逐行操作，完成同樣功能的情況下，它通常比集合操作要慢。差距會有多大呢？舉一個極端的例子：我曾經試驗用遊標給一個表填充行號，結果執行了十二個小時都沒有完，而同樣的操作，用前面文章提到過的不等聯接，只要不到三秒鐘。我保證不是所有情況下差距都會有這麼大，但這種情況的確存在。特別是當大量併發任務存在時，這種長時間的鎖定是很危險的。複雜和低效，是遊標的最大缺點，僅僅為這兩點，就足以讓我們對它抱有一種謹慎的態度。而且，一般來說，需要用遊標進行的操作，都可以在客戶端完成（能過所謂的宿主語言，Host Language）。

也許，在極端的關係模型擁護者看來，遊標是一個醜惡的存在。在一個完美、優雅，以無序的集合來管理資訊的體系中，我們為什麼要安置一個以有序方式逐行操作資訊的遊標呢？然而，正如《龍槍編年史》中，伊斯塔城神聖的帕拉丁神殿，卻遊蕩著有史以來最黑暗的黑衣法師費斯坦但提勒斯，在龐大嚴謹的關聯式資料庫中，有遊標這樣的另類存在。這是創造關係世界的眾神之旨意，自有其道理。

存在即合理

遊標雖有如此的缺憾，但它也有存在的價值。首先，當需要有序操作的資料集很大時，特別是最終的運算結果相對很小時，如果還要發到前臺做，對資源的浪費就太大了。而且，一個很大的資料集傳過來，宿主語言也不一定能支援這麼龐大的資料結構（比如Delphi的VCL容器在這方面就倍受指責），這一點也限制了我們用宿主語言來擴充系統功能 （比如MS SQL Server和Interbase本來可以寫擴充套件函式和擴充套件過程）。另外，如果要大量的逐行的寫操作，與前臺互動通常更低。遊標的確不是好方法，但沒有更好的方法時，它就是最好的方法。再就是以我的，以指令碼寫就的遊標要比宿主語言編譯後的二制程式碼的可維護性和可性要強。

俗世之中，是沒有絕對的黑暗和光明的。關鍵，在於我們是否正確的利用它。

正確使用遊標

遊標本身沒有所謂的對錯，但在使用它時，我們應先三思而後行。

很多時候，遊標未必是你想像中的唯一方法。我見過太多的遊標指令碼，本來都可以用更簡潔高效的結構化操作完成。只要簡單語句可以達到同樣的效果，就不要用遊標。《程式設計師》上讀到過一句話：Simple is smart。這是開發的真理。

遊標中，顯然只讀、單向的遊標速度最快，而且也不容易造成死鎖，儘可能用它吧。

在遊標使用的表上，建立適當的，這麼做帶來的效率提高會比一般的SQL語句更明顯，尤其是執行寫操作的遊標。

遊標操作的結果集，要儘可能的小。

如果遊標程式碼中有大量的運算，那麼考慮是不是把它分散開，放到其它伺服器或客戶端。

對遊標程式碼要進行充分的測試和驗證，再投入使用，尤其是程式和穩定性。這方面不能相信系統。比如你寫一個遊標，每讀一行把一個變數累加一，系統永遠也不會主動把它最佳化成count(*)。

有些系統可以把當前事務開啟的遊標保持到以後的事務中，直到顯示地關閉它。不過最好不要隨便使用它。這個功能當然看起來很酷，不過濫用它會給我們帶來無窮無盡的麻煩。你真的需要這種功能嗎？

適當的時候，把它寫成擴充套件儲存過程或擴充套件儲存函式，以二進位制程式碼的形式連結進資料庫系統。這樣做的缺點是失去了靈活性，換來的是效率的提升。

附：

以前一直不會在Interbase的ISQL中輸入多條成批執行的語句，所以什麼儲存過程、、甚至於遊標，都建不起來。直到有一天，注意了一下，發現在ISQL中是這樣做的：

set term^;

...

^

...

^

...

set term;^

從第一行set term^;開始，ISQL會把用^分隔開的語句成組地發至後臺執行，直至set term;^為止。有點像MS SQL Server的查詢分析器的“GO”。這樣，我們就可以自如地用ISQL編寫指令碼了。