How to Use the MySQL cursor?-

How to Use the MySQL cursor?-

MySQL supports stored procedures and triggers starting from MySQL 5.0. It gives us the reason that MySQL friends prefer mysql. The syntax
It is different from PL/SQL, but programmers know that syntax is not a problem. The key is thinking.
Variable definition, loop, Judgment, cursor, and exception handling are detailed. Usage of cursors MySQL currently provides
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 understand this.
To write the fetch process of MySQL, we need to understand other more in-depth knowledge so that we can truly write the proc used by the cursor.
Edure

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.
Fetchseqloop: loop
Fetch cursor into _ seqname, _ value;
End loop;
It is an endless loop, and there is no exit condition, so here there is a difference with Oracle, Oracle PL/SQL pointer has a recessive change
% 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 predefined not found error will appear. We will handle this error and define
The handler of a continue can be destroyed. For MySQL error handler, You can query the MySQL manual.
Define a flag. In not found, it indicates the flag. in the loop, the flag can be used as the end loop.
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 make nested games
Mark cyclic commit. Here we can implement commit based on the scope of the statement block, and divide a STATEM using begin end in MySQL.
The variable range defined in the block is also in this block, so we can add one more for nested cursor loops.
Begin end to differentiate their corresponding error handler (note that the handler of the same error in MySQL can only be defined once)
If there are multiple definitions, the duplicate handler defination will be prompted during the compile process, so the not found hand
Ler can only be defined once.) In a begin end, define the not found handler in the cursor,

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, you can easily achieve more levels of loops. However, compared with Oracle's PL/SQL, MySQL currently does not support dynamic game
The definition of the subject, so the powerful dynamic spelling of SQL cannot be done in the cursor, but this does not affect my favorite MySQL
Degrees, she just wanted the shy lotus flower, although there is no brilliant color, but the simple color, fresh and spotless elegance
It attracts countless MySQL fans, just as the lotus leaf is infinite, and the lotus flower is quite red.

Payment: MySQL also has a dynamic SQL function similar to execute immediate in Oracle. How much can it make up?
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.