資料指標(CURSOR)

/*** 資料指標(CURSOR) ***/


/* 1. 基本用法 */
/***********************************************************/
DECLARE cur1 CURSOR FOR SELECT PRODUCTID,PRODUCTNAME 
  FROM PRODUCTS --(1.宣告Cursor)

DECLARE @ProdId int, @ProdName VARCHAR(40),
        @Count NUMERIC(10,0)

OPEN cur1      --(2.開啟Cursor)
SET @Count = 0
FETCH NEXT FROM cur1 
    INTO @ProdId,@ProdName  --(3.抓取Cursor中之記錄資料)
WHILE @@FETCH_STATUS=0   /*0: Fetch成功 -1: Fetch失敗 
                          -2: 要Fetch之記錄已不存在(一般發生
                              在KEYSET之CURSOR) */ 
BEGIN
  PRINT CONVERT(CHAR(5),@ProdId)+' '+@ProdName
  SET @Count = @Count + 1
  FETCH NEXT FROM cur1 INTO @ProdId,@ProdName
END
PRINT ''
PRINT '共 '+ CONVERT(VARCHAR(10),@Count)+' 筆'

CLOSE cur1                  --(4.關閉Cursor)
DEALLOCATE cur1             --(5.移除Cursor)


/* 2. CURSOR 深入探討 */
/***********************************************************/

/* 2.1 Cursor宣告 */
/***********************************************************/
  /* 2.1.1 用SQL-92標準語法宣告 */
    /*===========================================*/
  DECLARE cur_name [INSENSITIVE] [SCROLL] CURSOR
  FOR select_statement
  [FOR {READ ONLY| UPDATE [OF col_name [,...n] ] } ]

  (註1) INSENSITIVE: 1.SQL Server會在Tempdb資料庫,
                     建立暫存資料表存放Cursor內容
                     (當其他使用者在Cursor開啟之後
                      才異動之資料,並不會反映於Cursor內容上);
                     2.此類Cursor不可用來更改或刪除資料
                     3.可加快資料讀取速度 
  (註2) SCROLL: 唯有此設定之Cursor,才可跳躍式地被讀取資料
                例如Fetch Fisrt/Last/Prior/Relative/Absolute
                (預設只能由前往後循序被讀取)
  (註3) FOR UPDATE OF ... : 指定資料表可異動之欄位


  /* 2.1.2 用T-SQL擴充語法宣告 */
    /*===========================================*/
  DECLARE cur_name CURSOR
  [LOCAL|GLOBAL]      /* Cursor 有效範圍(cursor_scope) */
                      --LOCAL: 當相關批次結束時,該Cursor自動被
                               SQL Server移除(Close and Deallocate)
                      --GLOBAL(預設):該Cursor可於整個登入連線過程
                                     中被使用
  [FORWARD_ONLY|SCROLL] /* 資料可否跳躍式讀取(scrollable) */
                        --FORWARD_ONLY: 相當於SQL-92未設定SCROLL者
                        --SCROLL: 相當於SQL-92之SCROLL

  [STATIC|KEYSET|DYNAMIC] /* Cursor內容是否會自動更新(model) */
                          --STATIC: 相當於SQL-92之INSENSITIVE者
                          --KEYSET: 1.只有鍵值欄位之資料存入暫存資料表,
                                      其他欄位內容可動態更新反映;
                                    2.當來源資料表之鍵值欄位被異動時,
                                      讀取Cursor中之對應記錄會產生錯誤
                                      (@@FETCH_STATUS => -2)
                          --DYNAMIC: 相當於SQL-92未設訂INSENSITIVE者
                     
  [READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]
                          /* Cursor可否被異動(concurrency) */
                          --READ_ONLY: 相當於SQL-92之 FOR READ ONLY
                          --SCROLL_LOCKS: 經由Cursor讀取到的記錄皆被
                                          SQL Server自動鎖住(Lock);
                                          以確定資料修改或刪除不會被他人
                                          妨礙
                          --OPTIMISTIC:  1.經由Cursor讀取到的記錄不會被
                                           鎖住,但允許由此Cursor去更改
                                           資料;
                                         2.原始資料中之記錄若已被異動,再
                                           由Cursor去異動該筆記錄時會導致
                                           失敗
  
  FOR select_statement
  [FOR UPDATE [OF col_name [,...n] ] ]  --相當於SQL-92之 FOR UPDATE 


  ◎最簡單範例:
  DECLARE cur1 CURSOR FOR SELECT * FROM PRODUCTID 

  --> 預設設定:GLOBAL + FORWARD_ONLY + DYNAMIC + OPTIMISTIC
                          
                             

