SQL游標的原理和使用方法

這篇文章主要介紹“SQL游標的原理和使用方法”，在日常操作中，相信很多人在SQL游標的原理和使用方法問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”SQL游標的原理和使用方法”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

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

    我們知道關系數據庫管理系統實質是面向集合的，在MS SQL SERVER 中并沒有一種描述表中單一記錄的表達形式，除非使用where 子句來限制只有一條記錄被選中。因此我們必須借助于游標來進行面向單條記錄的數據處理。
    由此可見，游標允許應用程序對查詢語句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
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,name,balance
FROM customer
WHERE province="北京";
ELSE
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,name,balance
FROM customer
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
INTO:ls_acct_no,
:ls_name,
:ll_balance;
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子句中加入變量作參數,如下所示:
DECLARE CustomerCursor CURSOR FOR
SELCECT acct_no,name,balance
FROM customer
WHERE province=:ls_province;
∥定義ls_province的值
OPEN CustomerCursor;
游標的類型
同其它變量一樣,我們也可以定義游標的訪問類型:全局、共享、實例或局部,游標變量的命名規范建議也同其它變量一樣。
--聲明游標
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
select @id as id,@name as name,@address as address
--定位到當前記錄前一條
fetch prior from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到當前記錄后一條
fetch next from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到首記錄
fetch first from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到尾記錄
fetch last from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address

實例：
 use database1
declare my_cursor cursor scroll dynamic
 /**//*scroll表示可隨意移動游標指        針（否則只能向前），dynamic表示可以讀寫游標（否則游標只讀）*/
for
select productname from  product

open my_cursor
declare @pname sysname
fetch next

到此，關于“SQL游標的原理和使用方法”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！