883
mysql 遊標
遊標 遊標用於多行處理, 打開內存塊, 並在內存中進行操作調用前必須先聲明, 並且經曆四個過程
聲明 <- 一個函數, 存儲可以同時定義多個遊標
打開
FETCH 傳遞
關閉
example:
DECLARE c1 CURSOR FOR SELECT id,data FROM new.t1; <- select 不能夠帶 into
open c1; <- 調用前必須先打開
FETCH c1 INTO a; <- 指定的打開光標讀取下一行
close c1; <- 關閉
注意: mysql 沒有數組功能, 無法進行每行推送保存
- 前提
- create table a1 ( id int, name varchar(10) );
delimiter //
create procedure i_a1 ( in b int )
begin
declare a int;
set a=1;
while a <= b
do
insert into a1 values ( a, concat(a,'x'));
set a=a+1;
end while;
end;
//
delimiter ;
create table a2 ( id int , name varchar(10) ); - ex1
- a1 表具有 15 行數據
目標, 傳輸 a1 表數據到 a2 表
delimiter //
create procedure test23 ( in a int )
begin
declare b int;
declare c varchar(10);
declare d int;
declare c1 cursor for select * from new.a1;
open c1;
set d=1;
repeat
fetch c1 into b,c;
insert into a2 values (b,c);
set b=b+1;
until a < b
end repeat;
close c1;
end;
//
delimiter ; - ex2
- 如 ex1, 假如 a1 表不存在數據, 則報錯
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
ERROR 1326 (24000): Cursor is not open <- 沒有打開遊標錯誤 ->
declare xx handler -- 必須在 cursor 後定義, 不能夠有 ; --
delimiter //
create procedure test24 ( in a int )
begin
declare b int;
declare c varchar(10);
declare d int;
declare c1 cursor for select * from new.a1;
declare exit handler for not found
begin end;
declare exit handler for 1326
begin end;
open c1;
set d=1;
repeat
fetch c1 into b,c;
insert into a2 values (b,c);
set b=b+1;
until a < b
end repeat;
close c1;
end;
//
delimiter ; - ex3
- 為 a1 增加列 addr varchar(25)
下麵遊標用於更新 name, addr 列
-- 注意: 5.0 中遊標具有隻讀性, 無法自行更新, 5.1.26 測試能夠更新遊標所在行 --
delimiter //
create procedure test25 ( in a int )
begin
declare b int;
declare c varchar(10);
declare c1 cursor for select name from new.a1;
open c1;
set b=1;
repeat
fetch c1 into c;
update a1 set name=concat('u',c) where id=b;
update a1 set addr=concat(a,c) where id=b;
set b=b+1;
until a < b
end repeat;
close c1;
end;
//
delimiter ;
最後更新：2017-04-02 22:16:23