What is a stored procedure is simply a set of one or more MySQL statements for future use. It can be considered as a batch file, although their role is not limited to batch processing. Like defining a function, you can pass in outgoing parameters, and the function performs some calculations internally. (This article is for MySQL learning and testing) Why
What is a stored procedure is simply a set of one or more MySQL statements for future use. It can be considered as a batch file, although their role is not limited to batch processing. Like defining a function, you can pass in outgoing parameters, and the function performs some calculations internally. (This article is for MySQL learning and testing) Why
What is a stored procedure is simply a set of one or more MySQL statements for future use. It can be considered as a batch file, although their role is not limited to batch processing. Like defining a function, you can pass in outgoing parameters, and the function performs some calculations internally. (This article is for MySQL learning and testing) Why to use stored procedure 1. simplify complicated operations by encapsulating processing in easy-to-use units. since there is no need to establish a series of processing steps repeatedly, this ensures data integrity. 3. simplify the management of changes. Create a stored procedure create a very simple stored procedure and return the average product price stored procedure:
Create procedure productpricing () begin select Avg (prod_price) AS priceaverage FROM products; END //
Calling the stored procedure is relatively simple:
Delete stored PROCEDURE: drop procedure productpricing;
Use parameter Example 1 and the following are the modified versions of productpricing: create procedure productpricing (OUT pl DECIMAL (), OUT ph DECIMAL (), OUT pa DECIMAL )) begin select Min (prod_price) INTO pl FROM products; SELECT Max (prod_price) INTO ph FROM products; SELECT Avg (prod_price) INTO pa FROM products; END //
CALL the Stored Procedure CALL productpricing (@ pricelow, @ pricehigh, @ priceaverage )//
Display call results:
In Example 2, the following example shows how to create a stored procedure, input the order number, and return the total amount of the order:
Create procedure ordertotal (IN onumber INT, OUT ototal DECIMAL (8, 2) begin select Sum (item_price * quantity) FROM orderitems WHERE order_num = onumber INTO ototal; END //
Call the stored procedure:
CALL ordertotal (20005, @ total )//
Display results;
To create a smart storage process, consider setting up the following storage process to calculate the total number of orders. However, this order requires a value-added tax, but not all customers need a value-added tax. Therefore, we have the following work: 1. Obtain the total value. 2. Add the value-added tax to the total value. 3. Return the total value.
The complete work of the stored procedure is as follows:
When calling this stored procedure, enter the order number, whether to collect the tax, and the final result is:
You can use show procedure status to view the Stored procedure status:
