Mysql 游標初識-有解無憂

MySql 游標初識

認識

游標(cursor), 按字面意思可理解為, 游動的標識, 或者叫做"游標", 這樣更容易理解. 就好比現有一張表存盤了n行記錄, 然后我想每次取出一行, 進行為所欲為, 這時候就用到了游標cursor, 資料的搬運工, 搬運完當前資料(游標指向當前), 然后又移動到下一條資料的位置.

"移動", 和 "指向" 這兩個詞很重要, 跟C的指標有點類似, 舉一個鏈表的例子吧. a -> b -> c -> d, 需求是求計算鏈表的長度. 則我們需要定義一個游標變數cursor, 默認定位到"a" 節點位置, 還需定義一個技術變數count 默認為0, 然后移動游標cursor (Python里 "=" 即表示"指向"), 沿著 a->b->c->d, 每移動一次, 則游標指向當前節點, 計數器加1....

應用場景, 在于, 好比一條 select xxx where xxx ; 回傳的是一個查詢集, 但我不想啪啪啪一頓回傳一堆資料, 我要自己逐行檢查和寫邏輯判斷, 這時候就需要cursor. 即, 一條sql, 對應N條資料, 取出(依次 or 自定義順序) 資料的介面(interface) / 句柄, 就是游標, 沿著游標方向, 依次可以一次取出1行.

介面(interface)

介面泛指物體把自己提供給外界的一種抽象化物（可以為另一物體），用以由內部操作分離出外部溝通方法，使其能被內部修改而不影響外界其他物體與其互動的方式，

通俗理解, 就是, A只想用B的一些功能,但并不關心B是怎么實作的, 由此B根據A的需求, 提供一些能滿足A的服務, 這些服務, 就是"介面".舉幾個栗子.

我想開車, 車里面是什么樣的我并不關系, 這時候車給了提供了, 方向盤, 油門,離合器, 剎車, 擋位等, 我就是能開了, 這些就是"介面" (好像不太恰當哦); 或者是想學外語, 這時候給我提給了一張VIP卡, 那我就可以學外語了, 這張VIP卡, 就是介面.

我撰寫了一個web網站, 想讓用戶用微信, QQ, 微博, 支付寶賬號也能登陸, 那這時候,我就要向這些大佬公司申請這些用戶ID驗證, 怎么驗證我不管, 只按照他們提供的 "驗證規則"傳參進去, 等待就好, 那, 這個驗證規則邏輯, 就是微信, 微博...向外界提供的介面.

我想要操作電腦, 這時候, windows / linux 給我提給了圖形化 / 命令列的方式讓我操作, 這也是介面.

創建一張MySql二維表存盤資料, 我可以對其進行增刪改查, 那這些功能, 也是介面.

編碼時, 呼叫自定義的或別人的或系統的類的方法時, 這些方法也是介面.

語法

宣告游標: declare 游標名 cursor for select _staetment;
打開游標: open 游標名
取出資料: fetch 游標名 into var1, var2 ....
關閉游標: close 游標名

-- 檔案說明
-- 游標宣告必須在procedure 資料處理之前, 和在變數宣告之后.
DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name;  -- 打開游標(當前塊,唯一命名)
FETCH cursor_name INTO var_name, [, var_name] ... -- 讀取游標資料, 并前進指標
CLOSE cursor_name;  -- 如果不close, 則會在其被宣告的復合陳述句末尾被關閉

案例

用之前的goods, 表操作一波.

-- 查看一下資料
-- out
mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  |  37 |
|   2 | dog  |  72 |
|   3 | pig  |  18 |
+-----+------+-----+
3 rows in set (0.09 sec)

-- 更新一波 cat 的數量吧
mysql> update goods set num = 100 where gid=1;

Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  | 100 |
|   2 | dog  |  72 |
|   3 | pig  |  18 |
+-----+------+-----+
3 rows in set (0.11 sec)

需求是要逐條取出每行資料, 而不是一下子都給我.

-- cursor: 依次獲取每行資料
drop procedure if exists cur1;
delimiter //
create procedure cur1()
begin
	-- select * from goods; 每行有3個值, gid, name, num 
	-- 首先要定義變數來存盤
	-- 宣告和打開游標
	-- fetch 每行資料
	-- 處理邏輯
	-- 關閉游標
end //
delimiter ;

具體實作

drop procedure if exists cur1;
delimiter //
create procedure cur1()
begin
	-- select * from goods; 每行有3個值, gid, name, num 
	-- 首先就為每行資料, 定義相應臨時變數來存盤
	declare tmp_gid int;
	declare tmp_name varchar(20);
	declare tmp_num int;
	-- 宣告和打開游標
	declare getGoods cursor for select gid, name, num from goods;
	open getGoods;
	-- fetch 每行資料, 要一一有對應的變數來接收哦
	fetch getGoods into tmp_gid, tmp_name, tmp_num;
	-- 處理邏輯(這里只列印一下)
	select tmp_gid, tmp_name, tmp_num;
	-- 關閉游標
	close getGoods;
	
