SQL遊标原理和使用方法

版權聲明：本文為部落客原創文章，未經部落客允許不得轉載。 https://blog.csdn.net/chinahuyong/article/details/3552248

 SQL遊标原理和使用方法

    在資料庫開發過程中，當你檢索的資料隻是一條記錄時，你所編寫的事務語句代碼往往使用SELECT INSERT 語句。但是我們常常會遇到這樣情況，即從某一結果集中逐一地讀取一條記錄。那麼如何解決這種問題呢？遊标為我們提供了一種極為優秀的解決方案。

1.1 遊标和遊标的優點

    在資料庫中，遊标是一個十分重要的概念。遊标提供了一種對從表中檢索出的資料進行操作的靈活手段，就本質而言，遊标實際上是一種能從包括多條資料記錄的結果集中每次提取一條記錄的機制。遊标總是與一條T_SQL 選擇語句相關聯因為遊标由結果集（可以是零條、一條或由相關的選擇語句檢索出的多條記錄）和結果集中指向特定記錄的遊标位置組成。當決定對結果集進行處理時，必須聲明一個指向該結果集的遊标。如果曾經用 C 語言寫過對檔案進行處理的程式，那麼遊标就像您打開檔案所得到的檔案句柄一樣，隻要檔案打開成功，該檔案句柄就可代表該檔案。對于遊标而言，其道理是相同的。可見遊标能夠實作按與傳統程式讀取平面檔案類似的方式處理來自基礎表的結果集，進而把表中資料以平面檔案的形式呈現給程式。

    我們知道關系資料庫管理系統實質是面向集合的，在MS SQL SERVER 中并沒有一種描述表中單一記錄的表達形式，除非使用where 子句來限制隻有一條記錄被選中。是以我們必須借助于遊标來進行嫦虻ヌ跫鍬嫉氖荽怼?BR>    由此可見，遊标允許應用程式對查詢語句select 傳回的行結果集中每一行進行相同或不同的操作，而不是一次對整個結果集進行同一種操作；它還提供對基于遊标位置而對表中資料進行删除或更新的能力；而且，正是遊标把作為面向集合的資料庫管理系統和面向行的程式設計兩者聯系起來，使兩個資料處理方式能夠進行溝通。

1.2 遊标種類

MS SQL SERVER 支援三種類型的遊标：Transact_SQL 遊标，API 伺服器遊标和客戶遊标。

（1） Transact_SQL 遊标

    Transact_SQL 遊标是由DECLARE CURSOR 文法定義、主要用在Transact_SQL 腳本、存儲過程和觸發器中。Transact_SQL 遊标主要用在伺服器上，由從用戶端發送給伺服器的Transact_SQL 語句或是批處理、存儲過程、觸發器中的Transact_SQL 進行管理。 Transact_SQL 遊标不支援提取資料塊或多行資料。

（2） API 遊标

    API 遊标支援在OLE DB， ODBC 以及DB_library 中使用遊标函數，主要用在伺服器上。每一次用戶端應用程式調用API 遊标函數，MS SQL SEVER 的OLE DB 提供者、ODBC驅動器或DB_library 的動态連結庫（DLL）都會将這些客戶請求傳送給伺服器以對API遊标進行處理。

（3） 客戶遊标

    客戶遊标主要是當在客戶機上緩存結果集時才使用。在客戶遊标中，有一個預設的結果集被用來在客戶機上緩存整個結果集。客戶遊标僅支援靜态遊标而非動态遊标。由于伺服器遊标并不支援所有的Transact-SQL 語句或批處理，是以客戶遊标常常僅被用作伺服器遊标的輔助。因為在一般情況下，伺服器遊标能支援絕大多數的遊标操作。

    由于API 遊标和Transact-SQL 遊标使用在伺服器端，是以被稱為伺服器遊标，也被稱為背景遊标，而用戶端遊标被稱為前台遊标。在本章中我們主要講述伺服器（背景）遊标。

select count(id) from info

