In-depth analysis on usage and closure of Mysql cursors

Mysql has been supporting stored procedures and triggers since mysql 5.0, giving us the reason that mysql friends prefer Mysql. The syntax is different from PL/SQL, but anyone who has programming knows that, syntax is not a problem. The key is thinking. After getting a general understanding of the syntax, we will learn more about variable definition, loop, Judgment, cursor, and exception handling. The usage of cursors Mysql currently provides very special features. Although it is not as easy to use as PL/SQL, it is the same in general,

Define a cursor
Declare fetchSeqCursor cursor for select seqname, value from sys_sequence;

Use cursor
Open fetchSeqCursor;
Fetch data
Fetch cursor into _ seqname, _ value;

Close cursor
Close fetchSeqCursor;
However, this is all about cursor operations. It is no different from PL/SQL, but it is not enough to write the fetch process of Mysql, we also need to know other more in-depth knowledge, so that we can truly write the procedure used by the cursor.
First, fetch cannot be separated from loop statements, so let's take a look at the loop first.
I usually think Loop and while are clear and the code is simple.

Loop is used here as an example. Copy codeThe Code is as follows: fetchSeqLoop: Loop
Fetch cursor into _ seqname, _ value;
End Loop;

It is an endless loop and there is no exit condition, so it is different from oracle here. Oracle's PL/SQL pointer has a recessive variable % notfound, mysql is judged by an Error handler statement,
Declare continue handler for Not found (do some action );
In Mysql, when the cursor traversal overflows, a pre-defined not found Error will appear. We can handle this Error and define a continue handler, for Mysql Error handler, You can query the Mysql manual to define a flag. In not found, Flag is indicated. in the Loop, the flag can be used as the end Loop.Copy codeThe Code is as follows: declare fetchSeqOk boolean; # define the flag for loop judgement
Declare _ seqname varchar (50); ## define the varient for store the data
Declare _ value bigint (20 );
Declare fetchSeqCursor cursor for select seqname, value from sys_sequence; # define the cursor
Declare continue handler for not found set fetchSeqOk = true; # define the continue handler for not
Found flag
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;

This is a complete process, so people who will think about it will generally think about it here. If so, how to implement nested cursor loop failover, here, we can implement commit based on the scope of the statement block. Mysql divides a statement block through begin end, and the variable range defined in the block is also in this block, therefore, for nested cursor loops, we can add a begin end to differentiate their corresponding error handler (note that in Mysql, the handler of the same error can only be defined once. If there are multiple definitions, in the compile process, duplicate handler defination will be prompted, so the not found handler can only be defined once). In a begin end, define the not found handler in the cursor,Copy codeThe Code is as follows: declare fetchSeqOk boolean; # define the flag for loop judgement
Declare _ seqname varchar (50); ## define the varient for store the data
Declare _ value bigint (20 );
Declare fetchSeqCursor cursor for select seqname, value from sys_sequence; # define the cursor
Declare continue handler for not found set fetchSeqOk = true; # define the continue handler for not
Found flag
Set fetchSeqOk = false;
Open fetchSeqCursor;
FetchSeqLoop: Loop
If fetchSeqOk then
Leave fetchSeqLoop;
Else
Fetch cursor into _ seqname, _ value;
Begin
Declare fetchSeqOk boolean default 'inner ';
Declare cursor2 cursor for select... from ......; # define the cursor
Declare continue handler for not found set fetchSeqOk = true; # define the continue handler for n
Ot
Set fetchSeqOk = false;
Open cursor2;
Fetchloop2 loop
If fetchSeqOk then
Else
End if;
End loop;
Close cursor2;
End;
End if;
End Loop;
Close fetchSeqCursor;

In this way, more levels of loops can be easily implemented. However, compared with oracle's PL/SQL, Mysql does not support dynamic cursor definition yet, so the powerful dynamic spelling of SQL cannot be done in the cursor, but this does not affect my liking for Mysql at all. She just wants to be shy like the lotus flower, although there is no brilliant color, the simple color, fresh and spotless elegance, attracts countless mysql fans, just as the lotus leaf is infinite, the lotus flower is quite red.

Pay: Mysql also has a dynamic SQL function similar to execute immediate in Oracle. This function can be used to make up for some shortcomings of dynamic cursors.
Set @ sqlStr = 'select * from table where condition1 =? ';
Prepare s1 for @ sqlStr;
Execute s1 using @ condition1; if multiple parameters are separated by commas (,)
Deallocate prepare s1; manually released, or when connection is disabled, the server automatically recycles the instance.