MySQL must know-23rd chapter-Using Stored Procedures

23rd chapter-Using Stored Procedures

This chapter describes what stored procedures are, why you use stored procedures, and how to use stored procedures, and describes the basic syntax for creating and using stored procedures.

23.1 Stored Procedures

MySQL 5 required MySQL 5 added support for stored procedures, so this chapter applies to MySQL 5 and later versions. So 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.

• In order to process the order, a check is required to ensure that the items are in stock.
• If items are in stock, these items need to be scheduled so that they are not sold to another person, and the amount of available items is reduced to reflect the correct inventory.
• Items that are not in stock need to be ordered, which requires some kind of interaction with the vendor.
• Inform the appropriate customer about which items are in storage (and can be shipped immediately) and which items are being retired. 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? Each statement can be written separately, and according to the result there is a 217164 use of the stored procedure to execute additional statements. This must be done every time the process is needed (and every application that needs it). You can create stored procedures. A stored procedure is simply a collection of one or more MySQL statements that are saved for later use. It can be considered a batch file, although their role is not limited to batch processing.

23.2 Why to use stored procedures

Now that we know what a stored procedure is, why use it? For a number of reasons, here are some of the main reasons.

• Simplify complex operations by encapsulating the processing in an easy-to-use unit (as described in the previous example).
• This guarantees the integrity of the data because it does not require a series of processing steps to be repeatedly established. 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.
• 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).
• Improve performance. Because using stored procedures is faster than using a separate SQL statement.
• There are some MySQL elements and features that can be used only in a single request, and 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 3 main benefits, namely simplicity, security, and high performance. Obviously, they are all very important. However, before you can convert your SQL code to a stored procedure, you must also know some of its flaws.
• In general, stored procedures are more complex to write than basic SQL statements, and writing stored procedures requires a higher level of skill and richer experience. 218
• 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. Despite these flaws, stored procedures are very useful and should be used as much as possible. Can't write a stored procedure? You can still use MySQL to differentiate 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.

23.3 Using Stored Procedures

Using 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.

23.3.1 Executing stored procedures

MySQL says that the execution of the stored procedure is called, so the statement that MySQL executes the stored procedure is call. 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);
Where you execute a stored procedure named Productpricing, which calculates and returns the lowest, highest, and average prices for the product. Stored procedures can display the results, or they may not display the results, as described later.

23.3.2 Creating a stored procedure

As 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:

Using stored procedures we'll cover 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. 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:

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, 22022123.3 uses stored procedure 167 to use 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 (); 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).

23.3.3 Deleting a stored procedure

After 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:

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. drop procedure will produce an error if the specified procedure does not exist only if it exists. 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).

23.3.4 Using Parameters

Productpricing is just a simple stored procedure that simply displays the results of a SELECT statement. In general, stored procedures do not display results, but instead return the results to your specified input and output analysis 222168 using stored procedure variables. Variable (variable) 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):

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). parameter data type the parameters of the stored procedure allow the same data type as the data type used in the table. These types are listed in Appendix D. 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:

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). To show the average price of the retrieved product, you can do the following:

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

Here is another example, this time using the in and out parameters. OrderTotal accepts the order number and returns the total of the order:

Input/Output input/Output input 224225170 Use stored procedure 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:

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 this total, you can proceed as follows:

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:

23.3.5 Building Intelligent Stored procedures

All of the stored procedures that have been used so far are basically a simple SELECT statement that encapsulates MySQL. Although they are all examples of valid stored procedures, they can do the work you can do directly with these encapsulated statements (if they can also bring more things, analysis input and output analysis input 22623.3 using stored procedure 171 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:

• Get the total (as before);
• Add the sales tax conditionally to the total;
• Returns the total (with or without tax). The complete work of the stored procedure is as follows:
  

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 has changed 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. 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. To test it, please use the following two statements:


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 of the IF statement gives the basic usage of the IF statement of 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).

23.3.6 Checking stored Procedures

To display the Create statement that creates a stored procedure, use the Show CREATE PROCEDURE statement:


In order to obtain a list of stored procedures including when, by whom, and so on, use Showprocedure STATUS. Limit 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:

23.4 Summary

This chapter describes what a stored procedure is and why you should use it. We describe the syntax of the execution and creation of stored procedures and some methods of using stored procedures. We will continue this topic in the next chapter.

MySQL must know-23rd chapter-Using Stored Procedures