MySQL Stored Procedures - Create Smart Stored Procedures and Check Stored Procedures

Building intelligent stored procedures

All stored procedures used so far are basically encapsulated MySQL simple SELECT statements. Although they are all valid examples of stored procedures, they do things that you could do directly with these encapsulated statements (if they bring anything more to the table, they make things more complicated). The power of stored procedures truly becomes apparent only when they include business rules and intelligent processing within them.

Consider this scenario. You need to get the same order total as before, but you need to add sales tax to the total, but only for certain customers (perhaps those in your state). Then, you need to do the following things:

1. Get the total (same as before);

2. Add sales tax to the total conditionally;

3 .Returns the total (with or without tax).

The complete work of the stored procedure is as follows:

Input:

-- Name:ordertotal
-- Parmeters:onumber = order number
--           taxable = 0 if not taxable
--           ototal = order total variable
create procedure ordretotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
)comment 'obtain order total,optiomally adding tax'
begin
-- dexlare variable for total
declare total decimal(8,2);
-- delcare tax percentage
declare taxtate int default 6;
-- get the ordertotal
select sum(item_price*quantity)
from orderitems
where order_num = onumber into total;
-- is this taxable?
if taxable then
-- yes,so add taxrate to the total
select total+(total/100*taxrate) into total;
end if;
-- and finally,save to out variable
select total into ototal;
end;

Analysis: This stored procedure has a lot of changes. First, add a comment (place -- in front). This is especially important as the complexity of stored procedures increases. An additional parameter taxable has been added, which is a boolean (true if the tax is to be increased, false otherwise). In the stored procedure body, two local variables are defined using the DECLARE statement. DECLARE requires specifying a variable name and data type, and it also supports optional default values ​​(the default for taxrate in this example is set to 6%). The SELECT statement has been changed so that its result is stored into total (a local variable) instead of ototal . The IF statement checks whether taxable is true, and if so, uses another SELECT statement to add the sales tax to the local variable total. Finally, use another SELECT statement to save total (which may or may not increase sales tax) to ototal .

COMMENT keyword The stored procedure in this example includes a COMMENT value in the CREATE PROCEDURE statement. It is not required, but if given, will appear in the results of SHOW PROCEDURE STATUS. This is obviously a more advanced and powerful stored procedure. To test it, use the following two statements:

Input:

call ordertotal(20005,0,@total);
select @total;

Output:

Input:

call ordertotal(20005,1,@total);
select @total;

Output:

Analysis: BOOLEAN value specified as 1 means true, specified as 0 means false (in fact, non-zero values ​​are considered true, only 0 is considered is false). Sales tax can be conditionally added to the order total by specifying 0 or 1 for the middle argument.

IF statement This example gives the basic usage of MySQL's IF statement. The IF statement also supports ELSEIF and ELSE clauses (the former also uses a THEN clause, the latter does not). We'll see other uses of IF (and other flow control statements) in future chapters.

Check the stored procedure

To display the CREATE statement used to create a stored procedure, use SHOW CREATE

PROCEDURE statement:

Input:

show create procedure ordetotal;

To obtain a list of stored procedures including details such as when and by whom they were created, use SHOW PROCEDURE STATUS.

Limit process status results SHOW PROCEDURE STATUS lists all stored procedures. To limit its output, you can use LIKE to specify a filter pattern, for example:

show procedure status like 'ordertotal';

The above is the detailed content of MySQL Stored Procedures - Create Smart Stored Procedures and Check Stored Procedures. For more information, please follow other related articles on the PHP Chinese website!