/* 2.2 Cursor開啟(Open) */
/***********************************************************/
  ◎建立Cursor與來源資料表之連結

  (註1)開啟INSENSITIVE 或 STATIC CURSOR: 
       SQL Server會在Tempdb資料庫,建立暫存資料表
       存放Cursor內容(當其他使用者在Cursor開啟之後
       才異動之資料,並不會反映於Cursor內容上)
  (註2)開啟KEYSET CURSOR:
       SQL Server只會將鍵值欄位資料存入暫存資料表



/* 2.3 Cursor關閉(Close) */
/***********************************************************/
  ◎解除Cursor與來源資料表之連結 



/* 2.4 Cursor移除(Deallocate) */
/***********************************************************/
  ◎從記憶體中移除Cursor定義 



/* 2.5. FETCH用法深入探討 */
/***********************************************************/
FETCH [NEXT             --(1) 預設 
                              (註: Cursor被Open時,
                                   記錄位置是在第一筆之前)
       PRIOR  
       FIRST
       LAST
       ABSOLUTE n
       RELATIVE n ]
[FROM] cur1 
[INTO @Var1,@Var2,... ] --(2) 省略INTO項,則抓取之資料
                              將傳回給前端應用程式

  --2.5.1 巢狀Cursor應用
  /*===========================================*/
DECLARE @cust_id VARCHAR(5), @ord_id INT, @ord_date DATETIME

/*** 外層 ***/
DECLARE cust_cur INSENSITIVE CURSOR FOR 
  SELECT CUSTOMERID   FROM CUSTOMERS 
  ORDER BY CUSTOMERID
OPEN cust_cur
FETCH cust_cur INTO @cust_id

WHILE @@FETCH_STATUS=0
BEGIN
  PRINT '客戶' + @cust_id + ' 訂單:'

  /*** 內層 ***/
  DECLARE ord_cur CURSOR FOR 
    SELECT ORDERID,ORDERDATE 
    FROM ORDERS 
    WHERE CUSTOMERID=@cust_id 
      -- @cust_id變數在左式之Cursor宣告時會以實際值取代
  OPEN ord_cur
  FETCH ord_cur INTO @ord_id,@ord_date
  WHILE @@FETCH_STATUS=0
  BEGIN
    PRINT '      ' + convert(varchar,@ord_id)+ ' '+ 
          convert(varchar,@ord_date,111)
    FETCH ord_cur INTO @ord_id,@ord_date
  END
  CLOSE ord_cur
  DEALLOCATE ord_cur
  /************/

  FETCH cust_cur INTO @cust_id
END

CLOSE cust_cur
DEALLOCATE cust_cur
   


/* 3. 透過CURSOR修改或刪除資料 */
/***********************************************************/
(註1)此Cursor不可是READ_ONLY

USE NORTHWIND
DROP TABLE PRODUCTS2
SELECT * INTO PRODUCTS2 FROM PRODUCTS

DECLARE @unitprc numeric(10,4)
DECLARE cur1 CURSOR FOR SELECT UNITPRICE FROM PRODUCTS2
OPEN cur1
FETCH cur1 INTO @unitprc 
WHILE @@FETCH_STATUS=0
BEGIN
 UPDATE PRODUCTS2 SET UNITPRICE = @unitprc*1.1
   WHERE CURRENT OF cur1

 FETCH cur1 INTO @unitprc
END
CLOSE cur1
DEALLOCATE cur1


/* 4. 預存程序之OUTPUT 參數使用Cursor變數 */
/***********************************************************/
CREATE PROC sp_EmpCur @cur_var CURSOR VARYING OUTPUT
AS
  Set @cur_var = CURSOR FOR SELECT EMPLOYEEID,LASTNAME+' '+FIRSTNAME FROM EMPLOYEES
  /* 上敘述產生區域性CURSOR,即此CURSOR只限被呼叫之程式使用
     (其他批次之程式不可使用) */
  Open @cur_var
GO

/***** 呼叫上述預存程序 *****/
DECLARE @cur_var CURSOR
DECLARE @empid int,@name VARCHAR(80)

EXEC sp_EmpCur @cur_var OUTPUT

FETCH NEXT FROM @cur_var INTO @empid, @name
WHILE @@FETCH_STATUS=0
BEGIN
  PRINT convert(varchar,@empid)+' '+@name
  FETCH NEXT FROM @cur_var INTO @empid, @name
END
 
/* 本批次結束後,CURSOR自動被Close及Deallocate */
GO


/* 5. Local Cursor與Global Cursor之命名空間 */
/***********************************************************/
--5.1 Global Cursor與Local Cursor可有相同的名稱
      當有同名之Cursor存在時,欲存取Global Cursor必須加上Global關鍵字

--5.2 實例示範 
DECLARE cur1 CURSOR FOR SELECT * FROM PRODUCTS
DECLARE cur1 CURSOR LOCAL FOR SELECT * FROM CUSTOMERS

OPEN cur1           --Local Cursor
FETCH cur1

OPEN GLOBAL cur1
FETCH GLOBAL cur1
CLOSE GLOBAL cur1
DEALLOCATE GLOBAL cur1