How does mysql stored procedure return result set (two methods)

MySQL stored procedures are a collection of pre-written SQL instructions that can be called and executed when needed. Stored procedures are usually used to handle complex database operations, which can improve the security and performance of the database while also reducing the amount of code in the application. MySQL stored procedures can also return result sets to facilitate application queries and statistical operations.

There are two ways for MySQL stored procedures to return result sets: using OUT parameters and using cursors. The following describes the use of these two methods respectively.

1. Use OUT parameters to return the result set

Use OUT parameters to store the query results in one or more variables, and then return these variables as output parameters of the stored procedure. This method is suitable for returning smaller result sets, such as a single value or a small amount of data.

The following is an example of using the OUT parameter to return query results:

CREATE PROCEDURE `get_total_orders`(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM orders;
END;

The above stored procedure is used to calculate the total number of orders and store the result in the OUT parameter named total. The sample code for using this stored procedure is as follows:

SET @total = 0;
CALL get_total_orders(@total);
SELECT @total as total_orders;

In this example, first define the variable @total and assign it a value of 0. The stored procedure get_total_orders is then called, storing the result in the OUT parameter @total. Finally, query the value of variable @total and output the result.

2. Use a cursor to return the result set

A cursor is a pointer to a result set that can be used to randomly access each row of the result set. Using a cursor to return a result set is suitable for returning a larger result set, or when multiple queries and operations are required.

The following is an example of using a cursor to return query results:

CREATE PROCEDURE `get_customer_orders`(IN customer_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE order_id INT;
    DECLARE order_date DATE;
    DECLARE order_total DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT id, order_date, total FROM orders WHERE customer_id = customer_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO order_id, order_date, order_total;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 处理每一行数据
    END LOOP;
    CLOSE cur;
END;

The above stored procedure is used to query all orders of a specified customer and store the query results in a cursor. The part that processes row data is commented out, and corresponding operations can be performed here according to business needs. The sample code for using this stored procedure is as follows:

CALL get_customer_orders(123);

In this example, the stored procedure get_customer_orders is called and the customer number 123 is passed in as the input parameter. The stored procedure will query all the customer's orders and store the results in the cursor for subsequent operations.

Summary

MySQL stored procedures can return result sets through OUT parameters and cursors, and you can choose the appropriate return method according to business needs. Using stored procedures can improve database security and performance while also reducing the amount of application code. When designing and using stored procedures, attention should be paid to standardized writing and moderate use to achieve optimal database performance and application efficiency.

The above is the detailed content of How does mysql stored procedure return result set (two methods). For more information, please follow other related articles on the PHP Chinese website!