Sql server中的Cursor

有時候我們需要逐條地讀取資料，就像下面這樣：

this.sqlConnection1.Open();
SqlDataReader myReader = this.sqlCommand1.ExecuteReader();
do
{
while (myReader.Read())
      {
Console.WriteLine("{0}{1}", myReader.GetInt32(0), myReader.GetString(1));
} while (myReader.NextResult());
myReader.Close();
      sqlConnection1.Close();    

我們經常會這樣在自己的程式中這樣去寫，但是怎麼在SQL Server 中逐條訪問資料，當然是有辦法的，那就是遊標。
比方說：
我想從Nurthwind資料庫的Employees中尋找TitleOfCourtesy為‘Mr.'的所有人的LastName和FirstName，同時我需要逐條訪問，下面是樣本：

USE Northwind
GO
--定義遊標
DECLARE myCursor CURSOR FOR
SELECT LastName, FirstName FROM Employees
WHERE TitleOfCourtesy ='Mr.'
--開啟遊標
OPEN myCursor
--逐行讀取
FETCH NEXT FROM myCursor
--@@FETCH_STATUS，FETCH 語句成功
WHILE @@FETCH_STATUS = 0
BEGIN  
   FETCH NEXT FROM myCursor
END
--關閉遊標
CLOSE myCursor
--釋放遊標
DEALLOCATE myCursor
GO

就這樣逐條的結果讀出來了

但 是遊標佔用記憶體，還用它們那些不可思議的方式鎖定表。每執行一次FETCH就等於執行一次SELECT命令？這意味著如果你的遊標有10000條記錄，它 將執行10000次SELECT！如果你使用一組SELECT、UPDATE或者DELETE來完成相應的工作，那將有效率的多。
關於遊標與資料庫效能：
Cursor threshold選項
用於指定遊標集的行數。
如果設定為-1，則所有的遊標集將同步產生，對於小遊標集會非常有用
如果設定為0，則所有的遊標集將非同步產生，這對於數量很大的遊標集會有協助
如果設定為其它值，非同步和同步將由遊標集中所期望的行數和遊標閾值的設定值比較決定。前者大於後者，非同步；否則，同步。
可以使用sp_configure系統預存程序來修改該設定。當注意的是只有show advanced options為1時才可更改。同時該設定修改後立即生效，無須重啟資料庫。

 


2.2 隱式遊標

       所有的隱式遊標都被假設為只返回一條記錄。

       使用隱式遊標時，使用者無需進行聲明、開啟及關閉。PL/SQL隱含地開啟、處理，然後關掉遊標。

       例如：

       …….

       SELECT studentNo,studentName

       INTO curStudentNo,curStudentName

       FROM StudentRecord

       WHERE name=’gg’;

       上述遊標自動開啟，並把相關值賦給對應變數，然後關閉。執行完後，PL/SQL變數curStudentNo,curStudentName中已經有了值。

遊標四大隱式屬性

%found、%notfound、%isopen、%rowcount

其中%found、%notfound、%isopen的結果為boolean型，%rowcount結果為整數。

舉例說明：
test表資料
 A NAME
-- -----
 1 aa
 2 bb
 3 cc
 4 dd
 5 ee

程式：
set serveroutput on
declare
cursor abc(idpara number) is   --帶參數
select name from test where a=idpara;
t_name test.name%type;
e exception;     --自訂異常
nopen exception;    --自訂異常
begin
open abc('3');     --參數idpara賦值為3
fetch abc into t_name;
if abc%isopen then    --如果遊標已開啟
dbms_output.put_line('cursor is open');
else
raise nopen;     --拋出自訂異常(遊標未開啟)
end if;
if abc%notfound then    --如果資料沒有找到
raise e;     --拋出自訂異常(資料沒有找到)
elsif abc%found then    --如果資料找到
dbms_output.put_line(t_name);
dbms_output.put_line('I found '||abc%rowcount||' data!'); --輸出找到多少個資料
end if;
exception    
when nopen then       --遊標未開啟異常執行
dbms_output.put_line('The cursor is not found!');  
when e then       --資料未找到異常執行
dbms_output.put_line('The eid is not found!');
end;
/

完成。