MySQL游標的介紹與使用

更新時間：2022年12月16日 15:05:29 作者：知其黑、受其白

雖然我們也可以通過篩選條件WHERE和HAVING，或者是限定返回記錄的關鍵字LIMIT返回一條記錄，但是，卻無法在結果集中像指針一樣，向前定位一條記錄、向后定位一條記錄，或者是隨意定位到某一條記錄，并對記錄的數據進行處理

定義

游標（Cursor）是處理數據的一種方法，為了查看或者處理結果集中的數據，游標提供了在結果集中一次一行遍歷數據的能力。

游標也是一種面向過程的 sql 編程方法，所以一般在存儲過程、函數、觸發(fā)器、循環(huán)處理中使用。

游標主要用于交互式應用，其中用戶需要滾動屏幕上的數據，并對數據進行瀏覽或做出更改。

游標的作用

游標相當于一個指針，這個指針指向 select 的第一行數據，可以通過移動指針來遍歷后面的數據。

游標是對查詢出來的結果集作為一個單元來有效的處理。

游標可以定在該單元中的特定行，從結果集的當前行檢索一行或多行。

可以對結果集當前行做修改。

一般不使用游標，但是需要逐條處理數據的時候，游標顯得十分重要。

游標的使用

在mysql中，游標可以在存儲過程、函數、觸發(fā)器和事件中使用。

游標需要與相關 handler 一起使用，并在 handler 之前定義。

游標有以下三個屬性：

Asensitive: 數據庫也可以選擇不復制結果集
Read only: 不可更新，只讀
Nonscrollable: 游標只能向一個方向前進，并且不可以跳過任何一行數據。

聲明游標：

創(chuàng)建一個游標，并指定這個游標需要遍歷的select查詢，聲明游標時并不會去執(zhí)行這個sql。

打開游標：

打開游標的時候，會執(zhí)行游標對應的select語句。

遍歷數據：

使用游標循環(huán)遍歷select結果中每一行數據，然后進行處理。

業(yè)務操作：

對遍歷到的每行數據進行操作的過程，可以放置任何需要執(zhí)行的執(zhí)行的語句(增刪改查):這里視具體情況而定。

關閉游標：

游標使用完之后一定要釋放（游標占用的內存還是有點大的）。

注：使用的臨時字段需要在定義游標之前進行聲明。

游標語法

游標的使用過程：聲明游標、打開游標、遍歷游標、關閉游標

聲明游標：DECLARE 游標名稱 CURSOR FOR 查詢語句;

打開游標：open 游標名稱;

遍歷游標：fetch 游標名稱 into 變量列表;

取出當前行的結果，將結果放在對應的變量中，并將游標指針指向下一行的數據。

當調用 fetch 的時候，會獲取當前行的數據，如果當前行無數據，會引發(fā) mysql 內部的 NOT FOUND 錯誤。

關閉游標：close 游標名稱; 游標使用完畢之后一定要關閉。

條件處理

DECLARE CONTINE HANDLER 表達式 1 SET 表達式 2：

這段代碼的作用是定義一個 CONTINE HANDLER，這個的作用是當表達式 1 的條件出現(xiàn)時，將執(zhí)行表達式 2 的語句。

用這個語句可以實現(xiàn)條件的變更實質是利用 mysql 的異常處理，也常常在游標上使用，來輔助判斷游標數據是否遍歷完了。

例如 DECLARE CONTINUE HANDLER FOR NOT FOUND … 的語句，這是為了對游標沒有下一條記錄可供訪問的情況做出異常處理。

創(chuàng)建表-test1-test2-test3

DROP TABLE IF EXISTS test1; 
CREATE TABLE test1(a int,b int); 
INSERT INTO test1 VALUES (1,2),(3,4),(5,6); 
DROP TABLE IF EXISTS test2; 
CREATE TABLE test2(a int); 
INSERT INTO test2 VALUES (100),(200),(300); 
DROP TABLE IF EXISTS test3; 
CREATE TABLE test3(b int); 
INSERT INTO test3 VALUES (400),(500),(600);

寫一個函數，計算 test1 表中 a、b 字段所有的和

/*刪除函數*/ 
DROP FUNCTION IF EXISTS fun1; 
/*聲明結束符為$*/ 
DELIMITER $ 
/*創(chuàng)建函數*/ 
CREATE FUNCTION fun1(v_max_a int) 
  RETURNS int 
  BEGIN 
    /*用于保存結果*/ 
    DECLARE v_total int DEFAULT 0; 
    /*創(chuàng)建一個變量，用來保存當前行中a的值*/
    DECLARE v_a int DEFAULT 0; 
    /*創(chuàng)建一個變量，用來保存當前行中b的值*/ 
    DECLARE v_b int DEFAULT 0; 
    /*創(chuàng)建游標結束標志變量*/ 
    DECLARE v_done int DEFAULT FALSE; 
    /*創(chuàng)建游標*/ 
    DECLARE cur_test1 CURSOR FOR SELECT a,b from test1 where a<=v_max_a; 
    /*設置游標結束時v_done的值為true，可以v_done來判斷游標是否結束了*/ 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; 
    /*設置v_total初始值*/ 
    SET v_total = 0; 
    /*打開游標*/ 
    OPEN cur_test1; 
    /*使用Loop循環(huán)遍歷游標*/ 
    a:LOOP 
      /*先獲取當前行的數據，然后將當前行的數據放入v_a,v_b中，如果當前行無數據，v_done會被置 為true*/
      FETCH cur_test1 INTO v_a, v_b; 
      /*通過v_done來判斷游標是否結束了，退出循環(huán)*/ 
      if v_done THEN 
      LEAVE a; 
      END IF; 
      /*對v_total值累加處理*/ 
      SET v_total = v_total + v_a + v_b;
    END LOOP; 
    /*關閉游標*/ 
    CLOSE cur_test1; 
    /*返回結果*/ 
    RETURN v_total; 
  END $ 
/*結束符置為;*/ 
DELIMITER ;

其中 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; 是異常處理的語法，意思是當遇到 NOT FOUND 錯誤時,將 v_done 設為 ture，continue 繼續(xù)執(zhí)行當前任務。

測試

mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------+------+
3 rows in set (0.00 sec)
mysql> select fun1(1);
+---------+
| fun1(1) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)
mysql> select fun1(4);
+---------+
| fun1(4) |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)
mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------+------+
3 rows in set (0.00 sec)
mysql> select fun1(5);
+---------+
| fun1(5) |
+---------+
|      21 |
+---------+
1 row in set (0.00 sec)