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 ;