mysql基础五游标
一、游标的定义:
成都创新互联公司是工信部颁发资质IDC服务器商,为用户提供优质的成都IDC机房托管服务
create procedure p12()
begin
declare row_name varchar(20);
declare row_num int;
declare myCursor cursor for select name,num from goods;//定义游标myCursor
open myCursor;//打开游标myCursor
fetch 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;//关闭游标myCursor
end;
二、游标+repeat循环-->实现遍历行:
create procedure p13()
begin
declare 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()
begin
declare 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;
repeat
fetch 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()
begin
declare 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;
repeat
fetch 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()
begin
declare 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 do
select 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()
begin
declare 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:loop
fetch myCursor into row_gid,row_name,row_num;
if over then
leave cursor_loop;
end if;
select row_gid,row_name,row_num;
end loop cursor_loop;
close myCursor;
end;
名称栏目:mysql基础五游标
文章链接:http://scyanting.com/article/pejjij.html