只談MySQL (第18天) Cursors...

DAY 18

只談MySQL系列第 18 篇

linux mysql

2009-10-30 22:17:08

34954 瀏覽

談到Stored Procedure和Function在資料庫中設計程式的時候, 一定不能缺少Cursors的介紹, Cursor是一個暫存在Cache中的資料集合, 利用Cursor能將這個資料集合中的每筆資料錄進行固定的處理工作, 這可以很方便的用在各種應用上, 例, 可以利用Cursor來處理某個期間內已經結案的銷貨訂單, 對每筆結案的銷貨訂單勾稽後, 變更其狀態為結案, 以防再拿來處理.
與Cursor相關的指令有四:

DECLARE: 宣告Cursor的資料結構及資料來源, 使用SELECT指令來配合
OPEN: 把Cursor啟用並放到Cache中
FETCH: 由Cursor中讀取一筆資料錄
CLOSE: 闗閉Cursor, 由Cache中移除Cursor的暫時資料集合及其定義
我們用一個例子來說明這些指令的運用:

CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;
OPEN cur2;

REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
CLOSE cur2;
END

首先宣告了四個使用者定義變數: done(預設值為0), a, b, c
然後, 宣告了兩個Cursors: cur1, cur2; cur1是由資料表test.t1的id及data欄位構成, 而cur2則由資料表test.t2的i欄位構成
在這裏有一個新的宣告方式: HANDLER, 其宣告語法如下:

DECLARE handler_type HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_type:
    CONTINUE
  | EXIT
  | UNDO

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

我們看前述的例子是:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

意思就是宣告一個繼續作業的HANDLER, 此HANDLER監'NOT FOUND'狀態, 如果出現'NOT FOUND'的狀態時, 就把變數done的值設置為1.
然後兩個OPEN指令, 在Cache中建立cur1和cur2的暫存區域...
因為Cursor既然是資料錄的集合, 我們必然用迴圈指令, 如LOOP、WHILE、REPEAT來逐筆處理Cursor中的資料錄, 這裏用的是REPEAT, 之前我們提到REPEAT的特色是至少會執行迴圈一次.
然後FETCH指令會由Cursor讀進一筆資料錄, 然後把資料錄的指標移到下一筆資料錄. 記得, 用OPEN指令啟用Cursor時, 資料錄指標一定指向第一筆記錄...然後再用FETCH指令逐筆讀入, 並將資料錄指標逐筆向下一筆一筆移動...直到移到最後一筆記錄之後...
FETCH指令把cur1的欄位資料存入變數a, b; 把cur2的欄位資料存入變數c.
然後, "IF NOT done"意思是"IF NOT 'NOT FOUND'", 記得前面用HANDLER宣告將done變數指定為'NOT FOUND'狀態的"代理人"(其實, 那是因為done預設值為0, 0又代表false值)
什麼時候會變成'NOT FOUND'狀態呢? 當資料錄指標移到最後一筆資料錄時, FETCH指令會讀取最後一筆資料錄, 然後將資料錄指標向下移一筆, 但因為已經讀取最後一筆資料錄, 這時Cursor會把資料錄指標定義為'EOF'(End of File), 同時觸發狀態'NOT FOUND', 而由HANDLER的定義, 就是把done值設置為1(1代表true值).
然後IF區塊內的執行...再接下來是UNTIL, 如果done值為0, 代表false, 也代表還有資料錄存在, 也就是資料錄指標還沒成為EOF, 就繼續回到FETCH指令再讀入一筆記錄.
如果done值為1, 代表true, 也就是資料錄指標為EOF, 沒有記錄可讀取了, 就結束REPEAT迴圈.
接下來是CLOSE指令, 清除Cache中的cur1和cur2所佔用的空間, 並且關閉cur1及cur2的定義.
以上, 我們以實例介紹了CURSOR, CURSOR在資料庫的應用中, 是很重要的一個環節, 一定要搞懂它. 此外, 使用Cursor有些限制: