1 遊標

SQL檢索操作返回一組稱為結果集的行。這組返回的行都是與SQL語句相匹配的行（零行或多行）。
有時，需要在檢索出來的行中前進或後退一行或多行。這就是使用遊標的原因。遊標（cursor）是一個儲存在SQL伺服器上的資料庫查詢，它不是一條 SELECT 語句，而是被該語句檢索出來的結果集。在儲存了遊標之後，應用程式可以根據需要滾動或瀏覽其中的資料。
遊標主要用於互動式應用，其中使用者需要滾動螢幕上的資料，並對資料進行瀏覽或做出更改。

只能用於儲存過程
不像多數DBMS，MySQL遊標只能用於儲存過程（和函式）。

2 使用遊標

使用遊標涉及幾個明確的步驟。

• 在能夠使用遊標前，必須宣告（定義）它。這個過程實際上沒有檢索資料，它只是定義要使用的 SELECT 語句。

• 一旦宣告後，必須開啟遊標以供使用。這個過程用前面定義的SELECT 語句把資料實際檢索出來。

• 對於填有資料的遊標，根據需要取出（檢索）各行。

• 在結束遊標使用時，必須關閉遊標。
  在宣告遊標後，可根據需要頻繁地開啟和關閉遊標。在遊標開啟後，可根據需要頻繁地執行取操作。

2.1 建立遊標

遊標用 DECLARE 語句建立。 DECLARE 命名遊標，並定義相應的 SELECT 語句，根據需要帶 WHERE 和其他子句。
例如，下面的語句定義了名為 ordernumbers 的遊標，使用了可以檢索所有訂單的 SELECT 語句。

CREATE PROCEDURE processorders ()BEGIN
    DECLARE ordernumbers CURSOR FOR
  SELECT order_num FROM orders;END;

DECLARE 語句用來定義和命名遊標，這裡為 ordernumbers 。 儲存過程處理完成後，遊標就消失（因為它侷限於儲存過程）。

2.2 開啟和關閉遊標

CREATE PROCEDURE processorders ()
BEGIN
-- 定義遊標DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;

-- 開啟遊標
OPEN ordernumbers;

-- 關閉遊標
CLOSE ordernumbers;

END;

遊標用 OPEN CURSOR 語句來開啟：
OPEN ordernumbers;
在處理 OPEN 語句時執行查詢，儲存檢索出的資料以供瀏覽和滾動。
遊標處理完成後，應當使用如下語句關閉遊標：
CLOSE ordernumbers;
CLOSE 釋放遊標使用的所有內部記憶體和資源，因此在每個遊標不再需要時都應該關閉。

在一個遊標關閉後，如果沒有重新開啟，則不能使用它。但是，使用宣告過的遊標不需要再次宣告，用 OPEN 語句開啟它就可以了。

隱含關閉
如果你不明確關閉遊標，MySQL將會在到達 END 語句時自動關閉它。

2.3 使用遊標資料

在一個遊標被開啟後，可以使用 FETCH 語句分別訪問它的每一行。FETCH 指定檢索什麼資料（所需的列），檢索出來的資料儲存在什麼地方。它還向前移動遊標中的內部行指標，使下一條 FETCH 語句檢索下一行（不重複讀取同一行）。

CREATE PROCEDURE processorders ()BEGIN-- 定義變數DECLARE o INT;-- 定義遊標DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;-- 開啟遊標OPEN ordernumbers;-- 取訂單號賦值給oFETCH ordernumbers INTO o;-- 關閉遊標CLOSE ordernumbers;END;

其中 FETCH 用來檢索當前行的 order_num 列（將自動從第一行開始）到一個名為 o 的區域性宣告的變數中。對檢索出的資料不做任何處理。

下面的例子，迴圈檢索資料，從第一行到最後一行：

