Detailed explanation of the principle and usage of cursor (DECLARE) in MySQL stored procedure

This article uses examples to illustrate the principles and usage of cursors (DECLARE) in MySQL stored procedures. Share with you for your reference, the details are as follows:

We can use cursors when processing result sets in stored procedures because cursors allow us to iterate over a set of rows returned by a query and process each row accordingly. MySQL cursor has three modes: read-only, non-scrollable and sensitive. Let's take a look:

• Read-only : Data in the underlying table cannot be updated through the cursor.
• Non-scrollable : rows can only be retrieved in the order determined by the select statement. It is not possible to fetch rows in reverse order. Additionally, you cannot skip rows or jump to a specific row in the result set.
• Sensitive : There are two types of cursors: sensitive cursors and insensitive cursors. Sensitive cursors point to the actual data, and insensitive cursors use a temporary copy of the data. A sensitive cursor executes faster than an insensitive cursor because it does not require temporary copying of data. However, any changes made to other connections' data will affect data used by sensitive cursors, so it is safer if you do not update data used by sensitive cursors. MySQL cursors are sensitive.

We can use MySQL cursors in stored procedures, stored functions, and triggers. Let's first look at the syntax for declaring cursors using the DECLARE statement:

DECLARE cursor_name CURSOR FOR SELECT_statement;

We should note that the cursor declaration must come after the variable declaration. If you declare a cursor before a variable declaration, mysql issues an error. Also, a cursor must always be associated with a SELECT statement. Now that we are done, let's use the OPEN statement to open the cursor. The OPEN statement initializes the result set of a cursor, so we must call the OPEN statement before fetching rows from the result set:

OPEN cursor_name;

Then, we use the FETCH statement to retrieve the next row pointed to by the cursor and move the cursor to the next row in the result set:

FETCH cursor_name INTO variables list;

After that, we can check if any row record is available and then fetch it. At the end, remember to call the CLOSE statement to deactivate the cursor and release the memory associated with it:

CLOSE cursor_name;

We need to know that when the cursor is no longer in use, it should be closed. When we use mysql cursors, we must also declare a NOT FOUND handler to handle the case when the cursor does not find any rows. Because each time the FETCH statement is called, the cursor attempts to read the next row in the result set. When the cursor reaches the end of the result set, it will not be able to get data and a condition will be generated. The NOT FOUND handler is used to handle this situation. Let's take a look at its grammatical structure:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

finished is a variable that indicates that the cursor has reached the end of the result set. Note that the handler declaration must appear after the variable and cursor declarations in the stored procedure. Let's take a look at the operating principle diagram of the MySQL cursor:

Next, we will develop a stored procedure to obtain a list of email addresses of all employees in the employees table. Let's start by declaring some variables, a cursor for looping over the employee emails, and a NOT FOUND handler:

DECLARE finished INTEGER DEFAULT 0;
DECLARE email varchar(255) DEFAULT "";
-- declare cursor for employee email
DEClARE email_cursor CURSOR FOR
 SELECT email FROM employees;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;
Next, open email_cursor using the OPEN statement:
OPEN email_cursor;

Then, iterate over the list of emails and concatenate each email using the separator (;):

get_email: LOOP
 FETCH email_cursor INTO v_email;
 IF v_finished = 1 THEN
 LEAVE get_email;
 END IF;
 --build email list
 SET email_list = CONCAT(v_email,";",email_list);
END LOOP get_email;

After that, we use the v_finished variable in the loop to check if there are any emails in the list to terminate the loop, and when we are done, we close the cursor using the CLOSE statement:

CLOSE email_cursor;

Let's take a look at all the code in the build_email_list stored procedure:

DELIMITER $$
CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))
BEGIN
 DECLARE v_finished INTEGER DEFAULT 0;
    DECLARE v_email varchar(100) DEFAULT "";
 -- declare cursor for employee email
 DEClARE email_cursor CURSOR FOR
 SELECT email FROM employees;
 -- declare NOT FOUND handler
 DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET v_finished = 1;
 OPEN email_cursor;
 get_email: LOOP
 FETCH email_cursor INTO v_email;
 IF v_finished = 1 THEN
 LEAVE get_email;
 END IF;
 --build email list
 SET email_list = CONCAT(v_email,";",email_list);
 END LOOP get_email;
 CLOSE email_cursor;
END$$
DELIMITER ;

Let's test the build_email_list stored procedure using the following script:

SET @email_list = "";
CALL build_email_list(@email_list);
SELECT @email_list;

As for the results, I won’t go into details.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL stored procedure skills", "MySQL common function summary", "MySQL log operation skills", "MySQL transaction operation skills summary" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.