SQL 語法 Cursor

Loading

SQL 可以快速處理大量的資料,但有時候需要依據 SQL 每一個紀錄 (Record) 裡的資料來進行處理,如把資料另外儲存或是計算成為新的欄位資料.這時候我們可以透過 CURSOR (搭配 OPEN , FETCH , CLOSE 以及 DECLARE HANDLER – https://benjr.tw/103022 來使用).

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE cursor_name CURSOR FOR SELECT_statement;
OPEN cursor_name;
FETCH cursor_name INTO x;
CLOSE cursor_name;

Cursor 只能使用在 stored programs (Procedure 或是 function). 並有以下的限制.

  1. Non-scrollable – 透過 SELECT statement 讀取到的資料,FETCH 時須一行一行的處理,無法跳過,也無法反向讀取(先讀取後面的資料.)
  2. Read-only – 透過 cursor 讀取的資料是無法更新的.
  3. Asensitive – Cursor 使用指標 (Point)的方式,指向真實資料的位置,並不是複製一份暫時 (Temporary Table)的資表料出來.

範例參考 – https://mariadb.com/kb/en/cursor-overview/

先建立測試用資料表 c1 , c2 與 c3 (比較 c1 與 c2 值並儲存較大的)

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE test;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> USE test;
Database changed
MariaDB [testdb]> CREATE TABLE c1(i INT);
Query OK, 0 rows affected (0.005 sec)

MariaDB [testdb]> CREATE TABLE c2(i INT);
Query OK, 0 rows affected (0.010 sec)

MariaDB [testdb]> CREATE TABLE c3(i INT);
Query OK, 0 rows affected (0.012 sec)

MariaDB [testdb]> INSERT INTO c1 VALUES(5),(50),(500);
Query OK, 3 rows affected (0.003 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [testdb]> INSERT INTO c2 VALUES(10),(20),(30);
Query OK, 3 rows affected (0.002 sec)
Records: 3  Duplicates: 0  Warnings: 0

建立使用 CURSOR (OPEN , FETCH 以及 CLOSE) 的 Procedure (stored programs)

DELIMITER //

CREATE PROCEDURE p1()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE x, y INT;
  DECLARE cur1 CURSOR FOR SELECT i FROM test.c1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.c2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO x;
    FETCH cur2 INTO y;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF x < y THEN
      INSERT INTO test.c3 VALUES (x);
    ELSE
      INSERT INTO test.c3 VALUES (y);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END; //

DELIMITER ;

程式說明:

DELIMITER //

//

DELIMITER ;
  • DELIMITER // //
    mysql 透過分號 “;” 來表示分隔(一個敘述完成),但函數裡面可能會有多個 分隔,這時候可以使用 DELIMITER // … // ,來表示裡面是一整個敘述.
  • DELIMITER ;
    把結束符號修改回為 分號 “;” .
CREATE PROCEDURE p1()
BEGIN

END;

建立一個 PROCEDURE與其名稱,不使用傳入與回傳值.

  DECLARE done INT DEFAULT FALSE;
  DECLARE x, y INT;

一般變數宣告.

  DECLARE cur1 CURSOR FOR SELECT i FROM test.c1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.c2;

CURSOR 變數宣告如下:

DECLARE cursor_name CURSOR FOR select_statement

這時候 cur1 , cur2 就像兩個指標,指向剛剛透過 SELECT 讀取到的資料.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

DECLARE HANDLER 宣告方式如下:

DECLARE handler_type HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_type:
    CONTINUE
  | EXIT 
  | UNDO

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mariadb_error_code
  • CONTINUE : 繼續執行目前的程序.
  • NOT FOUND : 表示已經沒有資料了.
  • SET done = TRUE : 當讀不到資料 (NOT FOUND) 時將變數 done 設定為 TRUE .

這一整段就是當資料處理完時將 變數 done 設定為 TRUE ,後續程式會依據此變數來決定是否結束迴圈.

  OPEN cur1;
  OPEN cur2;

開啟剛剛宣告的 cursor .

  read_loop: LOOP
  END LOOP;

LOOP 沒有像是 WHILE 或 REPEAT 可以定義 search_condition 來決定是否執行迴圈內的指令,它需搭配 ITERATE (繼續) , LEAVE (離開) 來使用.

      LEAVE read_loop;

LEAVE : 用於退出有定義標籤的流控制結構 (flow control construct), 如果標籤是定義於最外面存儲的程序時,則退出程序.

    FETCH cur1 INTO x;
    FETCH cur2 INTO y;

cursor 要讀取資料時須透過 FETCH 來一筆一筆讀取,並將資料儲存到變數 x 與 y.

    IF done THEN
      LEAVE read_loop;
    END IF;

當讀不到資料 (NOT FOUND) 時變數 done 為 TRUE,這時候就跳出迴圈(離開程序).

    IF x < y THEN
      INSERT INTO test.c3 VALUES (x);
    ELSE
      INSERT INTO test.c3 VALUES (y);
    END IF;

透過 IF 來比較 x (c1 資料表) 還是 y (c2 資料表) 的數值大,並儲存到 c3 資料表.

  CLOSE cur1;
  CLOSE cur2;

開啟的 cursor 在結束時須關閉.

測試一下,的確看到 c3 資料表的資料.

MariaDB [test]> SELECT * FROM c3;
Empty set (0.001 sec)

MariaDB [test]> CALL P1();
Query OK, 3 rows affected (0.009 sec)

MariaDB [test]> SELECT * FROM c3;
+------+
| i    |
+------+
|    5 |
|   20 |
|   30 |
+------+
3 rows in set (0.001 sec)

Tips

  1. Cursor on TEMPORARY TABLE
    可以使用 nested BEGIN .. END 來做 範例 – https://forums.mysql.com/read.php?98,40720,40872#msg-40872

    delimiter $$
    
    create procedure test_temp()
    begin
    drop temporary table if exists tmp;
    create temporary table tmp(id int unsigned);
    insert into tmp (id) values (1);
    begin
    declare v_id int unsigned;
    declare csr1 cursor for select * from tmp;
    open csr1;
    fetch csr1 into v_id;
    select v_id;
    close csr1;
    end;
    end;
    $$
    
    call test_temp()
    $$
    
  2. Multi Cursor
    在一個 loop 內只能有一個 Cursor ,如果要使用多個 Cursor 時可以使用 Block , 範例 https://stackoverflow.com/questions/6099500/multiple-cursors-in-nested-loops-in-mysql

    BLOCK1: begin
        declare v_col1 int;                     
        declare no_more_rows boolean1 := FALSE;  
        declare cursor1 cursor for              
            select col1
            from   MyTable;
        declare continue handler for not found  
            set no_more_rows1 := TRUE;           
        open cursor1;
        LOOP1: loop
            fetch cursor1
            into  v_col1;
            if no_more_rows1 then
                close cursor1;
                leave LOOP1;
            end if;
            BLOCK2: begin
                declare v_col2 int;
                declare no_more_rows2 boolean := FALSE;
                declare cursor2 cursor for
                    select col2
                    from   MyOtherTable
                    where  ref_id = v_col1;
               declare continue handler for not found
                   set no_more_rows2 := TRUE;
                open cursor2;
                LOOP2: loop
                    fetch cursor2
                    into  v_col2;
                    if no_more_rows then
                        close cursor2;
                        leave LOOP2;
                    end if;
                end loop LOOP2;
            end BLOCK2;
        end loop LOOP1;
    end BLOCK1;
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料