MySQL游标语法实例_MySQL

MySQL游标语法实例

1、基本语法：

Sql代码

#定义游标

declare fetchSeqCursor cursor for select seqname, value from sys_sequence;

#使用游标

open fetchSeqCursor；

#fetch数据

fetch cursor into _seqname, _value;

#关闭游标

close fetchSeqCursor;

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;

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;

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;

end loop;

close cursor2;

end;

#嵌套的游标循环结束

end if;

end Loop;

close fetchSeqCursor;