MySQL Midstream use case description (study notes)

1. What is a cursor?
Simply put: The cursor is the indicator of the swimming, what does it mean, in layman's words, a SQL takes out an interface/handle to a resource corresponding to N, which is a cursor that can fetch one row at a time along the cursor. Let me prepare a picture for you:

2. How do I use cursors?
1. declaring/Defining a cursor
DECLARE declaration; DECLARE CURSOR name cursor for select_statement;
2. Open a cursor
Open cursor Name
3. Take the value
Fetch fetch value; FETCH cursor name into var1,var2[,...]
4. Close a cursor
Close closes; close cursor name;

3. Cursor Combat
Cursor not used:
CREATE PROCEDURE P1 ()
Begin
SELECT * from category;
end$

Call p1$
Execution Result:

Using cursors:
/**
Comments
*/
CREATE PROCEDURE P2 ()
Begin
Define three variables to place the value of the fetch value into the corresponding variable
declare row_cat_id int;
declare row_cat_name int;
declare row_parent_id int;
Defining cursors
Declare getcategory cursor for select cat_id,cat_name,parent_id from category;
Open cursor
Open getcategory;
Take value
Fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
Close Cursors
Close getcategory;
end$
/**
Not commented
*/
CREATE PROCEDURE P2 ()
Begin
declare row_cat_id int;
DECLARE row_cat_name varchar (90);
declare row_parent_id int;
Declare getcategory cursor for select cat_id,cat_name,parent_id from category;
Open getcategory;
Fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
Close getcategory;
end$
When you execute it you will find that it is 0 rows, because we assign the result of the query to the variable, we have no query display for the variable after the assignment. So it's 0 lines. Therefore, we have to re-improve.
Call P2 () $
The result of the execution is:

Improved
Remove cursor re-execution
drop procedure p2$
CREATE PROCEDURE P2 ()
Begin
declare row_cat_id int;
DECLARE row_cat_name varchar (90);
declare row_parent_id int;
Declare getcategory cursor for select cat_id,cat_name,parent_id from category;
Open getcategory;
Fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
Select row_cat_id,row_cat_name,row_parent_id;
Close getcategory;
end$
Call P2 () $
The results of the implementation are as follows:

At this point you will find that we only get a query result, then why? At this time because of control in our here, I would like to take a line on a line, willing to take two lines on two lines. So, I'm just changing the motion.
CREATE PROCEDURE P3 ()
Begin
declare row_cat_id int;
DECLARE row_cat_name varchar (90);
declare row_parent_id int;
Declare getcategory cursor for select cat_id,cat_name,parent_id from category;
Open getcategory;
Fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
Select row_cat_id,row_cat_name,row_parent_id;

Fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
Select row_cat_id,row_cat_name,row_parent_id;

Fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
Select row_cat_id,row_cat_name,row_parent_id;

Fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
Select row_cat_id,row_cat_name,row_parent_id;

Fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
Select row_cat_id,row_cat_name,row_parent_id;

Fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
Select row_cat_id,row_cat_name,row_parent_id;
Close getcategory;
end$
I fetch six times, query five times, what will we get? Try it!
Call P4 () $
The results of the implementation are as follows:

Hint: Did you find anything? The same statement, we swam back once every time, several times to swim, until you have to swim all the signs, the system will report "02000" This error, tell us that the cursor has gone. We swam here six times, so we will print the first six records.
So, how do we loop cursors out all the lines?
Ideas:
1. Calculate All Rows Select count (*)
CREATE PROCEDURE P4 ()
Begin
declare row_cat_id int;
DECLARE row_cat_name varchar (90);
declare row_parent_id int;
DECLARE CNT int default 0;//define Total rows
declare i int default 0;
Declare getcategory cursor for select cat_id,cat_name,parent_id from category;
Select COUNT (*) into CNT from category;//calculates the total number of rows after the query is assigned to the CNT variable
Open getcategory;
Repeat
Set i:=i+1;
Fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
Select row_cat_id,row_cat_name,row_parent_id;
Until i>=cnt end repeat;
Close getcategory;
end$
Call P4 () $
The result of the execution is:

