SQL的遊標使用規則詳解和範例

 

MS-SQL的遊標是一種臨時的資料庫物件，既對可用來旋轉儲存在系統永久表中的資料行的副本，也可以指向儲存在系統永久表中的資料行的指標。遊標為您提供了在逐行的基礎上而不是一次處理整個結果集為基礎的動作表中資料的方法。 1． 如何使用遊標1）    定義遊標語句 Declare <遊標名> Cursor For 2）    建立遊標語句 Open <遊標名>3）    提取遊標列值、移動記錄指標 Fetch <列名列表> From <遊標名> [Into <變數列表>]4）    使用@@Fetch_Status利用While迴圈處理遊標中的行5）    刪除遊標並釋放語句 Close <遊標名>/Deallocate <遊標名>6）    遊標應用執行個體--定義遊標Declare cur_Depart Cursor For Select cDeptID,cDeptName From Department into @DeptID,@DeptName--建立遊標Open cur_Depart--移動或提取列值Fetch From cur_Depart into @DeptID,@DeptName--利用迴圈處理遊標中的列值While @@Fetch_Status=0Begin    Print @DeptID,@DeptName    Fetch From cur_Depart into @DeptID,@DeptNameEnd--關閉/釋放遊標Close cur_DepartDeallocate cur_Depart簡單的過程：定義遊標DECLARE CustomerCursor CURSOR FORSELECT acct_no,name,balanceFROM customerWHERE province="北京";開啟遊標OPEN CustomerCursor;提取資料--設定迴圈lb_continue=Truell_total=0DO WHILE lb_continueFETCH CustomerCursorINTO:ls_acct_no, :ls_name, :ll_balance;If sqlca.sqlcode=0 Thenll_total+=ll_balanceElselb_continue=FalseEnd IfLOOP關閉遊標CLOSE CustomerCursor; 2． 語句的詳細及注意1）  定義遊標語句 Declare < 遊標名> [Insensitive] [Scroll] Cursor                          For <Select 語句> [FOR {Read Only | Update [ OF < 列名列表>]}]  u     Insensitive DBMS建立查詢結果集資料的臨時副本（而不是使用直接引用資料庫表中的真實資料行中的列）。遊標是Read Only，也就是說不能修改其內容或底層表的內容； u     Scroll 指定遊標支援通過使用任意Fetch 選項（First Last Prior Next Relative Absolute）選取它的任意行作為當前行。如果此項省略，則遊標將只支援向下移動單行（即只支援遊標的Fetch Next）； u     Select 語句 定義遊標結果集的標準 SELECT 語句。在遊標聲明的 <Select語句>內不允許使用關鍵字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO； u     Read Only 防止使用遊標的使用者通過更新資料或刪除行改變遊標的內容； u     Update 建立可更新遊標且列出值能被更新的遊標列。如果子句中列入了任意列，則只有被列入的列才能被更新。如果Declare Cursor語句中只指定的UPDATE（沒有列名列表），則遊標將允許更新它的任何或所有列。Declare cur_Depart Cursor   For Select * From Department For Update OF cDeptID,cDeptName 2）  提取遊標列值、移動記錄指標語句 Fetch [Next | Prior | First | Last | {Absolute < 行號>} | {Relative < 行號>}]     From < 遊標名> [Into < 變數列表……>]                         每次執行Fetch語句時，DBMS移到遊標中的下一行並把遊標中的列值擷取到Into中列出的變數中。因此Fetch語句的Into子句中列出的變數必須與遊標定義中Select 語句中的列表的類型與個數相對應； 僅當定義遊標時使用Scroll參數時，才能使用Fetch語句的行定位參數（First、Last、Prior、Next、Relative、Absolute）；如果Fetch語句中不包括參數Next | Prior | First | Last，DBMS將執行預設的Fetch Next； u     Next 向下、向後移動一行（記錄）； u     Prior 向上、向前移動一行（記錄）； u     First 移動至結果集的第一行（記錄）； u     Last 移動至結果集的最後一行（記錄）； u     Absolute n 移動到結果集中的第n行。如果n是正值，DBMS從結果集的首部向後或向下移動至第n行；如果n是負數，則DBMS從結果集的底部向前或向上移動n行；         Fetch Absolute 2 From cur_Depart Into @DeptID,@DeptName u     Relative n   從指標的當前位置移動n行。如果n是正值，DBMS將行指標向後或向下移動至第n行；如果n是負數，則DBMS將行指標向前或向上移動n行；          Fetch Relative 2 From cur_Depart Into @DeptID,@DeptName 3）  基於遊標的定位DELETE/UPDATE 語句如果遊標是可更新的（也就是說，在定義遊標語句中不包括Read Only參數）,就可以用遊標從遊標資料的源表中DELETE/UPDATE行，即DELETE/UPDATE基於遊標指標的當前位置的操作；舉例：--刪除當前行的記錄Declare cur_Depart Cursor    For Select cDeptID,cDeptName From Department into @DeptID,@DeptNameOpen cur_DepartFetch From cur_Depart into @DeptID,@DeptNameDelete From Department Where CURRENT OF cur_Depart--更新當前行的內容Declare cur_Depart Cursor   For Select cDeptID,cDeptName From Department into @DeptID,@DeptNameOpen cur_DepartFetch From cur_Depart into @DeptID,@DeptName   Update Department Set cDeptID=’2007’ + @DeptID Where CURRENT OF cur_Depart 3． 遊標提示及注意1） 利用Order By改變遊標中行的順序。此處應該注意的是，只有在查詢的中Select 子句中出現的列才能作為Order by子句列，這一點與普通的Select語句不同；2） 當語句中使用了Order By子句後，將不能用遊標來執行定位DELETE/UPDATE語句；如何解決這個問題，首先在原表上建立索引，在建立遊標時指定使用此索引來實現；例如：Declare cur_Depart CursorFor Select cDeptID,cDeptName From Department With INDEX(idx_ID)For Update Of cDeptID,cDeptName 通過在From子句中增加With Index來實現利用索引對錶的排序；3） 在遊標中可以包含計算好的值作為列；4） 利用@@Cursor_Rows確定遊標中的行數 4． 使用系統過程管理遊標在建立一個遊標之後，便可利用系統過程對遊標進行管理管理，遊標的系統過程主要有以下幾個：sp_cursor_list、sp_describe_cursor、 sp_describe_cursor_tables 、sp_describe_cursor_columns。1）  sp_cursor_list   顯示在當前範圍內的遊標及其屬性。其命令格式為： sp_cursor_list [ @cursor_return = ] cursor_variable_name OUTPUT， [ @cursor_scope = ] cursor_scope參數： ·         [ @cursor_return =] cursor_variable_name OUTPUT：聲明的遊標變數的名稱。 cursor_variable_name 的資料類型為 cursor，沒有預設值。遊標是可滾動的、動態唯讀遊標。·         [ @cursor_scope =] cursor_scope：指定要報告的遊標層級。 cursor_scope 的資料類型為 int，沒有預設值，可以是下列值中的一個。

