欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL游标语法实例_MySQL

程序员文章站 2022-05-15 15:06:43
...
bitsCN.com


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;

bitsCN.com
相关标签: 游标