MySQL游标语法实例
- WBOY原創
- 2016-06-07 15:14:031418瀏覽
欢迎进入Linux社区论坛，与200万技术人员互动交流 >>进入 1、基本语法： Sql代码 #定义游标 declare fetchSeqCursor cursor for select seqname, value from sys_sequence; #使用游标 open fetchSeqCursor； #fetch数据 fetch cursor into _seqname, _value;
欢迎进入Linux社区论坛，与200万技术人员互动交流 >>进入
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;
