mysql基础五 游标

一、游标的定义：create procedure p12()
begindeclare row_name varchar(20);
declare row_num int;declare myCursor cursor for select name,num from goods;//定义游标myCursoropen myCursor;//打开游标myCursorfetch myCursor into row_name,row_num;//使用游标myCursor获取第一行select row_name, row_num;fetch myCursor into row_name,row_num;//使用游标myCursor获取第二行；每fetch一次游标就自动往下游一次.select row_name, row_num;close myCursor;//关闭游标myCursorend;二、游标+repeat循环–>实现遍历行：
create procedure p13()
begindeclare row_gid int;
declare row_name varchar(20);
declare row_num int;declare row_count int;
declare i int default 0;declare myCursor cursor for select gid,name,num from goods;select count(1) into row_count from goods;open myCursor;repeat fetch myCursor into row_gid,row_name,row_num;select row_gid,row_name,row_num;set i=i+1;until i>row_count end repeat;close myCursor;end;三、游标+continue handler实现遍历行：continue handler 当fetch触发此handler后，后面的语句继续执行。
所以会多执行一次select row_gid,row_name,row_num;
此handler常用。create procedure p15()begindeclare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare you int default 1;declare myCursor cursor for select gid,name,num from goods;declare continue handler for NOT FOUND set you=0;open myCursor;repeatfetch myCursor into row_gid,row_name,row_num;select row_gid,row_name,row_num;until you=0 end repeat;close myCursor;end;四、游标+exit handler实现遍历行：exit handler 当fetch触发此handler后，触发后后面的语句不再执行。
所以select row_gid,row_name,row_num;不会再被执行。
此handler不常用。create procedure p16()begindeclare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare you int default 1;declare myCursor cursor for select gid,name,num from goods;declare开发云主机域名 exit handler for NOT FOUND set you=0;open myCursor;repeatfetch myCursor into row_gid,row_name,row_num;select row_gid,row_name,row_num;until you=0 end repeat;close myCursor;end;五、游标+while实现遍历行：create procedure p15()begindeclare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare over int default 1;declare myCursor cursor for select gid,name,num from goods;declare continue handler for NOT FOUND set over =0;open myCursor;fetch myCursor into row_gid,row_name,row_num;while over doselect row_gid,row_name,row_num;fetch myCursor into row_gid,row_name,row_num;end while;close myCursor;end;六、游标+loop实现遍历行：— loop 与 leave,iterate 实现循环
— loop 标志位无条件循环；leave 类似于Java break 语句，跳出循环，即跳出 begin end；
iterate 类似于java continue ，结束本次循环，继续下一次循环。
—loop的优点在于可以根据条件结束本次循环或者根据条件跳出循环。create procedure p17()begindeclare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare over int default 0;declare myCursor cursor for select gid,name,num from goods;declare continue handler for NOT FOUND set over=1;open myCursor;cursor_loop:loopfetch myCursor into row_gid,row_name,row_num;if over thenleave cursor_loop;end if;select row_gid,row_name,row_num;end loop cursor_loop;close myCursor;end;

相关推荐: 【Mysql】MySQL查询计划key_len全知道

本文首先介绍了MySQL的查询计划中ken_len的含义；然后介绍了key_len的计算方法；最后通过一个伪造的例子,来说明如何通过key_len来查看联合索引有多少列被使用开发云主机域名。 在MySQL中,可以通过explain查看SQL语句所走的路径,如下…