3、游标要和select语句配合使用 declare c cursor for select * from user 这句话就表示游标c指向select * from user查询结果集的第一条记录(该记录还未从数据库中取出,这儿可以写逻辑决定要不要返回该记录给调用程序,每取一次,游标就向后移动一位)
4、定义游标并循环游标(推荐使用repeat循环)
XML Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
create procedure use_cursor() begin declare row_id int default 0; declare row_name varchar(20) default ''; declare flag int default 0;
declare c cursor for select id,name from t1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET FLAG=1; open c; repeat fetch c into row_id,row_name; select concat('id=',row_id,' name=',row_name); until flag end repeat; close c; end$$
create procedure use_e_handler() begin declare row_id int default 0; declare row_name varchar(20) default ''; declare flag int default 0;
declare c cursor for select id,name from t1; DECLARE exit HANDLER FOR NOT FOUND SET FLAG=1; open c; repeat fetch c into row_id,row_name; until flag end repeat; close c; select 'continue over...'; end$$
6.2手动检测flag标志的值,如果发现触发了handler就手动离开循环
XML Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
create procedure use_cursor_e() begin declare row_id int default 0; declare row_name varchar(20) default ''; declare flag int default 0; declare c cursor for select id,name from t1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET FLAG=1; open c; r:repeat fetch c into row_id,row_name; if flag then leave r; end if; select concat('id=',row_id,' name=',row_name); until flag end repeat r; close c; end$$
7,存储过程之间的通信(唯一的方法是创建临时表)
--创建一个创建临时表的存储过程
XML Code
1 2 3 4
create procedure p_producer() begin create temporary table t2 as select * from t1; end$$
--创建一个用于消费前一个存储过程创建的表的存储过程
XML Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
create procedure p_consumer() begin declare flag int default 0; declare row_id int default 0; declare row_name varchar(10) default ''; declare c cursor for select id,name from t2; declare continue handler for not found set flag=1; open c; r:repeat fetch c into row_id,row_name; if flag then leave r; end if; select row_id,row_name; until flag end repeat r; end$$