MySQL游標 - tw511教學網

在本教學中，您將學習如何在儲存過程中使用MySQL游標來遍歷SELECT語句返回的結果集。

MySQL游標簡介

要處理儲存過程中的結果集，請使用游標。游標允許您疊代查詢返回的一組行，並相應地處理每行。

MySQL游標為唯讀，不可捲動和敏感。

唯讀：無法通過游標更新基礎表中的資料。
不可捲動：只能按照SELECT語句確定的順序獲取行。不能以相反的順序獲取行。此外，不能跳過行或跳轉到結果集中的特定行。
敏感：有兩種游標：敏感游標和不敏感游標。敏感游標指向實際資料，不敏感游標使用資料的臨時副本。敏感游標比一個不敏感的游標執行得更快，因為它不需要臨時拷貝資料。但是，對其他連線的資料所做的任何更改都將影響由敏感游標使用的資料，因此，如果不更新敏感游標所使用的資料，則更安全。 MySQL游標是敏感的。

使用MySQL游標

首先，必須使用DECLARE語句宣告游標：

DECLARE cursor_name CURSOR FOR SELECT_statement;

游標宣告必須在變數宣告之後。如果在變數宣告之前宣告游標，MySQL將會發出一個錯誤。游標必須始終與SELECT語句相關聯。

接下來，使用OPEN語句開啟游標。OPEN語句初始化游標的結果集，因此您必須在從結果集中提取行之前呼叫OPEN語句。

OPEN cursor_name;

然後，使用FETCH語句來檢索游標指向的下一行，並將游標移動到結果集中的下一行。

FETCH cursor_name INTO variables list;

之後，可以檢查是否有任何行記錄可用，然後再提取它。

最後，呼叫CLOSE語句來停用游標並釋放與之關聯的記憶體，如下所示：

CLOSE cursor_name;

當游標不再使用時，應該關閉它。

當使用MySQL游標時，還必須宣告一個NOT FOUND處理程式來處理當游標找不到任何行時的情況。因為每次呼叫FETCH語句時，游標會嘗試讀取結果集中的下一行。當游標到達結果集的末尾時，它將無法獲得資料，並且會產生一個條件。處理程式用於處理這種情況。

要宣告一個NOT FOUND處理程式，參考以下語法：

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

finished是一個變數，指示游標到達結果集的結尾。請注意，處理程式宣告必須出現在儲存過程中的變數和游標宣告之後。

下圖說明了MySQL游標如何工作。

MySQL游標範例

為了更好地演示，我們將開發一個儲存過程，來獲取MySQL範例資料庫(yiibaidb)中employees表中所有員工的電子郵寄清單。

首先，宣告一些變數，一個用於迴圈員工電子郵件的游標和一個NOT FOUND處理程式：

DECLARE finished INTEGER DEFAULT 0;
DECLARE email varchar(255) DEFAULT "";

-- declare cursor for employee email
DEClARE email_cursor CURSOR FOR 
 SELECT email FROM employees;

-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER 
FOR NOT FOUND SET finished = 1;

接下來，使用OPEN語句開啟email_cursor：

OPEN email_cursor;

然後，迭代電子郵寄清單，並使用分隔符(;)連線每個電子郵件：

get_email: LOOP
 FETCH email_cursor INTO v_email;
 IF v_finished = 1 THEN 
 LEAVE get_email;
 END IF;
 -- build email list
 SET email_list = CONCAT(v_email,";",email_list);
END LOOP get_email;

之後，在迴圈中，使用v_finished變數來檢查列表中是否有任何電子郵件來終止迴圈。

最後，使用CLOSE語句關閉游標：

CLOSE email_cursor;

build_email_list儲存過程所有程式碼如下：

DELIMITER $$

CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))
BEGIN

 DECLARE v_finished INTEGER DEFAULT 0;
        DECLARE v_email varchar(100) DEFAULT "";

 -- declare cursor for employee email
 DEClARE email_cursor CURSOR FOR 
 SELECT email FROM employees;

 -- declare NOT FOUND handler
 DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET v_finished = 1;

 OPEN email_cursor;

 get_email: LOOP

 FETCH email_cursor INTO v_email;

 IF v_finished = 1 THEN 
 LEAVE get_email;
 END IF;

 -- build email list
 SET email_list = CONCAT(v_email,";",email_list);

 END LOOP get_email;

 CLOSE email_cursor;

END$$

DELIMITER ;

可以使用以下指令碼測試build_email_list儲存過程：

SET @email_list = "";
CALL build_email_list(@email_list);
SELECT @email_list;

註：由於內容比較長，這裡就不放上輸出結果了。

在本教學中，我們向您展示了如何使用MySQL游標來迭代結果集並相應地處理每一行。