Detailed explanation of MySQL stored procedure deletion operation and examples of using parameters

Delete stored procedures

After a stored procedure is created, it is saved on the server for use until it is deleted. The delete command (similar to the statement introduced in Chapter 21) deletes the stored procedure from the server. To delete the newly created stored procedure, you can use the following statement:

Input:

drop procedure productpricing;

Analysis: This statement deletes the newly created stored procedure. Please note that the following () is not used, only the stored procedure name is given.

DROP ONLY IF EXISTS If the specified procedure does not exist, DROP PROCEDURE will generate an error. Use DROP PROCEDURE IF EXISTS when a procedure exists and you want to delete it (without generating an error if the procedure does not exist).

Using Parameters

productpricing is just a simple stored procedure that simply displays the SELECT statement result. Generally, stored procedures do not display the results, but return the results to the variables you specify.

Variable (variable)A specific location in memory used to temporarily store data. The following is a modified version of productpricing (you cannot create this stored procedure again without first deleting it):

Input:

create procedure productpricing(
out pl 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;

Analysis: This stored procedure accepts 3 parameters: pl Store Product The lowest price, ph stores the highest price of the product, and pa stores the average price of the product. Each argument must have the specified type, here decimal values ​​are used. The keyword OUT indicates that the corresponding parameter is used to pass a value out of the stored procedure (return to the caller). MySQL supports IN (passed into a stored procedure), OUT (passed out of a stored procedure, as used here), and INOUT (passed into and out of a stored procedure) types of parameters. The code for the stored procedure is located within the BEGIN and END statements, which, as you saw earlier, are a series of SELECT statements that retrieve values ​​and then save them to the appropriate variables (by specifying the INTO keyword).

Data types of parameters The data types allowed for the parameters of the stored procedure are the same as the data types used in the table. Appendix D lists these types.

Note that recordset is not an allowed type, therefore, multiple rows and columns cannot be returned through one parameter. This is why the previous example uses 3 parameters (and 3 SELECT statements). In order to call this modified stored procedure, 3 variable names must be specified, as follows:

Input:

call productpricing(@price low,
@pricehigh,
@priceaverage);

Analysis: Since this stored procedure requires 3 parameters, they must be passed exactly 3 parameters, no more, no less. So, this CALL statement gives 3 parameters. They are the names of the 3 variables where the stored procedure will hold the results.

Variable name All MySQL variables must start with @.

When called, this statement does not display any data. It returns variables that can later be displayed (or used in other processing). In order to display the average price of the retrieved products, proceed as follows:

Input:

select @priceaverage;

Output:

To get 3 values, you can use the following statement:

Input:

select @pricehigh,@pricrlow,@priceaverage;

Output:

The following is another example, This time use the IN and OUT parameters. ordertotal accepts the order number

and returns the total of the order:

Input:

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;

Analysis: onumber is defined as IN because the order number is passed into the stored procedure. ototal is defined as OUT because the total is returned from the stored procedure. The SELECT statement uses these two parameters, the WHERE clause uses onumber to select the correct row, and INTO uses ototal to store the calculated total.

To call this new stored procedure, you can use the following statement:

Input:

call ordertotal(20005,@total);

Analysis: Two parameters must be passed to ordertotal; the first parameter is the order number , the second parameter is the name of the variable containing the calculated total. To display this total, proceed as follows:

Input:

select @total;

Output:

Analysis: @ total has been filled in by the CALL statement of ordertotal, and SELECT displays the values ​​it contains. In order to get the total display of another order, you need to call the stored procedure again and then redisplay the variable:

Input:

call ordertotal(20009,@total);
select @total;

The above is the detailed content of Detailed explanation of MySQL stored procedure deletion operation and examples of using parameters. For more information, please follow other related articles on the PHP Chinese website!