select * from info

--清除所有記錄

truncate table info

declare @i int

set @i=1

while @i<1000000

begin

 insert into info values('Justin'+str(@i),'深圳'+str(@i))

 set @i=@i+1

end

1.3 遊标操作

    使用遊标有四種基本的步驟:聲明遊标、打開遊标、提取資料、關閉遊标。

聲明遊标

    象使用其它類型的變量一樣,使用一個遊标之前,首先應當聲明它。遊标的聲明包括兩個部分:遊标的名稱;這個遊标所用到的SQL語句。如要聲明一個叫作Cus-tomerCursor的遊标用以查詢位址在北京的客戶的姓名、帳号及其餘額,您可以編寫如下代碼:

DECLARE CustomerCursor CURSOR FOR

SELECT acct_no,name,balance

FROM customer

WHERE province="北京";

    在遊标的聲明中有一點值得注意的是,如同其它變量的聲明一樣,聲明遊标的這一段代碼行是不執行的,您不能将debug時的斷點設在這一代碼行上,也不能用IF...END IF語句來聲明兩個同名的遊标,如下列的代碼就是錯誤的。

IF Is_prov="北京"THEN

ELSE

WHERE province〈〉"北京";

END IF

打開遊标

聲明了遊标後在作其它操作之前,必須打開它。打開遊标是執行與其相關的一段SQL語句,例如打開上例聲明的一個遊标,我們隻需鍵入:

OPEN CustomerCursor;

    由于打開遊标是對資料庫進行一些SQL SELECT的操作,它将耗費一段時間,主要取決于您使用的系統性能和這條語句的複雜程度。如果執行的時間較長,可以考慮将螢幕上顯示的滑鼠改為hourglass。

提取資料

    當用OPEN語句打開了遊标并在資料庫中執行了查詢後,您不能立即利用在查詢結果集中的資料。您必須用FETCH語句來取得資料。一條FETCH語句一次可以将一條記錄放入程式員指定的變量中。事實上,FETCH語句是遊标使用的核心。在DataWindow和DataStore中,執行了Retrieve ()函數以後,查詢的所有結果全部可以得到;而使用遊标,我們隻能逐條記錄地得到查詢結果。

已經聲明并打開一個遊标後,我們就可以将資料放入任意的變量中。在FETCH語句中您可以指定遊标的名稱和目标變量的名稱。如下例:

FETCH CustmerCur-sor

INTO:ls_acct_no,

:ls_name,

:ll_balance;

    從文法上講,上面所述的就是一條合法的取資料的語句,但是一般我們使用遊标卻還應當包括其它的部分。正如我們前面所談到的,遊标隻能一次從背景資料庫中取一條記錄,而在多數情況下,我們所想要作的是在資料庫中從第一條記錄開始提取,一直到結束。是以我們一般要将遊标提取資料的語句放在一個循環體内,直至将結果集中的全部資料提取後,跳出循環圈。通過檢測SQLCA.SQL-CODE的值,可以得知最後一條FETCH語句是否成功。一般,當SQLCODE值為 0時表明一切正常,100表示已經取到了結果集的末尾,而其它值均表明操作出了問題,這樣我們可以編寫以下的代碼:

lb_continue=True

ll_total=0

DO WHILE lb_continue

FETCH CustomerCur-sor

If sqlca.sqlcode=0 Then

ll_total+=ll_balance

Else

lb_continue=False

End If

LOOP

    循環體的結構有多種,這裡提到的是最常見的一種。也有的程式員喜愛将一條FETCH語句放在循環體的前面,循環體内再放置另外一條FETCH語句,并檢測SQLCA.SQLCODE是否為100。但是這樣做,維護時需同時修改兩條FETCH語句,稍麻煩了些。

關閉遊标

    在遊标操作的最後請不要忘記關閉遊标,這是一個好的程式設計習慣,以使系統釋放遊标占用的資源。關閉遊标的語句很簡單:

CLOSE CustomerCursor;