It can be seen that the results of the table, again emphasizing the meaning of the cursor here is that it takes the right to take out each line to you, you may be in every repeat out of this line to make other judgments.
2. Define an out-of-bounds identifier for the cursor
In a MySQL cursor, you can define continue handler to manipulate an out-of-bounds identity, using syntax: Declare continue handler for not FOUND Statemet (statements to execute when no data is available)

This sentence means that I want to declare a handle event, you take it back, and once the Not FOUND event occurs, set ergodic:=0 this statement
CREATE PROCEDURE P5 ()
Begin
declare row_cat_id int;
DECLARE row_cat_name varchar (90);
declare row_parent_id int;
DECLARE ergodic int default 1;//declares a variable indicating that there is data to traverse
Declare getcategory cursor for select cat_id,cat_name,parent_id from category;
Declare continue handler for not FOUND set ergodic:=0;
Open getcategory;
Repeat
Fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
Select row_cat_id,row_cat_name,row_parent_id;
Until ergodic=0 end repeat;
Close getcategory;
end$
Call P5 () $
The result of the execution is:

Did you find the problem? Why was the last one checked two times? What is the reason for this? We might as well analyze the statements we wrote:

Since the problem has been analyzed, how do we deal with this problem?
Solution: Declare that the Hanlder is no longer continue, but exit to achieve the goal. That is: Declare exit handler for not FOUND set ergodic:=0;
The difference between exit and continue is that after the exit is triggered, the subsequent statement is no longer executed, and continue still needs to be executed.
Note: In addition to this exit and continue two ways, there is also a way to undo handler.
When triggered by the undo handler method, the preceding statement is revoked directly. "But at the moment it seems this way, MySQL is not yet supported"
CREATE PROCEDURE P6 ()
Begin
declare row_cat_id int;
DECLARE row_cat_name varchar (90);
declare row_parent_id int;
DECLARE ergodic int default 1;
Declare getcategory cursor for select cat_id,cat_name,parent_id from category;
Declare exit handler for not FOUND set ergodic:=0;
Open getcategory;
Repeat
Fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
Select row_cat_id,row_cat_name,row_parent_id;
Until ergodic=0 end repeat;
Close getcategory;
end$
Call P6 () $
The result of the execution is:

This solves the problem.
Digression: What should we do if we still use continue to achieve non-repetition? At this point we can deal with this problem in our code logic, we first analyze the code:
Hint:
Have you ever considered that there is no data (value is empty) when you first fetch the value, so we can manually fetch a line out first, Immediately following the repeat data.
CREATE PROCEDURE P7 ()
begin
Declare row_cat_id int;
DECLARE row_cat_name varchar (90);
Declare row_parent_id int;
Declare ergodic int default 1;
Declare getcategory cursor for select cat_id,cat_name,parent_id from category;
Declare continue handler for not FOUND set ergodic:=0;
Open getcategory;
fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
Repeat
Select row_cat_id,row_cat_name,row_parent_id;
Fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
until ergodic=0 end repeat;
Close getcategory;
end$
Call P7 () $
Execution result is:

Attachment:
Test database and Data tables:
CREATE TABLE category (
cat_id smallint unsigned auto_increment primary key,
Cat_name varchar (+) NOT null default ' ',
parent_id smallint unsigned
) engine MyISAM charset UTF8;

INSERT into ' category ' VALUES
(1, ' phone type ', 0),
(2, ' CDMA phones ', 1),
(3, ' GSM phone ', 1),
(4, ' 3G phone ', 1),
(5, ' dual-mode phone ', 1),
(6, ' mobile phone accessories ', 0),
(7, ' Charger ', 6),
(8, ' headphones ', 6),
(9, ' battery ', 6),
(11, ' Reader and Memory card ', 6),
(12, ' recharge card ', 0),
(13, ' PHS/Landline Prepaid card ', 12),
(14, ' mobile phone recharge card ', 12),
(15, ' Unicom Mobile prepaid card ', 12);

MySQL Midstream use case description (study notes)