MySQL cursor syntax instance

MySQL cursor syntax Example 1. Basic Syntax: SQL code # define the cursor declare fetchSeqCursor cursor for select seqname, value from sys_sequence; # Use the cursor open fetchSeqCursor; # fetch Data fetch cursor into _ seqname, _ value; # close the cursor close fetchSeqCursor; www.2cto.com 2. Single-cursor example: SQL code # 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. nested cursor loop Java code # 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 lifecycle = false; keep open Loop; fetchSeqLoop: Loop if fetchSeqOk then leave fetchSeqLoop; else fetch cursor into _ seqname, _ value; # nested cursor loop 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 of nested cursor Loop if; end Loop; close fetchSeqCursor;