end //
delimiter ;

-- out
mysql> call cur1();
+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       1 | cat      |     100 |
+---------+----------+---------+
1 row in set (0.14 sec)

與之前直接取出一行的區別在于, 控制權在我們手里, 真的可以為所欲為, 進一步可以進行判斷,取值等各種編程操作, 真的可以為所欲為.

如何fetch多行呢?

-- fetch 多行 及 游標到尾(沒有資料了, 不會報錯)
drop procedure if exists cur1;
delimiter //
create procedure cur1()
begin
	declare tmp_gid int;
	declare tmp_name varchar(20);
	declare tmp_num int;
	
	declare getGoods cursor for select gid, name, num from goods;
	open getGoods;
	-- fetch and into val1, var2.....
	-- fetch 多行
	fetch getGoods into tmp_gid, tmp_name, tmp_num;
	select tmp_gid, tmp_name, tmp_num;
	
    fetch getGoods into tmp_gid, tmp_name, tmp_num;
	select tmp_gid, tmp_name, tmp_num;
	
    fetch getGoods into tmp_gid, tmp_name, tmp_num;
	select tmp_gid, tmp_name, tmp_num;
	
    fetch getGoods into tmp_gid, tmp_name, tmp_num;
	select tmp_gid, tmp_name, tmp_num;
	-- cursor 即便到尾了(沒有data, 也不會報錯哦)
	fetch getGoods into tmp_gid, tmp_name, tmp_num;
	select tmp_gid, tmp_name, tmp_num;
	-- close
	close getGoods;
end //
delimiter ;

-- out

