準備資料

建立庫：javacode2018

建立表：test1、test2、test3

/*建庫javacode2018*/drop database if exists javacode2018;create database javacode2018;/*切換到javacode2018庫*/use javacode2018;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);123456789101112131415161718

遊標定義

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

遊標只能在儲存過程和函式中使用。

遊標的作用

如sql：

select a,b from test1;1

上面這個查詢返回了test1中的資料，如果我們想對這些資料進行遍歷處理，此時我們就可以使用遊標來進行操作。

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

遊標的使用步驟

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

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

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

• 關閉遊標：遊標使用完之後一定要關閉。

遊標語法

宣告遊標

DECLARE 遊標名稱 CURSOR FOR 查詢語句;1

一個begin end中只能宣告一個遊標。

開啟遊標

open 遊標名稱;1

遍歷遊標

fetch 遊標名稱 into 變數列表;1

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

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

關閉遊標

close 遊標名稱;1

遊標使用完畢之後一定要關閉。

單遊標示例

寫一個函式，計算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 ;123456789101112131415161718192021222324252627282930313233343536373839404142

上面語句執行過程中可能有問題，解決方式如下。 changchun/

錯誤資訊：Mysql 建立函式出現This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary

mysql的設定預設是不允許建立函式

解決辦法1:

執行： xinyang/

SET GLOBAL log_bin_trust_function_creators = 1;

不過 重啟了 就失效了

注意：有主從複製的時候 從機必須要設定 不然會導致主從同步失敗

解決辦法2：

在my.cnf裡面設定

log-bin-trust-function-creators=1

不過這個需要重啟服務

見效果：

mysql> SELECT a,b 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(2);+---------+| fun1(2) |+---------+|       3 |+---------+1 row in set (0.00 sec)mysql> SELECT fun1(3);+---------+| fun1(3) |+---------+|      10 |+---------+1 row in set (0.00 sec)123456789101112131415161718192021222324252627282930313233

遊標過程詳解

以上面的示例程式碼為例，我們們來看一下游標的詳細執行過程。

遊標中有個指標，當開啟遊標的時候，才會執行遊標對應的select語句，這個指標會指向select結果中第一行記錄。

當呼叫fetch 遊標名稱時，會獲取當前行的資料，如果當前行無資料，會觸發NOT FOUND異常。

當觸發NOT FOUND異常的時候，我們可以使用一個變數來標記一下，如下程式碼：

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE;1

當遊標無資料觸發NOT FOUND異常的時候，將變數v_down的值置為TURE，迴圈中就可以透過v_down的值控制迴圈的退出。

如果當前行有資料，則將當前行資料存到對應的變數中，並將遊標指標指向下一行資料，如下語句：

fetch 遊標名稱 into 變數列表;1

巢狀遊標

寫個儲存過程，遍歷test2、test3，將test2中的a欄位和test3中的b欄位任意組合，插入到test1表中。

建立儲存過程：

/*刪除儲存過程*/DROP PROCEDURE IF EXISTS proc1;/*宣告結束符為$*/DELIMITER $/*建立儲存過程*/CREATE PROCEDURE proc1()
  BEGIN
    /*建立一個變數，用來儲存當前行中a的值*/
    DECLARE v_a int DEFAULT 0;
    /*建立遊標結束標誌變數*/
    DECLARE v_done1 int DEFAULT FALSE;
    /*建立遊標*/
    DECLARE cur_test1 CURSOR FOR SELECT a FROM test2;
    /*設定遊標結束時v_done1的值為true，可以v_done1來判斷遊標cur_test1是否結束了*/
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done1=TRUE;
    /*開啟遊標*/
    OPEN cur_test1;
    /*使用Loop迴圈遍歷遊標*/
    a:LOOP
      FETCH cur_test1 INTO v_a;
      /*透過v_done1來判斷遊標是否結束了，退出迴圈*/
      if v_done1 THEN
        LEAVE a;
      END IF;
      BEGIN
        /*建立一個變數，用來儲存當前行中b的值*/
        DECLARE v_b int DEFAULT 0;
        /*建立遊標結束標誌變數*/
        DECLARE v_done2 int DEFAULT FALSE;
        /*建立遊標*/
        DECLARE cur_test2 CURSOR FOR SELECT b FROM test3;
        /*設定遊標結束時v_done1的值為true，可以v_done1來判斷遊標cur_test2是否結束了*/
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done2=TRUE;
        /*開啟遊標*/
        OPEN cur_test2;
        /*使用Loop迴圈遍歷遊標*/
        b:LOOP
          FETCH cur_test2 INTO v_b;
          /*透過v_done1來判斷遊標是否結束了，退出迴圈*/
          if v_done2 THEN
            LEAVE b;
          END IF;
          /*將v_a、v_b插入test1表中*/
          INSERT INTO test1 VALUES (v_a,v_b);
        END LOOP b;
        /*關閉cur_test2遊標*/
        CLOSE cur_test2;
      END;
    END LOOP;
    /*關閉遊標cur_test1*/
    CLOSE cur_test1;
  END $/*結束符置為;*/DELIMITER ;12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758

見效果：

mysql> DELETE FROM test1;Query OK, 9 rows affected (0.00 sec)mysql> SELECT * FROM test1;Empty set (0.00 sec)mysql> CALL proc1();Query OK, 0 rows affected (0.02 sec)mysql> SELECT * from test1;+------+------+| a    | b    |+------+------+|  100 |  400 ||  100 |  500 ||  100 |  600 ||  200 |  400 ||  200 |  500 ||  200 |  600 ||  300 |  400 ||  300 |  500 ||  300 |  600 |+------+------+9 rows in set (0.00 sec)123456789101112131415161718192021222324

成功插入了9條資料。