mysql stored procedure function

MySQL stored procedures and functions are two important concepts in the MySQL database. They can effectively simplify the management and maintenance of the database. This article will introduce in detail the basic knowledge, usage and usage precautions of MySQL stored procedures and functions.

1. MySQL stored procedures

MySQL stored procedures are a set of SQL statements to complete specific tasks. They are SQL codes written on the MySQL server and can be regarded as a batch script. It can accept input parameters, can create complex operations such as temporary tables during execution, and can return results to the calling program through output parameters. The main advantages of MySQL stored procedures are as follows:

1. Improve code reusability: stored procedures can be shared and called by multiple applications, greatly improving the reusability of code between different applications. Usability.
2. Improve performance: Since the stored procedure is pre-compiled, it can avoid repeated compilation and improve execution efficiency when performing the same operation multiple times.
3. Reduce the burden of database connection management: After the stored procedure is executed, a session context will be left before the connection is released, which shortens the time interval for repeated execution of the stored procedure, thereby reducing the establishment and closing of connections and reducing the database cost. Connection management burden.

The specific way to implement the MySQL stored procedure is as follows:

1. Use DELIMITER to set the separator: because the separator in MySQL defaults to ";", and the stored procedure also ";" will be used, so you need to use DELIMITER to set a unique delimiter.
2. Writing stored procedures: Use the CREATE PROCEDURE statement to create a stored procedure, and write specific SQL code between BEGIN and END.
3. Execute stored procedures: Use the CALL statement to execute stored procedures. The CALL statement is followed by the stored procedure name and parameters.

For example, the following example creates a simple MySQL stored procedure:

DELIMITER $$
CREATE PROCEDURE Get_Customer (

IN `cust` varchar(255)

)
BEGIN

SELECT *
FROM customer
WHERE customer_name=cust;

END$$
DELIMITER ;

Execute the stored procedure:

CALL Get_Customer('John');

2. MySQL function

Similar to a stored procedure, a MySQL function is also a block of code with a specific function, but the difference is that the function returns a value for the caller to use, while the stored procedure No return value is required.

MySQL supports two types of functions: system functions and custom functions.

System functions are functions provided by MySQL based on a series of internally predefined rules, which can meet most common needs. For example:

① ABS(): Returns the absolute value.

② LENGTH(): Returns the length of the string.

③ CONCAT(): Returns the combined string.

Custom functions are functions designed by ourselves based on needs and are highly customizable. Custom functions need to be created using the CREATE FUNCTION statement and specify the function's input parameters and return value type.

The following example creates a custom function named GetCustomerBalance to obtain the balance of a customer:

DELIMITER $$
CREATE FUNCTION GetCustomerBalance ( custname varchar(255))
RETURNS decimal(15,2)
BEGIN

DECLARE cbalance decimal(15,2);
SELECT balance INTO ccbalance FROM customers WHERE name=custname;
RETURN ccbalance;

END$$
DELIMITER ;

Use SELECT statement to test the custom function:

SELECT GetCustomerBalance('John');

3. Summary

This article introduces the basic knowledge, usage and usage notes of MySQL stored procedures and functions. matter. Stored procedures and functions can greatly improve the efficiency of database management and maintenance, improve code reusability and performance, and reduce the burden of database connection management. In specific practice, corresponding features should be selected according to specific needs and applied flexibly.

The above is the detailed content of mysql stored procedure function. For more information, please follow other related articles on the PHP Chinese website!