MySQL from 5.0 to support the stored procedures and trigger, we like to use MySQL friends more like the reason for MySQL, grammatical and pl/sql differences, but have been programmed to know that the grammar is not a problem, the key is the idea, the general understanding of the grammar, the definition from the variable, cycle , Judgment, cursor, exception handling this several aspects of the detailed study. About the use of cursors MySQL now provides a special, although not pl/sql so smooth, but the use is generally the same,
Defining Cursors
Declare fetchseqcursor cursor FOR select Seqname, value from Sys_sequence;
using Cursors
Open fetchseqcursor;
Fetch data
Fetch cursor into _seqname, _value;
Close Cursor
Close fetchseqcursor;
But this is all for cursor operation, and Pl/sql is no different, but just understand this is not enough to write the MySQL fetch process, but also to understand the other in-depth knowledge, we can really write good cursor use of the procedure
First of all, the fetch can not be separated from the circular statement, then first understand the Loop bar.
I generally use loop and while to feel more clear, and the code is simple.
use loop here for example
Copy Code code as follows:
Fetchseqloop:loop
Fetch cursor into _seqname, _value;
End Loop;
Now is the dead loop, there is no exit condition, then there is a difference with Oracle, Oracle's PL/SQL pointer has a recessive variable%notfound,mysql is judged by an error handler statement,
Declare continue handler for don't found (do some action);
In MySQL when the cursor traversal overflows, there will be a predefined not found error, we handle this error and define a continue handler can le, about MySQL Error Handler can query the MySQL manual to define a flag, in not FOUND, Mark Flag, in the loop with this flag as the end of the cycle of the judge can le.
Copy Code code 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 le, then will think of people generally here will think, if so, how to do nested cursor loop LE, here can be based on the scope of statement block to achieve LE, MySQL through the begin end to divide a statement block, the range of variables defined in the blocks is also in this box, so for nested cursor loops we can add a begin end to differentiate their corresponding error handler ( Note that the same error in MySQL handler can only be defined once, multiple definitions, in the process of compile will be prompted duplicate handler defination, so not found handler can only be defined once), To define the not FOUND handler of the inside cursor in a BEGIN end,
Copy Code code 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 ...; # # 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;
This makes it easy to implement more levels of loops, but MySQL does not yet support the definition of dynamic cursors for Oracle's Pl/sql, so it's very powerful to dynamically spell out the SQL in the cursor, but that doesn't really affect how much I love MySQL, She would like that shy Lotus, although there is no brilliant color, but the simple tones, fresh and not dyed a trace of the elegance of lead dust, as attracted countless MySQL fans, as the day after the Lotus leaves infinite Bi, Ying-day lotus different red.
Pay: MySQL also has the function of dynamic SQL similar to execute immediate in Oracle, how much can make up for some dynamic cursors by this function le
Set @sqlStr = ' select * FROM table where condition1 =? ';
Prepare S1 for @sqlStr;
Execute S1 using @condition1; If you have multiple arguments separated by commas
deallocate prepare S1; Manually released, or when connection is closed, the server automatically reclaims.
