MySQL stored procedures

one, stored proceduresSo far, most of the SQL statements used are single statements for one or more tables. Not all operations are so simple, and often there is a complete operation that requires multiple statements to complete.        For example, consider the following scenario.        1, in order to process orders, need to check to ensure that the inventory of the corresponding items.        2. If there are items in stock, these items need to be scheduled so that they are not sold to another person, and the amount of available items will be reduced to reflect the correct stock.        3. Items not in stock need to be ordered, which requires some kind of interaction with the supplier.     4, about which items storage (and can be shipped immediately) and which items to unsubscribe, need to inform the corresponding customer. This is obviously not a complete example, it's even beyond the scope of the sample table used in this book, but it's enough to help express what we mean. Performing this processing requires multiple MySQL statements for many tables.     In addition, the specific statements that need to be executed and their order are not fixed, and they may change (and will be) based on which items are not in the inventory. So, how do you write this code? One is that we can write each statement individually and conditionally execute additional statements based on the result. This must be done every time the process is needed (and every application that needs it).     The other can create a stored procedure. In fact, simply put: A stored procedure is a collection of one or more MySQL statements saved for later use. It can be considered a batch file, although their role is not limited to batch processing. second, why to use stored procedures        Since we know what a stored procedure is, why use it? For a number of reasons, here are some of the main reasons.         1 simplifies complex operations by encapsulating the processing in an easy-to-use unit (as described in the previous example).         2,   because there is no need to repeatedly establish a series of processing steps, which ensures the integrity of the data. If all developers and applications use the same (test and test) stored procedures, the code used is the same. The extension of this point is to prevent errors. The more steps you need to perform, the greater the likelihood of errors. Prevent errors to ensure data consistency.         3, simplify the management of changes. If a table name, column name, or business logic (or something else) changes, you only need to change the code of the stored procedure. The people who use it don't even need to know about these changes. The extension of this point is security. Restricting access to the underlying data through stored procedures reduces the chance of data corruption (data corruption that is caused by an unconscious or other cause).         4, improve performance. Because using stored procedures is faster than using a separate  SQL statement.         5, there are some  mysql elements and attributes that can only be used in a single request. Stored procedures can use them to write more powerful and flexible code (as can be seen in the example in the next chapter.)     in other words, using stored procedures has the  3 primary benefit of simplicity, security, and high performance. Obviously, they are all very important. However, before you can convert  sql code to a stored procedure, you must also know some of its flaws.         1, generally speaking, the writing of stored procedures is more complex than the basic  sql statement, and the writing of stored procedures requires a higher skill and richer experience.         2, you may not have secure access to create stored procedures. Many database administrators restrict the creation of stored procedures, allow users to use stored procedures, but do not allow them to create stored procedures.   &nbSp Despite these flaws, stored procedures are very useful and should be used as much as possible.      can't write stored procedures? You can still use: MySQL distinguishes between the security and access to write stored procedures and the security and access to execute stored procedures. It's a good thing. Even if you can't (or don't want to) write your own stored procedures, you can still execute other stored procedures at the appropriate time.   iii. using Stored proceduresUsing stored procedures requires knowing how to execute (run) them. The execution of a stored procedure is far more frequent than its definition, so we'll start with the execution of the stored procedure.     Then we introduce the creation and use of stored procedures. The Execute stored procedure MySQL calls the execution of the stored procedure as a call, so the statement that MySQL executes the stored procedure is called. Call accepts the name of the stored procedure and any arguments that need to be passed to it. Take a look at the following example:

Call Productpricing (@ pricelow,                             @ pricehigh,                             @ priceaverage                           );

Which executes productpricing's stored procedure, he calculates and returns the lowest price, highest price, and average value of the product. Stored procedures can display the results, or they can not display the results, which are mentioned next. create a stored procedureAs mentioned, writing a stored procedure is not trivial. To get you through this process, take a look at an example-a stored procedure that returns the average price of a product. Here's the code:

CREATE PROCEDURE productpricing () BEGIN   SELECT AVG (prod_price) as priceaverage from Products   ; END;

We'll 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 are listed in (). This stored procedure has no parameters, but the followed () is still required.    The BEGIN and END statements are used to qualify a stored procedure body, which is itself a simple SELECT statement (using the AVG () function introduced in chapter 12th). When MySQL processes this code, it creates a new stored procedure productpricing.    There is no return data because this code does not call the stored procedure, which is only created for later use.    Here's one thing to note: MySQL command line client delimiter if you are using the MySQL command-line utility, you should read this description carefully. The default MySQL statement delimiter is; (as you've seen in the MySQL statement you've used so far). The MySQL command-line utility is also used, as a statement delimiter. If the command-line utility is to interpret the characters within the stored procedure itself, they will not eventually become the component of the stored procedure, which causes the SQL in the stored procedure to appear syntactically wrong. The workaround is to temporarily change the statement delimiter for the command-line utility 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, you can see that the end of the flag stored procedure is defined as end//instead of end;. In this way, the stored procedure is in vivo, remains intact, and is passed correctly to the database engine. Finally, to revert to the original statement delimiter, you can use the DELIMITER;. Any character, except the \ symbol, can be used as a statement delimiter.     If you're using the MySQL command-line utility, keep this in mind as you read this chapter. So, how do you use this stored procedure? As shown below:

Call Productpricing ();

The result is:

+--------------+| Priceaverage |+--------------+|    16.133571 |+--------------+

Call Productpricing (); Executes the stored procedure you just created and displays the returned results. Because a stored procedure is actually a function, the stored procedure name needs to have a () symbol (even if you do not pass parameters). To Delete a stored procedureAfter the stored procedure is created, it is saved on the server for use until it is deleted. The delete command (similar to the statement described in chapter 21st) deletes the stored procedure from the server. To delete the stored procedure you just created, use the following statement:

DROP PROCEDURE productpricing;

This statement deletes the stored procedure that was just created.    Note that no later () is used, only the name of the stored procedure is given. Delete only when present: if the specified procedure does not exist, DROP procedure will produce an error. Drop PROCEDURE If EXISTS can be used when a procedure exists to delete it (if the procedure does not exist and does not produce an error). Using ParametersProductpricing is just a simple stored procedure that simply displays the results of a SELECT statement. In general, the stored procedure does not display the result, but instead returns the result to the variable you specified. The variable (variable) described here is a specific location in memory that is used to temporarily store data. The following is a modified version of productpricing (you cannot create it again if you do not first delete this stored procedure):

DELIMITER//create PROCEDURE Pricing (out   PL decimal (8, 2), out   ph decimal (8, 2), Out   pa decimal (8, 2),) Begi N   SELECT MIN (prod_price) to   Pl from   productes;   SELECT MAX (Prod_price) into the   ph from   productes   ; SELECT AVG (Prod_price) to   PA from   productes;  END//delimiter;

This stored procedure accepts 3 parameters: PL Storage Product Lowest price, ph storage product highest price, PA storage product average price. Each parameter must have the specified type, where the decimal value is used. The keyword out indicates that the corresponding parameter is used to send a value (returned to the caller) from the stored procedure. MySQL supports in (passed to stored procedures), out (outgoing from stored procedures, as used here) and INOUT (for stored procedure incoming and outgoing) parameters of type.    The code for the stored procedure is in the begin and end statements, as seen previously, as a series of SELECT statements that retrieve values and then save to the appropriate variable (by specifying the INTO keyword). Note The data type of the parameter: the parameters of the stored procedure allow the same data type as the data type used in the table. Note that the recordset is not a permitted type, so you cannot return multiple rows and columns through one parameter.    This is why the previous example uses 3 parameters (and 3 SELECT statements). To invoke this modified stored procedure, you must specify 3 variable names, as follows:

Call Pricing (@pricelow, @pricehigh, @pricevarage);

Since this stored procedure requires 3 parameters, you must pass exactly 3 parameters, no more and no less. So, this call statement gives 3 parameters.    These are the names of the 3 variables in which the stored procedure will save the results.    Variable name all MySQL variables must begin with @. When called, this statement does not display any data. It returns variables that can be displayed later (or used in other processes).

SELECT @pricevarage

Output:

+--------+| @pricevarage |+--------+|  55.00 |+--------+

In order to obtain 3 values, the following statements can be used:

SELECT @pricelow, @pricehigh, @pricevarage;

You will see a result of the output. Here is another example, this time using the in and out parameters. OrderTotal accepts the order number and returns the total of the order:

CREATE PROCEDURE OrderTotal (in Onumber int,out ototal decimal (8,2)) Beginselect sum (item_price * quantity) from OrderItems where order_num = Onumberinto ototal;end//

Onumber is defined as in because the order number is passed into the stored procedure. Ototal is defined as out because the total is to be returned from the stored procedure.     The SELECT statement uses these two parameters, where clause uses Onumber to select the correct row, and into uses ototal to store the calculated totals. To invoke this new stored procedure, you can use the following statement:

Call OrderTotal (20005, @total);

You must pass two parameters to the OrderTotal, the first parameter is the order number, and the second parameter is the variable name that contains the calculated totals. In order to display the secondary totals, you can do the following:

SELECT @total;

Output:

+--------+| @total |+--------+|   192.37|+--------+

The @total has been filled in by the OrderTotal call statement, and select displays the values it contains. To get the total display of another order, you need to call the stored procedure again and then display the variable again:

Call OrderTotal (20009, @total); SELECT @total;

Output:

+--------+| @total |+--------+|  38.47 |+--------+

We can use this method to get the total price of a product every time we have to pass the order number. is not very useful ah.

building Intelligent Stored ProceduresAll of the stored procedures that have been used so far are basically a simple SELECT statement that encapsulates MySQL. While they are all examples of valid stored procedures, the work that they can accomplish can be done directly with these encapsulated statements (if they can bring more stuff). That is to make things more complicated).       Only when you include business rules and intelligent processing within a stored procedure will their power really manifest. Consider this scenario. You need to get the same order totals as before, but you need to increase the sales tax on the totals, but only for some customers (perhaps the customers in your state). So, here are a few things you need to do: 1, get the total (as before) 2, add the sales tax conditional to the total 3, return the total (with or without tax) we enter the following code:

--Name:ordertotal        //   Add Comment--parameters:onumber = order number--             Taxable = 0 if not taxable, 1 if taxtable--             ototal = Order Total Variablecreate     PROCEDURE OrderTotal (in Onumber int,in taxable boolean,out ototal DECIMAL (8,2)) COMMENT ' obtain order total, optionally a Dding tax ' BEGIN    --  Declare variable for total           Declare total DECIMAL (8.2);   declaring variables         --  Declare tax percentage    Declare taxrate INT DEFAULT 6;       --Get The order total    SELECT SUM (item_price * quantity) from    orderitems    WHERE order_num = Onumber    in To total       -was 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        
   

This stored procedure is subject to significant changes. First, add a comment (placed before--). This is especially important when the complexity of stored procedures increases. Added another parameter, taxable, which is a Boolean value (False if you want to increase the tariff to true). In the stored procedure body, two local variables are defined with a Declare statement. Declare requires specifying the variable name and data type, and it also supports optional default values (in this example, the default of TaxRate is set to 6%). The SELECT statement changes so that its result is stored to total (local variable) instead of Ototal. The If statement checks if taxable is true, and if true, adds the sales tax to the local variable total with another SELECT statement.    Finally, use a different SELECT statement to save total (which increases or not increase sales tax) to ototal. Note: The comment keyword, the stored procedure in this example contains a comment value in the CREATE procedure statement.    It is not required, but if given, it will be displayed in the results of show PROCEDURE status. This is obviously a more advanced, more powerful stored procedure. In order to test it, please use the following two statements: the first article:

Call OrderTotal (20009, 0, @total); SELECT @total;

Output:

+--------+| @total |+--------+|  38.47 |+--------+

Article II:

Call OrderTotal (20009, 1, @total); SELECT @total;

Output:

+--------+| @total |+--------+|  36.21 |+--------+

A Boolean value of 1 is specified as true, and specifying 0 for false (in fact, non-0 values are considered true and only 0 are considered false). By specifying 0 or 1 for the intermediate parameters, you can conditionally add the business tax to the order totals. This example gives the basic usage of the IF statement for MySQL. The IF statement also supports the ElseIf and ELSE clauses (the former also uses the then clause, which is not used).     In later chapters we will see other uses of if (as well as other flow control statements). Check the stored procedure to display the CREATE statement that was used for creating a stored procedure, using the Show CREATE PROCEDURE statement:

Show CREATE PROCEDURE OrderTotal;

To get a list of stored procedures that include when, who created, and so on, use SHOW PROCEDURE STATUS. Note: To limit the process status results, SHOW PROCEDURE status lists all stored procedures. To limit its output, you can use like to specify a filtering mode, for example:

SHOW PROCEDURE STATUS like ' OrderTotal ';

MySQL stored procedures