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$