• notice
  • Congratulations on the launch of the Sought Tech site

Definition of mysql cursors and closing analysis

Mysql supports stored procedures and triggers since 5.0.Friends who like to use mysql prefer mysql, the syntax is different from PL/SQL, but anyone who has done programming knows that syntax is not a problem, the key is It is the idea.After a general understanding of the grammar, I have learned in detail from the aspects of variable definition, looping, judgment, cursor, and exception handling.Regarding the usage of cursors, Mysql is still very special.Although it is not as easy to use as PL/SQL, it is still roughly the same in use.

Define the cursor
declare fetchSeqCursor cursor for select seqname, value from sys_sequence;

Use cursor
open fetchSeqCursor;
fetch data
fetch cursor into _seqname, _value;

Close the cursor
close fetchSeqCursor;
But this is all for the cursor operation, it is no different from PL/SQL, but just knowing this is not enough to write For Mysql's fetch process, we need to understand other more in-depth knowledge so that we can really write a good cursor procedure.
First of all, fetch cannot be separated from the loop statement, so let's understand the loop first.
I generally use Loop and while to find it clearer, and the code is simple.

Loop is used as an example here
The code is as follows:

fetchSeqLoop:Loop
fetch cursor into _seqname, _value;
end Loop;

Now it is an infinite loop, and there is no exit condition, so here is a difference from Oracle, Oracle’s PL/SQL pointer has an implicit variable %notfound.Mysql is judged by the declaration of an Error handler,
declare continue handler for Not found (do some action);
When in Mysql When the cursor traversal overflows, a predefined NOT FOUND Error will appear.We can handle this Error and define a continue handler.For the Mysql Error handler, you can query the Mysql manual to define a flag.In NOT FOUND, mark Flag.In the loop, the judgment of ending the loop with this flag can be fine.
The 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 can think will generally think here.If this is the case, how to do nested cursor loops, here can be achieved according to the scope of the statement block, and through begin in Mysql End to divide a statement block, the variable range defined in the block is also in this block, so we can add a begin end to distinguish their corresponding error handlers for nested cursor loops (note that the same error in Mysql The handler can only be defined once.If you define more than one, you will be prompted to duplicate han during the compile process.dler defination, so the NOT FOUND handler can only be defined once), define the NOT FOUND handler of the cursor inside a begin end,
The 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;

This makes it easy to implement more levels of loops, but compared to Oracle's PL/SQL, Mysql does not support dynamic cursors yet Definition, so the very powerful dynamic spelling out of SQL cannot be done in the cursor, but this does not affect my love for Mysql at all.She is like the shy lotus.Although there is no brilliant color, it is simple The color, fresh and elegant without a trace of lead dust, attracts countless mysql fans, just like the infinite green lotus leaf and the red lotus in the sun.

Pay: Mysql also has a dynamic SQL function similar to execute immediate in Oracle.This function can make up for the shortcomings of some dynamic cursors.
set @sqlStr=' select * from table where condition1=?';
prepare s1 for @sqlStr;
execute s1 using @condition1; If there are multiple parameters separated by commas
deallocate prepare s1; release manually, or close the connection When the time, the server automatically recycles.

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+