一、游標的定義：

成都創(chuàng)新互聯(lián)公司是工信部頒發(fā)資質(zhì)IDC服務(wù)器商，為用戶提供優(yōu)質(zhì)的成都IDC機房托管服務(wù)

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;//關(guān)閉游標myCursor

end;

二、游標+repeat循環(huán)-->實現(xiàn)遍歷行：
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實現(xiàn)遍歷行：

continue handler 當fetch觸發(fā)此handler后，后面的語句繼續(xù)執(zhí)行。
所以會多執(zhí)行一次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實現(xiàn)遍歷行：

exit handler 當fetch觸發(fā)此handler后，觸發(fā)后后面的語句不再執(zhí)行。
所以select row_gid,row_name,row_num;不會再被執(zhí)行。
此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實現(xiàn)遍歷行：

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實現(xiàn)遍歷行：

-- loop 與 leave,iterate 實現(xiàn)循環(huán)
-- loop 標志位無條件循環(huán)；leave 類似于Java break 語句，跳出循環(huán)，即跳出 begin end；
iterate 類似于java continue ，結(jié)束本次循環(huán)，繼續(xù)下一次循環(huán)。
--loop的優(yōu)點在于可以根據(jù)條件結(jié)束本次循環(huán)或者根據(jù)條件跳出循環(huán)。

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;