drop procedure p12$ //删除存储过程
//游标cursor,一条sql对应n条资源，取出资源的接口/句柄就是cursor, 一条sql产生的n条结果不是一次性全部输出，而是返回一个地址，然后一个个的筛选。
//声明游标（declare），打开游标(open)，打开游标就去数据库查询资源了，查到了并不是把所有的结果行返回给你，而是给你一个口，然后就可以去fetch(取值)，最后close游标。
mysql> delimiter $
create procedure p13()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20); //声明3个变量，存储fetch得到的每一行的3个列的值
declare getgoods cursor //声明游标，此时的游标是一个地址，如果查询为空则getgoods就是null,
for select gid,num,name from goods; //游标作用的语句
open getgoods; //打开游标
fetch getgoods //fetch一次就得到一行
into row_gid,row_num,row_name; //一一对应赋值
select row_num,row_name; //显示出来看
close getgoods; //关闭游标
end$
mysql> call p13()$
+---------+----------+
| row_num | row_name |
+---------+----------+
| 222 | 猫 |
+---------+----------+
mysql> delimiter $
create procedure p14()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare getgoods cursor for select gid,num,name from goods;
open getgoods;
fetch getgoods into row_gid,row_num,row_name; //相同的语句每取一次，往后移动一次，
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
close getgoods;
end$
mysql> call p14()$
+---------+----------+
| row_num | row_name |
+---------+----------+
| 222 | 猫 |
+---------+----------+
1 row in set
+---------+----------+
| row_num | row_name |
+---------+----------+
| 333 | 马 |
+---------+----------+
1 row in set
+---------+----------+
| row_num | row_name |
+---------+----------+
| 555 | 猪 |
+---------+----------+
1 row in set
delimiter $
create procedure p16()
begin
declare totalcunt int default 0;//总行数
declare i int default 0;
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare getgoods cursor for select gid,num,name from goods;
//declare要放在一起写完，再写select语句
select count(*) into totalcunt from goods;
open getgoods;
repeat //可以在repeat中对每一行做处理
set i:=i+1;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
until i >= totalcunt end repeat;
close getgoods;
end$
mysql> call p16()$
+---------+----------+
| row_num | row_name |
+---------+----------+
| 222 | 猫 |
+---------+----------+
1 row in set
+---------+----------+
| row_num | row_name |
+---------+----------+
| 333 | 马 |
+---------+----------+
1 row in set
//游标越界时标志，在mysql cursor 中可以declare continue handler来操作是否越界，declare continue handler for NOT FOUND set ....声明continue handler专门监控没有数据事件时执行语句，
delimiter $
create procedure p17()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare outrange int default 1;//越界标志，为0时就没有了
declare getgoods cursor for select gid,num,name from goods; //声明游标,返回的是select的结果集的首地址，
declare continue handler for NOT FOUND set outrange:=0;//声明句柄，没有数据事件发生时，执行设置outrange为0，
open getgoods;
repeat //可以在repeat中对每一行做处理
fetch getgoods into row_gid,row_num,row_name; //取一行
select row_num,row_name; //输出fetch到的哪一行
until outrange= 0 end repeat;
close getgoods;
end$
mysql> call p17()$ //多取了一行，
+---------+----------+
| row_num | row_name |
+---------+----------+
| 222 | 猫 |
+---------+----------+
1 row in set
+---------+----------+
| row_num | row_name |
+---------+----------+
| 666 | uuu |
+---------+----------+
1 row in set
+---------+----------+
| row_num | row_name |
+---------+----------+
| 666 | uuu |
+---------+----------+
1 row in set
repeat
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
until outrange= 0 end repeat;
//最后一个fetch--->没数据--->outrange=0--->因为声明的是一个continue handle,继续执行后面的sql语句，select row_num,row_name; --->因此最后一行取出2次
//如果NOT FOUND事件发生后，select不再执行就好了。声明handler不是continue而是exit就好了。
declare exit handler for NOT FOUND set outrange:=0; //就好了
//exit与continue的区别：exit触发后后面的语句不在执行。除了continue和exit还有undo handler，
//continue是触发后后面的语句继续执行
//exit是出发后后面的语句不执行
//undo 是触发后前面的语句撤销，但是mysql不支持。
//就用continue同时还不取出最后一行，游标终极版本。
delimiter $
create procedure p20()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare outrange int default 1;
declare getgoods cursor for select gid,num,name from goods;
declare continue handler for NOT FOUND set outrange:=0;
open getgoods;
fetch getgoods into row_gid,row_num,row_name; //先取出一行，防止第一行就是空的
repeat
select row_num,row_name; //输出fetch到的那行，
fetch getgoods into row_gid,row_num,row_name; //fetch最后一行的下一行的时候，NOT FOUND事件触发，继续执行的是until outrange= 0 不会多取出最后一行，
until outrange= 0
end repeat;
close getgoods;
end$
//游标终极版本。
delimiter $
create procedure p23()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare outrange int default 1;
declare getgoods cursor for select gid,num,name from goods;
declare continue handler for NOT FOUND set outrange:=0;
open getgoods;
fetch getgoods into row_gid,row_num,row_name;
while outrange=1 do //repeat相当于do while,
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;
end while;
close getgoods;
end$
