MySql Stored Procedure

Introduction:A stored procedure includes a name, a list of parameters, and a set of SQL statements that can contain many SQL statements.

A stored procedure includes a name, a list of parameters, and a set of SQL statements that can contain many SQL statements.

Create a stored procedure:

Syntax: Create procedure p () BEGIN /* Body of the stored procedure */ END

Create procedure productpricing () BEGIN SELECT Avg (pro_price) AS priceaverage FROM products; END; # Begin... End is the subject definition of the stored procedure. # The mysql Delimiter is a semicolon (;)

The method to call a stored procedure is:

# Add the process name and brackets to the CALL. # For example, call the stored procedure defined above CALL productpricing (); # Even if no parameter is required, the brackets () after the stored procedure name are required.

To delete a stored procedure, follow these steps:

Drop procudure productpricing;

Create a stored procedure with parameters:

Create procudure productpricing ( OUT p1 DECIMAL (8, 2 ), OUT ph DECIMAL (8, 2 ), OUT pa DECIMAL (8, 2) ) 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; # DECIMAL: Specifies the Data Type of a parameter. # OUT indicates that this value is output from the stored procedure. # MySQL supports OUT, IN, and INOUT

Call a stored procedure with parameters:

CALL productpricing (@ pricelow, @ Pricehigh, @ Priceaverage ); # All parameters must start @ # To obtain the value of @ priceaverage, use the following statement SELECT @ priceaverage; # Use the following statement to obtain three values: SELECT @ pricehigh, @ pricelow, @ priceaverage;

Another stored procedure with IN and OUT parameters:

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 ordertotal (20005, @ total ); SELECT @ total;

Add a complete example: