MySQL stored Procedure Storage function Concept Example

A stored procedure is a programmable function that can be created and saved in MySQL. It consists of some SQL statements and some special control structure statements.

Stored procedures are a very useful way to perform the same functions on different applications or platforms, or to encapsulate specific functionality. Stored procedures in a database can be seen as simulations of object-oriented methods in programming.

Basic Example Total_ordres

Delimiter//create procedure Total_orders (out of total float) BEGIN Select sum (amount) to total from orders; END//delimiter;

Below, let's analyze the above code row by line:

First line of statement:

Delimiter//

The delimiter at the end of the statement is from the default value (the default delimiter for MySQL; ) Change to//. The purpose of this is to use a semicolon delimiter in the stored procedure so that MySQL uses the semicolon as the code for the stored procedure and does not execute immediately.

The following statement:

CREATE PROCEDURE Total_ordres (out of total float)

Defines a stored procedure. The name of the stored procedure is total_orders, which has only one total parameter, which is the last value to get the result. Out indicates that the parameter will be outgoing or returned . This can also be declared as in, which means that the value must be passed into the stored procedure. Or inout indicates that the value must be passed in but can be modified by the stored procedure .

Float indicates the type of the parameter.

If you want to use more than one parameter, you can provide a list of parameters separated by commas, just like in PHP. The procedure body must be encapsulated in the BEGIN END statement.

After you have declared the procedure, you can reset the delimiter to a semicolon:

delimiter;

After the procedure has been declared, you can invoke the procedure with the call keyword:

Call Total_orders (@h);

This statement invokes the Total_orders procedure and passes in a variable to hold the result.

To view the variable, the following statement shows:

Select @h;

Function

Similar to the way you create a procedure, you can also create a function. The function receives an input parameter and returns a unique value. The basic syntax for creating functions is almost the same.

Delimiter//create function Add_tax (price float) retuns Floatreturn price*1.1;d elimiter;

As you can see, the example uses the function keyword. Rather than the previous procedure keyword. In addition, there are some other differences.

Parameters do not have to be specified by in or out, because all parameters in the function are in or input parameters. After the argument list is the returns FLOAT clause, which specifies the type of the return value. Again, this value can be any valid MySQL type.

Use the return statement to return a value, as described in PHP.

Please note that the BEGIN and end statements are not used in this example. Of course you can use them, but they are not required. Just like in PHP, if a statement block contains only one statement, the start and end tags of the statement block can be omitted.

There are some differences between calling a function and calling a procedure. A stored function can be called in the same way as a built-in function.

Select Add_tax (100);

The return of the statement is as follows:

+-------------------+

| Add_tax (100) |

+-------------------+

| 110 |

+-------------------+

After you have defined stored procedures and stored functions, you can view the code that defines these procedures and functions by using statements such as the following:

Show create procedure total_orders; or show create function add_tax;

You can also use the following statements to delete them:

drop procedure total_orders; or drop function add_tax;

Stored procedures provide the ability to use control structures, variables, declare handles (like exceptions), and the important concept of cursors .

In the following article, we will briefly describe these concepts.

This article from "Professor elder brother" blog, reprint please contact the author!

MySQL stored Procedure storage function Concept example