mysql存儲(chǔ)過(guò)程之游標(biāo)（DECLARE）原理與用法詳解

本文實(shí)例講述了mysql存儲(chǔ)過(guò)程之游標(biāo)（DECLARE）原理與用法。分享給大家供大家參考，具體如下：

我們?cè)谔幚泶鎯?chǔ)過(guò)程中的結(jié)果集時(shí)，可以使用游標(biāo)，因?yàn)橛螛?biāo)允許我們迭代查詢(xún)返回的一組行，并相應(yīng)地處理每行。mysql的游標(biāo)為只讀，不可滾動(dòng)和敏感三種模式，我們來(lái)看下：

• 只讀：無(wú)法通過(guò)光標(biāo)更新基礎(chǔ)表中的數(shù)據(jù)。
• 不可滾動(dòng)：只能按照select語(yǔ)句確定的順序獲取行。不能以相反的順序獲取行。 此外，不能跳過(guò)行或跳轉(zhuǎn)到結(jié)果集中的特定行。
• 敏感：有兩種游標(biāo)：敏感游標(biāo)和不敏感游標(biāo)。敏感游標(biāo)指向?qū)嶋H數(shù)據(jù)，不敏感游標(biāo)使用數(shù)據(jù)的臨時(shí)副本。敏感游標(biāo)比一個(gè)不敏感的游標(biāo)執(zhí)行得更快，因?yàn)樗恍枰R時(shí)拷貝數(shù)據(jù)。但是，對(duì)其他連接的數(shù)據(jù)所做的任何更改都將影響由敏感游標(biāo)使用的數(shù)據(jù)，因此，如果不更新敏感游標(biāo)所使用的數(shù)據(jù)，則更安全。 MySQL游標(biāo)是敏感的。

我們可以在存儲(chǔ)過(guò)程，存儲(chǔ)函數(shù)和觸發(fā)器中使用MySQL游標(biāo)，咱們先來(lái)看下使用DECLARE語(yǔ)句聲明的語(yǔ)法：

DECLARE cursor_name CURSOR FOR SELECT_statement;

我們要注意下，游標(biāo)聲明必須在變量聲明之后。如果在變量聲明之前聲明游標(biāo)，mysql將會(huì)發(fā)出一個(gè)錯(cuò)誤，另外游標(biāo)必須始終與SELECT語(yǔ)句相關(guān)聯(lián)。完事呢，我們來(lái)使用OPEN語(yǔ)句打開(kāi)游標(biāo)。OPEN語(yǔ)句初始化游標(biāo)的結(jié)果集，因此我們必須在從結(jié)果集中提取行之前調(diào)用OPEN語(yǔ)句：

OPEN cursor_name;

然后，我們來(lái)使用FETCH語(yǔ)句來(lái)檢索光標(biāo)指向的下一行，并將光標(biāo)移動(dòng)到結(jié)果集中的下一行：

FETCH cursor_name INTO variables list;

之后，我們就可以檢查是否有任何行記錄可用，然后再提取它。完事最后還要記得調(diào)用CLOSE語(yǔ)句來(lái)停用光標(biāo)并釋放與之關(guān)聯(lián)的內(nèi)存：

CLOSE cursor_name;

我們要知道，當(dāng)光標(biāo)不再使用時(shí)，應(yīng)該關(guān)閉它。當(dāng)我們使用mysql游標(biāo)時(shí)，還必須聲明一個(gè)NOT FOUND處理程序來(lái)處理當(dāng)游標(biāo)找不到任何行時(shí)的情況。 因?yàn)槊看握{(diào)用FETCH語(yǔ)句時(shí)，游標(biāo)會(huì)嘗試讀取結(jié)果集中的下一行。 當(dāng)光標(biāo)到達(dá)結(jié)果集的末尾時(shí)，它將無(wú)法獲得數(shù)據(jù)，并且會(huì)產(chǎn)生一個(gè)條件。NOT FOUND處理程序用于處理這種情況，我們來(lái)看下它的語(yǔ)法結(jié)構(gòu)：

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

finished是一個(gè)變量，指示光標(biāo)到達(dá)結(jié)果集的結(jié)尾。請(qǐng)注意，處理程序聲明必須出現(xiàn)在存儲(chǔ)過(guò)程中的變量和游標(biāo)聲明之后。我們?cè)賮?lái)看下mysql游標(biāo)的運(yùn)行原理圖：

咱們接下來(lái)，就要開(kāi)發(fā)一個(gè)存儲(chǔ)過(guò)程用來(lái)獲取employees表中所有員工的電子郵件列表。我們先來(lái)聲明一些變量，一個(gè)用于循環(huán)員工電子郵件的游標(biāo)和一個(gè)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;
接下來(lái)，使用OPEN語(yǔ)句打開(kāi)email_cursor：
OPEN email_cursor;

然后，迭代電子郵件列表，并使用分隔符(;)連接每個(gè)電子郵件：

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;

之后，我們?cè)谘h(huán)中，使用v_finished變量來(lái)檢查列表中是否有任何電子郵件來(lái)終止循環(huán)，完事呢，使用CLOSE語(yǔ)句關(guān)閉游標(biāo)：

CLOSE email_cursor;

我們來(lái)看下build_email_list存儲(chǔ)過(guò)程所有代碼：

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 ;

我們來(lái)使用以下腳本測(cè)試build_email_list存儲(chǔ)過(guò)程：

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

至于結(jié)果，咱就不贅述了哈。

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專(zhuān)題：《MySQL存儲(chǔ)過(guò)程技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。