使用Where子句子

    我們可以動态地定義遊标中的Where子句的參數,例如在本例中我們是直接定義了查詢省份是北京的記錄,但也許在應用中我們要使用一個下拉式清單框,由使用者來選擇要查詢的省份,我們該怎樣做呢?

    我們在前面曾經提到過,DECLARE語句的作用隻是定義一個遊标,在OPEN語句中這個遊标才會真正地被執行。了解了這些,我們就可以很友善地實作這樣的功能,在DECLARE的Where子句中加入變量作參數,如下所示:

SELCECT acct_no,name,balance

WHERE province=:ls_province;

∥定義ls_province的值

遊标的類型

    同其它變量一樣,我們也可以定義遊标的通路類型:全局、共享、執行個體或局部,遊标變量的命名規範建議也同其它變量一樣。

--聲明遊标

declare my_cursor cursor keyset for select * from info

--删除遊标資源

deallocate my_cursor

--打開遊标,在遊标關閉或删除前都有效

open my_cursor

--關閉遊标

close my_cursor

--聲明局部變量

declare @id int,@name varchar(20),@address varchar(20)

--定位到指定位置的記錄

fetch absolute 56488 from my_cursor into @id,@name,@address

select @id as id,@name as name,@address as address

--定位到目前記錄相對位置記錄

fetch relative -88 from my_cursor into @id,@name,@address

--定位到目前記錄前一條

fetch prior from my_cursor into @id,@name,@address

--定位到目前記錄後一條

fetch next from my_cursor into @id,@name,@address

--定位到首記錄

fetch first from my_cursor into @id,@name,@address

--定位到尾記錄

fetch last from my_cursor into @id,@name,@address

執行個體：

 use database1

declare my_cursor cursor scroll dynamic

 /**//*scroll表示可随意移動遊标指        針（否則隻能向前），dynamic表示可以讀寫遊标（否則遊标隻讀）*/

for

select productname from  product

declare @pname sysname

fetch next from my_cursor into @pname

while(@@fetch_status=0)

  begin

    print 'Product Name: ' + @pname

    fetch next from my_cursor into @pname

  end

fetch first from my_cursor into @pname

print @pname

/**//*update product set productname='zzg' where current of my_cursor */

/**//*delete from product where current of my_cursor */

1.4 遊标的進階技巧

    盡管目前基于SQL語句的背景資料庫所支援的語言都大緻相當,但對遊标的支援卻有着一些差異,例如對滾動遊标支援。所謂滾動遊标,就是程式員可以指定遊标向前後任意一個方向滾動。如在Informix中,您甚至還可以将遊标滾向結果集開頭或末尾,使用的語句分别是FETCH FIRST,FETCH LAST、FETCH PRIOR和FETCH NEXT。當程式員用FETCH語句,其預設是指FETCH NEXT。由于滾動是在資料庫背景實作的,是以滾動遊标為使用者程式設計提供了極大的友善。

    對遊标支援的另一個不同是可修改遊标。上述遊标的使用都是指隻讀遊标,而象Oracle、Sybase等資料庫卻另外支援可作修改的遊标。使用這樣的資料庫,您可以修改或删除目前遊标所在的行。例如修改目前遊标所在行的使用者的餘額,我們可以如下操作:

UPDATE customer

SET balance=1000

WHERE CURRENT of customerCursor;

删除目前行的操作如下:

DELETE FROM Customer

WHERE CURRENT OF CustomerCursor;

    但是如果您目前使用的資料庫是Sybase,您需要修改資料庫的參數,将遊标可修改的值定為1,才能執行上述操作。這一指派在連接配接資料庫的前後進行均可。

SQLCA.DBParm="Cursor Update=1"

    另外一個内容是動态遊标,也就是說您可以運作過程中動态地形成遊标的SELECT語句。這同在PowerBuilder中動态地使用嵌入式SQL一樣,需要用到DynamicStagin-gArea等資料類型,這已超出了本節的範圍。