Tutorial on the use, execution and creation of MySQL stored procedures

Using Stored Procedures

Using stored procedures requires knowing how to execute (run) them. The execution of a stored procedure is encountered far more often than its definition, so we'll start with executing a stored procedure. Then introduces the creation and use of stored procedures.

Execute stored procedures

MySQL calls the execution of stored procedures a call, so the statement used by MySQL to execute stored procedures is CALL. CALL accepts the name of the stored procedure and any parameters that need to be passed to it. Please look at the following example:

Input:

call productpricing(@pricelow,@pricehigh,@priceaverage);

Analysis: Among them, a stored procedure named productpricing is executed, which calculates and returns the minimum, maximum and average price of the product.

Stored procedures can display results or not, as described later.

Creating Stored Procedures

As stated, writing stored procedures is not trivial. To give you an idea of ​​this process, consider an example - a stored procedure that returns the average price of a product. Here is its code:

Input:

create procedure productpricing() begin select avg(prod_price) as priceaverage from products;end;

Analysis: We will introduce the first and last statements later. This stored procedure is named productpricing and is defined with the CREATE PROCEDURE productpricing() statement. If the stored procedure accepts parameters, they will be enumerated in (). This stored procedure has no parameters, but the following () is still required. The BEGIN and END statements are used to qualify the stored procedure body, which itself is just a simple SELECT statement (using the Avg() function).

When MySQL processes this code, it creates a new stored procedure product-pricing . No data is returned because this code does not call the stored procedure, it is simply created for later use.

Separator characters for mysql command-line clients If you are using the mysql command-line utility, you should read this description carefully.

The default MySQL statement delimiter is ; (as you have seen in the MySQL statements used so far). The mysql command line utility also uses ; as a statement delimiter. If a command line utility were to interpret ; characters within the stored procedure itself, they would not end up as components of the stored procedure, which would cause a syntax error in the SQL within the stored procedure.

The solution is to temporarily change the command line utility's statement delimiter as follows:

delimiter//
create procedure productpricing()
begin
select avg(prod_price) as priceaverage from products;
end  //
delimiter;

where DELIMITER // tells the command line utility to use // as the new statement end delimiter symbol, you can see that END, which marks the end of the stored procedure, is defined as END// instead of END;. This way, the ; inside the stored procedure body remains intact and is passed to the database engine correctly. Finally, to revert to the original statement delimiter, use DELIMITER ;. Any character can be used as a statement separator except the \ symbol.

If you are using the mysql command line utility, please keep this in mind as you read this chapter.

So, how to use this stored procedure? As shown below:

Input:

call productpricing();

Output:

##Analysis: CALL productpricing(); Execute the stored procedure just created and Display the returned results. Because a stored procedure is actually a function, the () symbol is required after the stored procedure name (even if no parameters are passed).

The above is the detailed content of Tutorial on the use, execution and creation of MySQL stored procedures. For more information, please follow other related articles on the PHP Chinese website!