mysql result set stored procedure storage

The stored procedure in MySQL allows users to customize a set of SQL statements, encapsulate it into a reusable code block, and can be directly called and executed in the application. This feature can greatly improve the reusability of SQL statements and the readability of code, and can also speed up query execution. Using result sets in stored procedures can provide users with more flexible data processing methods.

1. What is a result set

The result set (ResultSet) is a collection of data returned by a query operation. Through this collection, the information of the specified columns in the database table can be obtained and the information Perform operations, calculations and processing. In MySQL, stored procedures can use result sets, which can return the results of query operations to the application that calls the stored procedure for further processing and use.

2. Stored procedures that use result sets

When using result sets in stored procedures, you need to use a special keyword - CURSOR. CURSOR is a cursor type that can extract each row of data in the query result set one by one and store the data row in a temporary table for processing and output by the stored procedure.

The syntax of CURSOR is as follows:

DECLARE cursor_name CURSOR FOR SELECT statement;

Among them, cursor_name is the name of the cursor, and SELECT statement is the SQL statement that needs to be queried.

Next, we use an example to demonstrate how to use the result set in the stored procedure.

Example: Create a stored procedure to query the order information of the specified user and output the result set to the application. Since the result set can be processed and calculated more flexibly, the query results also need to include statistical information such as the total number of orders and the total amount of orders.

In the mysql client, enter the following code:

DELIMITER //

CREATE PROCEDURE order_info(IN p_customer_id INT)
BEGIN
    DECLARE order_id INT;
    DECLARE order_total DECIMAL(10,2);
    DECLARE total_orders INT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT o.order_id, o.order_total FROM orders o WHERE o.customer_id = p_customer_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SELECT COUNT(*) INTO total_orders FROM orders o WHERE o.customer_id = p_customer_id;

    IF total_orders > 0 THEN
        SELECT SUM(o.order_total) INTO order_total FROM orders o WHERE o.customer_id = p_customer_id;

        OPEN cur;

        SELECT 'Order ID', 'Order Total' UNION ALL
        SELECT '========', '===========';

        loop_cursor: LOOP
            FETCH cur INTO order_id, order_total;
            IF done THEN
                LEAVE loop_cursor;
            END IF;
            SELECT order_id, order_total;
        END LOOP;

        SELECT 'Total Orders: ', total_orders;
        SELECT 'Total Amount: ', order_total;

        CLOSE cur;
    ELSE
        SELECT 'No orders found.';
    END IF;
END
//

DELIMITER ;

The comments are as follows:

DELIMITER is used to change the default delimiter in MySQL to avoid conflict with the delimiter in the stored procedure. number conflict.

CREATE PROCEDURE is used to create stored procedures.

DECLARE is used to declare variables.

IF-ELSE statement is used to determine whether the query result is empty.

CURSOR and FETCH are used to query the result set.

The above are some key codes of this example. Let's take a look at the execution results of the stored procedure.

Enter the following statement to call the stored procedure:

CALL order_info(1);

The execution result is as follows:

From the execution result, the stored procedure can be easily It is a good way to query the order information of the specified user and output the result set to the application. At the same time, it also collects statistics on the total number of orders and the total amount of orders, realizing a more flexible data processing method.

3. Summary

This article introduces the concepts of result sets, stored procedures and cursors in MySQL, and demonstrates how to use result sets in stored procedures through a specific example. As an important feature of MySQL, result sets provide users with a more flexible data processing method, especially when used in stored procedures, which can greatly improve the reusability of SQL statements and the readability of codes, and optimize Query efficiency.

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