SQL 語法 Cursor

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). 並有以下的限制.

Non-scrollable – 透過 SELECT statement 讀取到的資料,FETCH 時須一行一行的處理,無法跳過,也無法反向讀取(先讀取後面的資料.)
Read-only – 透過 cursor 讀取的資料是無法更新的.
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

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()
$$

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;

沒有解決問題,試試搜尋本站其他內容

Tips

發佈留言 取消回覆

發佈留言取消回覆