create or replace procedure proc1()
begin
declare done int default false; /* 用于判断退出循环 */
declare x,y int; /* 用于保存fetch结果 */
declare cur1 cursor for select i from t1; /* fetch t1的游标 */
declare cur2 cursor for select i from t2; /* fetch t2的游标 */
declare continue handler for not found set done=true; /* not found时，退出循环 */
open cur1;
open cur2;
my_loop: LOOP
fetch cur1 into x;
fetch cur2 into y;
if done then
leave my_loop;
end if;
if x <= y then
insert into t3 values(y);
else
insert into t3 values(x);
end if;
end loop;
close cur1;
close cur2;
end$$
delimiter ;
call proc1;
查看表t3：
select * from t3;
+------+
| i |
+------+
| 15 |
| 30 |
| 20 |
+------+
下面是MariaDB 10.3上使用游标的一个示例：将表t1中i字段某一段数据插入到表t2中。
create or replace table t1(i int);
create or replace table t2(i int);
insert into t1 values(5),(10),(20),(30),(40);
delimiter $$
create or replace procedure proc1(min int,max int)
begin
declare done int default false;
declare x int;
declare cur1 cursor(cmin int,cmax int) for select i from t1 where t1.i between cmin and cmax;
declare continue handler for not found set done=true;
open cur1(min,max);
my_loop: LOOP
fetch cur1 into x;
if done then
leave my_loop;
end if;
insert into t2 values(x);
end loop;
close cur1;
end$$
delimiter ;
call proc1(10,40);
查看t2结果：
MariaDB [test]> select * from t2;
+------+
| i |
+------+
| 10 |
| 20 |
| 30 |
| 40 |
+------+