MySQL遊標的介紹與使用

2022-12-19 14:01:49

定義

遊標（Cursor）是處理資料的一種方法，為了檢視或者處理結果集中的資料，遊標提供了在結果集中一次一行遍歷資料的能力。

遊標也是一種程式導向的 sql 程式設計方法，所以一般在儲存過程、函數、觸發器、迴圈處理中使用。

遊標主要用於互動式應用，其中使用者需要捲動螢幕上的資料，並對資料進行瀏覽或做出更改。

遊標的作用

遊標相當於一個指標，這個指標指向 select 的第一行資料，可以通過移動指標來遍歷後面的資料。

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

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

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

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

遊標的使用

在mysql中，遊標可以在儲存過程、函數、觸發器和事件中使用。

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

遊標有以下三個屬性：

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

宣告遊標：

建立一個遊標，並指定這個遊標需要遍歷的select查詢，宣告遊標時並不會去執行這個sql。

開啟遊標：

開啟遊標的時候，會執行遊標對應的select語句。

遍歷資料：

使用遊標迴圈遍歷select結果中每一行資料，然後進行處理。

業務操作：

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

關閉遊標：

遊標使用完之後一定要釋放（遊標佔用的記憶體還是有點大的）。

注：使用的臨時欄位需要在定義遊標之前進行宣告。

遊標語法

遊標的使用過程：宣告遊標、開啟遊標、遍歷遊標、關閉遊標

宣告遊標：DECLARE 遊標名稱 CURSOR FOR 查詢語句;

開啟遊標：open 遊標名稱;

遍歷遊標：fetch 遊標名稱 into 變數列表;

取出當前行的結果，將結果放在對應的變數中，並將遊標指標指向下一行的資料。

當呼叫 fetch 的時候，會獲取當前行的資料，如果當前行無資料，會引發 mysql 內部的 NOT FOUND 錯誤。

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

條件處理

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

這段程式碼的作用是定義一個 CONTINE HANDLER，這個的作用是當表示式 1 的條件出現時，將執行表示式 2 的語句。

用這個語句可以實現條件的變更實質是利用 mysql 的例外處理，也常常在遊標上使用，來輔助判斷遊標資料是否遍歷完了。

例如 DECLARE CONTINUE HANDLER FOR NOT FOUND … 的語句，這是為了對遊標沒有下一條記錄可供存取的情況做出例外處理。

建立表-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 $ 
/*建立函數*/ 
CREATE FUNCTION fun1(v_max_a int) 
  RETURNS int 
  BEGIN 
    /*用於儲存結果*/ 
    DECLARE v_total int DEFAULT 0; 
    /*建立一個變數，用來儲存當前行中a的值*/
    DECLARE v_a int DEFAULT 0; 
    /*建立一個變數，用來儲存當前行中b的值*/ 
    DECLARE v_b int DEFAULT 0; 
    /*建立遊標結束標誌變數*/ 
    DECLARE v_done int DEFAULT FALSE; 
    /*建立遊標*/ 
    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迴圈遍歷遊標*/ 
    a:LOOP 
      /*先獲取當前行的資料，然後將當前行的資料放入v_a,v_b中，如果當前行無資料，v_done會被置 為true*/
      FETCH cur_test1 INTO v_a, v_b; 
      /*通過v_done來判斷遊標是否結束了，退出迴圈*/ 
      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 繼續執行當前任務。

測試

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)