MySQL游标语法实例

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;  
  
end loop;  
close cursor2;  
end;  
#嵌套的游标循环结束  
  
end if;  
  
end Loop;  
close fetchSeqCursor;