MySQL游標語法實例
1、基本語法：
Sql代碼
#定義游標
declare fetchSeqCursor cursor for select seqname, value from sys_sequence;
#使用游標
open fetchSeqCursor；
#fetch數據
fetch cursor into _seqname, _value;
#關閉游標
close fetchSeqCursor;
www.2cto.com
2、單游標例子：
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;
www.2cto.com
3、嵌套的游標循環
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;
www.2cto.com
open fetchSeqCursor;
fetchSeqLoop:Loop
if fetchSeqOk then
leave fetchSeqLoop;
else
fetch cursor into _seqname, _value;
#嵌套的游標循環
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; www.2cto.com
end loop;
close cursor2;
end;
#嵌套的游標循環結束
end if;
end Loop;
close fetchSeqCursor;
