mysql stored procedure return

MySQL stored procedure is a set of pre-written SQL statements that can be called as a unit. MySQL stored procedures have many advantages, such as improving database performance, reducing network traffic, unifying and simplifying business logic, and improving security. However, in the process of using MySQL stored procedures, sometimes we also need to return a result set. This article will delve into how to use MySQL stored procedures to return a result set.

1. Create a stored procedure

Creating a MySQL stored procedure is very simple and can be done through the CREATE PROCEDURE statement. Here is a simple example of a stored procedure that accepts two integer parameters, adds them and returns the result:

DELIMITER //
CREATE PROCEDURE add_numbers(IN num1 INT, IN num2 INT, OUT result INT)
BEGIN
SET result = num1 num2;
END //
DELIMITER ;

2. Call stored procedure

Call storage The process is also very simple, just use the CALL statement. Here is an example that calls the stored procedure created above and prints the results to the console:

SET @a = 1;
SET @b = 2;
CALL add_numbers (@a, @b, @result);
SELECT @result;

3. Return result set

If you need to return a result set in the MySQL stored procedure , then you can use CURSOR. CURSOR can be used to iterate through a result set and save the results to a variable. Here is an example that returns a result set of a product list:

DELIMITER //
CREATE PROCEDURE get_product_list()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE id INT;
DECLARE name VARCHAR(255);

DECLARE cur CURSOR FOR SELECT id, name FROM products;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

CREATE TABLE IF NOT EXISTS temp_product_list(id INT, name VARCHAR(255));
TRUNCATE TABLE temp_product_list;

OPEN cur;

read_loop: LOOP

FETCH cur INTO id, name;
IF done THEN
  LEAVE read_loop;
END IF;

INSERT INTO temp_product_list(id, name)
VALUES(id, name);

END LOOP;

CLOSE cur;

SELECT * FROM temp_product_list;
END //
DELIMITER ;

In the above code, we first create a CURSOR object , and specify the product list to be queried. While traversing the result set, we insert the results into the temp_product_list table one by one. Finally, we return the query results.

4. Processing returned results

When calling a stored procedure, we can use the SELECT statement to process the returned result set. For example:

CALL get_product_list();

This statement will call the get_product_list stored procedure and return a result set. We can use the SELECT statement to output the result set to the console:

SELECT * FROM temp_product_list;

This statement will output all results in the temp_product_list table.

Summary

MySQL stored procedures are a very useful technology that can help us improve database performance, reduce network traffic, unify and simplify business logic, and improve security. In actual use, we may need to return a result set. In this case, we can use CURSOR to traverse the query results and save the results to a table. Finally, we can use the SELECT statement to process the returned result set. Through the introduction of this article, I believe readers will have a deeper understanding of how to use MySQL stored procedures to return result sets.

The above is the detailed content of mysql stored procedure return. For more information, please follow other related articles on the PHP Chinese website!