值	描述
1	報告所有本地遊標。
2	報告所有全域遊標。
3	報告本地遊標和全域遊標。

提示：由於sp_cursor_list是一個含有遊標類型變數@cursor_return，且有OUTPUT保留字的系統過程，遊標變數@cursor_return中的結果集與pub_cur遊標中的結果集是不同的。2）  sp_describe_cursor 報表服務器遊標的特性。 sp_describe_cursor [ @cursor_return = ] output_cursor_variable OUTPUT
    { [ , [ @cursor_source = ] N'local'
        , [ @cursor_identity = ] N' local_cursor_name ' ]
            | [ , [ @cursor_source = ] N'global'
        , [ @cursor_identity = ] N' global_cursor_name ' ]
            | [ , [ @cursor_source = ] N'variable'
        , [ @cursor_identity = ] N' input_cursor_variable ' ]
    } 參數：·         [ @cursor_return =] output_cursor_variable OUTPUT：聲明遊標變數的名稱，該變數接收遊標輸出。 output_cursor_variable 的資料類型為 cursor，沒有預設值。調用 sp_describe_cursor 時，不能與任何遊標相關聯。返回的遊標是可滾動的動態唯讀遊標。·         [ @cursor_source =] { N'local' | N'global' | N'variable' }：指定是使用本地遊標的名稱、全域遊標的名稱、還是遊標變數的名稱來指定當前正在對其進行報告的遊標。參數是 nvarchar(30)。·         [ @cursor_identity =] N' local_cursor_name ']：由具有 LOCAL 關鍵字或預設設定為 LOCAL 的 DECLARE CURSOR 語句建立的遊標的名稱。 local_cursor_name 的資料類型為 nvarchar(128)。·         [ @cursor_identity =] N' global_cursor_name ']：由具有 GLOBAL 關鍵字或預設設定為 GLOBAL 的 DECLARE CURSOR 語句建立的遊標的名稱。也可以是由 ODBC 應用程式開啟然後通過調用 SQLSetCursorName 對遊標命名的 API 伺服器資料指標的名稱。 global_cursor_name 的資料類型為 nvarchar(128)。 ·         [ @cursor_identity =] N' input_cursor_variable ']：與開放遊標相關聯的遊標變數的名稱。 input_cursor_variable 的資料類型為 nvarchar(128)。 提示： sp_descride_cursor_tables和sp_describe_cursor_columms的命令格式與sp_describe_cursor的命令格式一樣。 5． 遊標種類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 infoselect * from info--清除所有記錄truncate table infodeclare @i intset @i=1while @i<1000000begininsert into info values('Justin'+str(@i),'深圳'+str(@i))set @i=@i+1end 6． 遊標和遊標的優點在資料庫中，遊標是一個十分重要的概念。遊標提供了一種對從表中檢索出的資料進行操作的靈活手段，就本質而言，遊標實際上是一種能從包括多條資料記錄的結果集中每次提取一條記錄的機制。遊標總是與一條T_SQL 選擇語句相關聯因為遊標由結果集（可以是零條、一條或由相關的選擇語句檢索出的多條記錄）和結果集中指向特定記錄的遊標位置群組成。當決定對結果集進行處理時，必須聲明一個指向該結果集的遊標。如果曾經用 C 語言寫過對檔案進行處理的程式，那麼遊標就像您開啟檔案所得到的檔案控制代碼一樣，只要檔案開啟成功，該檔案控制代碼就可代表該檔案。對於遊標而言，其道理是相同的。可見遊標能夠實現按與傳統程式讀取一般檔案類似的方式處理來自基礎資料表的結果集，從而把表中資料以一般檔案的形式呈現給程式。我們知道關聯式資料庫管理系統實質是面向集合的，在MS SQL中並沒有一種描述表中單一記錄的表達形式，除非使用where 子句來限制只有一條記錄被選中。因此我們必須藉助於遊標來進行面向單條記錄的資料處理。 SERVER 由此可見，遊標允許應用程式對查詢語句select 返回的行結果集中每一行進行相同或不同的操作，而不是一次對整個結果集進行同一種操作；它還提供對基於遊標位置而對錶中資料進行刪除或更新的能力；而且，正是遊標把作為面向集合的資料庫管理系統和面向行的程式設計兩者聯絡起來，使兩個資料處理方式能夠進行溝通。