mysql> call cur1();
+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       1 | cat      |     100 |
+---------+----------+---------+
1 row in set (0.20 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       2 | dog      |      72 |
+---------+----------+---------+
1 row in set (0.37 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       3 | pig      |      18 |
+---------+----------+---------+
1 row in set (0.54 sec)

mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  | 100 |
|   2 | dog  |  72 |
|   3 | pig  |  18 |
+-----+------+-----+
3 rows in set (0.18 sec)

可以看出, 游標的特點是, 每fetch一次, 就往后游動一次, 即稱為游標嘛.

現在, 要采用回圈與游標相配合 取出每條資料. 思路可以是先查詢到表的行數rows_num;作為回圈的退出條件, 然后回圈fetch即可.(while, repeat都行).

-- 通過cursor, 回圈取出每行資料
drop procedure if exists curRepeat;
delimiter //
create procedure curRepeat()
begin
	declare getGoods cursor for select gid, name, num from goods;
	open getGoods;
	-- 回圈fetch
	repeat
		-- 這里需要一大波的定義變數哦.
		fetch getGoods into xxx, xxx, ...;
		select xxx, xxx ...;
		until i > rows_num
	end repeat;
end //
delimiter ;

詳細repeat 實作

-- 通過cursor, 回圈取出每行資料
drop procedure if exists curRepeat;
delimiter //
create procedure curRepeat()
begin
	declare tmp_gid int;
	declare tmp_name varchar(20);
	declare tmp_num int;
	
	declare i int default 0;  -- 自增變數
	declare rows_num int default 0;  -- 存盤查詢集的行數(回圈的退出條件)
	
	declare getGoods cursor for select gid, name, num from goods;
	
    -- 獲取查詢集的行數 rows_num, 注意順序, 操作要放在 declare 之后哦
    -- 錯誤語法: set rows_num := select count(*) from goods;
	select count(*) into rows_num from goods;
	open getGoods;
	-- 回圈fetch
	repeat
		fetch getGoods into tmp_gid, tmp_name, tmp_num;
		-- 業務邏輯處理(這里只是簡單列印), 游標的作用就在于此, 這里可以為所欲為.
		select tmp_gid, tmp_name, tmp_num;
		
		set i := i + 1;
		until i > rows_num
	end repeat;
	-- 別忘了close
	close getGoods;
end //
delimiter ;

-- out
call curRepeat();
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       1 | cat      |     100 |
+---------+----------+---------+
1 row in set (0.12 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       2 | dog      |      72 |
+---------+----------+---------+
1 row in set (0.26 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       3 | pig      |      18 |
+---------+----------+---------+
1 row in set (0.41 sec)

思路二: 游標取值越界時, 利用越界標識判斷, 用到DECLARE ... HANDLER... 處理程式

SQLWARNING 是對所有以01開頭的SQLSTATE代碼的標記
NOT FOUND 是對02開頭的SQLSTATE代碼標記
SQLEXCEPTION 是對除了01, 02外的代碼標記

DECLARE handler_type HANDLER FOR condtion_value ... sp_statement.

-- cursor 越界標識來退出回圈
drop procedure if exists curBorder;
delimiter //
create procedure curBorder()
begin
	declare tmp_gid int;
	declare tmp_name varchar(20);
	declare tmp_num int;
	-- 游標遍歷資料結束的標志
	declare done boolean default false;
	declare getGoods cursor for select gid, name, num from goods;
	-- 退出的 handler 標記, not found 時執行
	-- declare continue handler for NOT FOUND set done := True;
	-- 解決continue 多取一行的問題, 用 EXit 即可
	declare EXIT handler for NOT FOUND set done := True;
	
	open getGoods;
	-- 回圈取每行值
	repeat
		fetch getGoods into tmp_gid, tmp_name, tmp_num;
		-- 業務邏輯處理
		select tmp_gid, tmp_name, tmp_num;
		until done=True
	end repeat;
	-- 總是忘了最后關閉游標呀
	close getGoods;
	
end //
delimiter ;

-- out

mysql> call curBorder();
+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       1 | cat      |     100 |
+---------+----------+---------+
1 row in set (0.12 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       2 | dog      |      72 |
+---------+----------+---------+
1 row in set (0.27 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       3 | pig      |      18 |
+---------+----------+---------+
1 row in set (0.38 sec)

Query OK, 0 rows affected (0.00 sec)

小結 handler 型別

continue handler ...not found ... 是觸發后, 后面的陳述句繼續執行
exit handler ...not found ... 是出發后, 后面的陳述句不執行
undo handler ... 前面的陳述句撤銷...

從邏輯上, 就用continue handler 來取出資料

-- 堅持用contine取出所有行,并考慮特殊情況
drop procedure if exists curContinue;
delimiter //
create procedure curContinue()
begin
	declare tmp_gid int;
	declare tmp_name varchar(20);
	declare tmp_num int;
	
	declare done boolean default False;
	declare getGoods cursor for select gid, name, num from goods;
	declare continue handler for not found set done := True;
	
	open getGoods;
	-- 先fetch 一行出來
	fetch getGoods into tmp_gid, tmp_name, tmp_num;
	
	repeat
		-- 先取出一條來出來(不論是0,1或多), 再繼續 fetch 
		select tmp_gid, tmp_name, tmp_num;
		fetch getGoods into tmp_gid, tmp_name, tmp_num;
		until done = True
	end repeat;
	-- colse
	close getGoods;
end //
delimiter ;

-- out

mysql> call curContinue();
+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       1 | cat      |     100 |
+---------+----------+---------+
1 row in set (0.14 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       2 | dog      |      72 |
+---------+----------+---------+
1 row in set (0.28 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       3 | pig      |      18 |
+---------+----------+---------+
1 row in set (0.39 sec)

Query OK, 0 rows affected (0.00 sec)

同樣, 換成while回圈也是一樣的.

drop procedure if exists cur_while;
delimiter //
create procedure cur_while()
begin
	-- 宣告臨時變數來存盤fetch每行值和一狀態變數
	declare tmp_gid int;
	declare tmp_name varchar(20);
	declare tmp_num int;
	declare done boolean default True;
	-- 宣告游標對應的查詢集
	declare getGoods cursor for select gid, name, num from goods;
	-- 宣告退出條件的 handler
	declare continue handler for not found set done := False;
	
	open getGoods;
	-- 先取一行
	fetch getGoods into tmp_gid, tmp_name, tmp_num;
	-- while 回圈 來取資料
	while done do
		-- 處理每行的業務邏輯
		select tmp_gid, tmp_name, tmp_num;
		-- 繼續往后fetch
		fetch getGoods into tmp_gid, tmp_name, tmp_num;
	end while;
	close getGoods;
end //
delimiter ;

-- out
mysql> call cur_while();
+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       1 | cat      |     100 |
+---------+----------+---------+
1 row in set (0.12 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       2 | dog      |      72 |
+---------+----------+---------+
1 row in set (0.27 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       3 | pig      |      18 |
+---------+----------+---------+
1 row in set (0.38 sec)

Query OK, 0 rows affected (0.00 sec)

小結游標

理解游標cusror的概念, "移動的游標", 在腦海里要有畫面感, 結合 C 的"指標" 和Python的 "=".
游標的用處在于,可以依次去一行資料, 然后可按業務需求邏輯, 對其為所欲為.
游標的語法: declare, open, fetch, close.
結合回圈代碼實作
- 宣告 fetch 每行資料, 每個值的變數取接收資料
- 宣告一個狀態變數(boolean型), 當游標走到最后時, 此變數改變狀態,并作為退出回圈的依據
- declare getGoods cursor for select xxxx;
- declare continue handler for not found set done := False;
- 先試著取出一行 + 對應該行的業務邏輯處理
- 再進行回圈取資料 + 業務邏輯
- close cursor_name;
語法小細節
- 游標宣告要在 declare變數之后, 在handler之前
- 注意不要忘了結束的 "; " 和關鍵字 for, set , 單詞拼錯, 這些
- 記得最好要close cursor_name; 釋放資源

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/124373.html

標籤：MySQL

上一篇：MySQL復習值代碼知識點(1)

下一篇：【JDBC】CRUD操作