Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to use cursors in mysql storage procedures. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

Cursors can be used when working with result sets in stored procedures, because cursors allow us to iterate over a set of rows returned by the query and process each row accordingly. Mysql cursors are read-only, non-scrollable and sensitive. Let's take a look at:

Read-only: the data in the underlying table cannot be updated with the cursor.

Non-scrollable: rows can only be fetched in the order determined by the select statement. You cannot get rows in reverse order. In addition, you cannot skip rows or jump to specific rows in the result set.

Sensitive: there are two types of cursors: sensitive and insensitive. Sensitive cursors point to actual data, while insensitive cursors use temporary copies of data. A sensitive cursor executes faster than an insensitive cursor because it does not need to temporarily copy data. However, any changes to data from other connections will affect the data used by sensitive cursors, so it is more secure if you do not update the 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 declared using the declare statement:

DECLARE cursor_name CURSOR FOR SELECT_statement

We should note that the cursor declaration must be after the variable declaration. If the cursor is declared before the variable declaration, mysql will issue an error, and the cursor must always be associated with the SELECT statement. When we're done, let's use the open statement to open the cursor. The OPEN statement initializes the result set of the 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 line that the cursor points to and move the cursor to the next row in the result set:

FETCH cursor_name INTO variables list

After that, we can check to see if any row records are available and then extract it. Finally, remember to call the close statement to deactivate the cursor and free the memory associated with it:

CLOSE cursor_name

We need to know that when the cursor is no longer in use, it should be turned off. When we use mysql cursors, we must also declare a NOT FOUND handler to handle situations when the cursor cannot 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 cannot get the data and a condition is generated. The NOT FOUND processor is used to handle this situation. Let's take a look at its syntax structure:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1

Finished is a variable that indicates that the cursor reaches the end of the result set. Note that the handler declaration must appear after the variable and cursor declaration in the stored procedure. Let's take a look at the schematic diagram of the operation of the mysql cursor:

Next, we will develop a stored procedure to get the e-mail list of all employees in the employees table. Let's first declare some variables, a cursor for looping employee e-mail, and a NOT FOUND handler:

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

Then, iterate over the e-mail list and connect each e-mail with a delimiter (;):

Get_email: LOOP FETCH email_cursor INTO vested 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

Then, in the loop, we use the v_finished variable to check if there are any emails in the list to terminate the loop, and when we're done, use the close statement to close the cursor:

CLOSE email_cursor

Let's take a look at all the code of 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 vault 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; is how to use cursors in mysql stored procedures. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.