MySQL游標(biāo)語法的用法

157次閱讀

共計(jì) 1635 個(gè)字符，預(yù)計(jì)需要花費(fèi) 5 分鐘才能閱讀完成。

本篇內(nèi)容主要講解“MySQL 游標(biāo)語法的用法”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓丸趣 TV 小編來帶大家學(xué)習(xí)“MySQL 游標(biāo)語法的用法”吧!

1、基本語法：
Sql 代碼
#定義游標(biāo)
declare fetchSeqCursor cursor for select seqname, value from sys_sequence;
#使用游標(biāo)
open fetchSeqCursor；
#fetch 數(shù)據(jù)
fetch cursor into _seqname, _value;
#關(guān)閉游標(biāo)
close fetchSeqCursor;
2、單游標(biāo)例子：
Sql 代碼
## define the flag for loop judgement
declare fetchSeqOk boolean;
## define the varient for store the data
declare _seqname varchar(50);
declare _value bigint(20);
## define the cursor
declare fetchSeqCursor cursor for select seqname, value from sys_sequence;
## define the continue handler for not found flag
declare continue handler for NOT FOUND set fetchSeqOk = true;
set fetchSeqOk = false;
open fetchSeqCursor;
fetchSeqLoop:Loop
if fetchSeqOk then
leave fetchSeqLoop;
else
fetch cursor into _seqname, _value;
select _seqname, _value;
end if;
end Loop;
close fetchSeqCursor;
3、嵌套的游標(biāo)循環(huán)
Java 代碼
## define the flag for loop judgement
declare fetchSeqOk boolean;
## define the varient for store the data
declare _seqname varchar(50);
declare _value bigint(20);
## define the cursor
declare fetchSeqCursor cursor for select seqname, value from sys_sequence;
## define the continue handler for not found flag
declare continue handler for NOT FOUND set fetchSeqOk = true;
set fetchSeqOk = false;
open fetchSeqCursor;
fetchSeqLoop:Loop
if fetchSeqOk then
leave fetchSeqLoop;
else
fetch cursor into _seqname, _value;
#嵌套的游標(biāo)循環(huán)
begin
declare fetchSeqOk boolean default inner
## define the cursor
declare cursor2 cursor for select …. from …;
## define the continue handler for not
declare continue handler for NOT FOUND set fetchSeqOk = true;
set fetchSeqOk = false;
open cursor2;
fetchloop2 loop
if fetchSeqOk then
else
end if;
end loop;
close cursor2;
end;
#嵌套的游標(biāo)循環(huán)結(jié)束
end if;
end Loop;
close fetchSeqCursor;