CREATE PROCEDURE processorders ()BEGIN-- 定義變數DECLARE done TINYINT(1) DEFAULT 0;DECLARE o INT;-- 定義遊標DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;-- 定義CONTINUE HANDLERDECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;-- 開啟遊標OPEN ordernumbers;-- 迴圈開始REPEAT-- 取訂單號賦值給oFETCH ordernumbers INTO o;-- 結束迴圈UNTIL done END REPEAT;-- 關閉遊標CLOSE ordernumbers;END;

與前一個例子一樣，這個例子使用 FETCH 檢索當前 order_num到宣告的名為 o 的變數中。但與前一個例子不一樣的是，這個例子中的 FETCH 是在 REPEAT 內，因此它反覆執行直到 done 為真（由 UNTILdone END REPEAT; 規定）。為使它起作用，用一個 DEFAULT 0 （假，不結束）定義變數 done 。那麼， done 怎樣才能在結束時被設定為真呢？答案是用以下語句：
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
這條語句定義了一個 CONTINUE HANDLER ，它是在條件出現時被執行的程式碼。這裡，它指出SQLSTATE '02000' 出現時， SET done=1。SQLSTATE '02000' 是一個未找到條件，當 REPEAT 由於沒有更多的行供迴圈而不能繼續時，出現這個條件。

---

MySQL的錯誤程式碼：

錯誤碼	說明
'00'	成功
'01'	警告
'02'	未找到
> '02'	異常

'02'——這在遊標的上下文中是相關的，用於控制當遊標到達資料集末尾時發生的情況。對於不檢索任何行的語句，也會出現這種情況 。

---

DECLARE 語句的次序
DECLARE 語句的釋出存在特定的次序。用 DECLARE 語句定義的區域性變數必須在定義任意遊標或控制程式碼之前定義，而控制程式碼必須在遊標之後定義。不遵守此順序將產生錯誤訊息。
Variable or condition declaration after cursor or handler declaration
Cursor declaration after handler declaration

如果呼叫這個儲存過程，它將定義幾個變數和一個 CONTINUE HANDLER ，定義並開啟一個遊標，重複讀取所有行，然後關閉遊標。
如果一切正常，你可以在迴圈內放入任意需要的處理（在 FETCH 語句之後，迴圈結束之前）。

---

為了把這些內容組織起來，下面給出我們的遊標儲存過程樣例的更進一步修改的版本，這次對取出的資料進行某種實際的處理：

CREATE PROCEDURE processorders ()BEGIN-- 定義變數DECLARE done TINYINT(1) DEFAULT 0;DECLARE o INT;DECLARE t DECIMAL(8,2);-- 定義遊標DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;-- 定義CONTINUE HANDLERDECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;-- 建立一個表儲存結果CREATE TABLE IF NOT EXISTS ordertotals(
    order_num INT,
    total DECIMAL(8,2)
);-- 開啟遊標OPEN ordernumbers;-- 迴圈開始REPEAT-- 取訂單號賦值給oFETCH ordernumbers INTO o;-- 執行ordertotalmye儲存過程CALL ordertotalmye(o,1,t);-- 往ordertotals表中插入資料INSERT INTO ordertotals(order_num,total)VALUES(o,t);-- 結束迴圈UNTIL done END REPEAT;-- 關閉遊標CLOSE ordernumbers;END;

在這個例子中，我們增加了另一個名為 t 的變數（儲存每個訂單的合計）。此儲存過程還在執行中建立了一個新表（如果它不存在的話），名為 ordertotals 。這個表將儲存儲存過程生成的結果。 FETCH像以前一樣取每個 order_num ，然後用 CALL 執行另一個儲存過程(ordertotalmye)來計算每個訂單的帶稅的合計（結果儲存到 t ）。最後，用 INSERT 儲存每個訂單的訂單號和合計。
此儲存過程不返回資料，但它能夠建立和填充另一個表，可以用一條簡單的 SELECT 語句檢視該表：

CALL processorders();SELECT * FROM ordertotals ORDER BY order_num;

cursor.jpg

這樣，我們就得到了儲存過程、遊標、逐行處理以及儲存過程呼叫其他儲存過程的一個完整的工作樣例。

作者：Love零O
連結：