MySQL 游標

• SQL語句是“面向集合編程”，重點在於“獲得什麼”，而不是“如何獲得”。
• 有時候我們不需要對查詢結構集的每一條都進行相同的操作，而是只操作其中的某些行，這時候就需要面向過程的編程方法，而游標就是面向過程編程方式的體現
• 游標就相當於“指針”，它一次只指向一行
• 游標的作用就是用於對查詢資料庫所返回的記錄進行遍歷，以便進行相應的操作

游標的使用

聲明（給定結果集）、打開、通過游標獲取數據、關閉、釋放游標

• 聲明，給定結果集，存儲過程結束後游標就被清理

  DECLARE cursor_name CURSOR FOR (SELECT...);
  

• 打開游標，將結果集送到游標工作區

  OPEN cursor_name;
  

• 通過游標獲取數據

  游標先判斷當前行是否為空，若為空則改變done，若不為空則將數據存放到臨時變數中，讀取後進入下一行為下次讀取做準備。

  一定要想明白FETCH的運行方式，不然可能會出現重覆FETCH到數據的情況

  FETCH cursor_name INTO (變數名s);
  

• 關閉游標

  CLOSE cursor_name;
  

• 因為每次調用FETCH游標就會嘗試下一行，因此還要聲明一個 NOT FOUND處理程式來處理游標讀取不到下一行的情況

  DECLARE CONTINUE HANDLER FOR not found SET done = true;
  

  或者

  DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = true;
  

  SQLSTATE '02000'可以看做和not found一樣，SQLSTATE '02000'是當沒有更多的行以供迴圈時出現的一個條件。

  上面兩種都可行，至於done賦值為true還是為1，就要看done是如何定義的，當然定義為INIT的為可以初始化為true

   

使用舉例1

現有如下數據表

mysql> select * from t;
+----+------+-----------------+
| id | nums | content         |
+----+------+-----------------+
|  1 |    2 | NULL            |
|  2 |    6 | 二六一十二      |
|  3 |    3 | 三三得九        |
+----+------+-----------------+
3 rows in set (0.00 sec)

我們通過游標讀取每一行並實現將nums導入到新數據表中，在實際工作中這種方式可以大大減少我們"分表"時的操作難度。

DELIMITER //

CREATE PROCEDURE transferData()
BEGIN
DECLARE temp_num INT;
DECLARE done BOOLEAN DEFAULT false;
DECLARE cur CURSOR FOR select nums FROM t;
DECLARE continue HANDLER FOR not found SET done = true;

CREATE TABLE IF NOT EXISTS t2 (auto_id int PRIMARY KEY AUTO_INCREMENT, val int not null);

OPEN cur;
REPEAT
IF NOT done THEN
FETCH cur INTO temp_num;
INSERT INTO t2 (val) VALUES (temp_num);
END IF;
UNTIL done=true END REPEAT;
CLOSE cur;
SELECT * from t2;
END//

DELIMITER ;

執行該存儲過程後自動顯示如下結果：

mysql> select * from t2;
    -> //
+---------+-----+
| auto_id | val |
+---------+-----+
|       1 |   2 |
|       2 |   6 |
|       3 |   3 |
|       4 |   3 |
+---------+-----+
4 rows in set (0.00 sec)

我們會發現，為什麼這裡多了一行數據？看起來是被重覆FETCH並插入了，究其原因，是因為沒有理清楚FETCH的運作方式---沒有搞清楚什麼時候FETCH會改變done

如果我們改成

CREATE PROCEDURE transferData2()
BEGIN
DECLARE temp_num INT;
DECLARE done BOOLEAN DEFAULT false;
DECLARE cur CURSOR FOR select nums FROM t;
DECLARE continue HANDLER FOR not found SET done = true;

CREATE TABLE IF NOT EXISTS t3 (auto_id int PRIMARY KEY AUTO_INCREMENT, val int not null);

OPEN cur;
REPEAT
FETCH cur INTO temp_num;
IF NOT done THEN
INSERT INTO t3 (val) VALUES (temp_num);
END IF;
UNTIL done=true END REPEAT;
CLOSE cur;
SELECT * from t3;
END//

則結果會符合我們的預期

mysql> select * from t3//
+---------+-----+
| auto_id | val |
+---------+-----+
|       1 |   2 |
|       2 |   6 |
|       3 |   3 |
+---------+-----+
3 rows in set (0.00 sec)