mysql stored procedure stored function

MySQL is a popular relational database management system that supports the creation and use of database objects such as stored procedures and stored functions. In this article, we will dive into the concepts, uses, syntax, and examples of MySQL stored procedures and stored functions.

1. What are stored procedures and stored functions?

Stored procedures and stored functions are two types of database objects in MySQL. They can all be created on the MySQL server side and can be called and executed by other client programs. What they have in common is that they can encapsulate a series of SQL statements and implement some complex database operations or business logic.

Stored Procedure (Stored Procedure) is a set of predefined SQL statements, similar to subroutines or functions in other programming languages. They are written as a single SQL statement and stored on the server, and can be called when needed. Stored procedures can be used to implement some specific business requirements, such as batch processing of data, execution of transactions, fast query, etc. Stored procedures can accept parameters when executed and can also return results after execution.

A stored function (Stored Function) is a procedure that can be called in a SQL query, which accepts input parameters and returns a single value. Similar to stored procedures, stored functions are also a collection of SQL statements. Stored functions are typically used to calculate, convert, or manipulate values ​​and return the results to the caller. Unlike stored procedures, stored functions can only return a single value and cannot return a set of results. Stored functions are widely used in queries and reports.

2. Advantages of stored procedures and stored functions

1. Improve database performance: stored procedures and stored functions can run on the server, reducing the load of data transmission and network communication overhead , thereby improving database performance.
2. Better security: Stored procedures and stored functions can rely on access control to control access to tables. Because the SQL codes are stored in the database server, users cannot access them directly and can only use them with specific permissions.
3. Simplify the code: Stored procedures and stored functions can reuse SQL code, reducing repeated SQL queries and logic code writing, reducing the amount of code and making it more maintainable.
4. Portability: Stored procedures and stored functions can be reused in different applications and portable between different operating systems and database platforms.

3. Create and use stored procedures

1. Create stored procedures:

The syntax for creating stored procedures is as follows:

CREATE PROCEDURE procedure_name ([IN | OUT | INOUT] parameter datatype [, …])
BEGIN
SQL statements;
END;

• procedure_name: The name of the stored procedure
• parameter: The parameter of the stored procedure, which can be an IN (default) input parameter, an OUT output parameter and an INOUT (simultaneous input and output) parameter.
• datatype: the data type of the parameter
• SQL statements: the main statement of the stored procedure

For example, the following stored procedure will pass in the amount and customer ID, and Add the amount to the customer account balance:

CREATE PROCEDURE add_amount(IN amt INT, IN cust_id INT)
BEGIN
UPDATE customers SET balance = balance + amt WHERE id = cust_id;
END;

2. Call the stored procedure:

The syntax for calling the stored procedure is as follows:

CALL procedure_name([parameter_value]);

For example, the following code The add_amount() stored procedure just created will be called:

CALL add_amount(100, 1);

The above code will increase the balance of the customer with ID 1 by 100 yuan in the customers table.

4. Create and use stored functions

1. Create a stored function:

The syntax for creating a stored function is as follows:

CREATE FUNCTION function_name ([parameter datatype [, …]])
RETURNS datatype
BEGIN
SQL statements;
RETURN return_value;
END;

• function_name: the name of the stored function
• parameter: the parameter of the stored function
• datatype: the data type of the parameter
• SQL statements: the main statement of the stored function
• return_value: The return value of the stored function

For example, the following example creates a stored function for calculating the average:

CREATE FUNCTION average (a INT, b INT)
RETURNS INT
BEGIN
DECLARE result INT;
SET result = (a + b) / 2;
RETURN result;
END;

2. Calling the stored function:

The syntax for calling a stored function is similar to calling a stored procedure:

SELECT function_name([parameter_value]);

For example, the following code will call the average() stored function just created:

SELECT average(10, 20);

The above code will Returns 15, which is the result of (10 20)/2.

5. Examples of stored procedures and stored functions

1. Examples of stored procedures

The following stored procedure will automatically calculate the average age of all customers, and put the results into another table:

CREATE PROCEDURE calc_avg_age()
BEGIN
DECLARE avg_age FLOAT;
SELECT AVG(YEAR(CURDATE()) - YEAR(birth_date)) INTO avg_age FROM customers;
INSERT INTO statistics (name, value) VALUES ('avg_age', avg_age);
END;

2. Example of stored function

The following stored function will return the number of days between two dates:

CREATE FUNCTION days_between (date1 DATE, date2 DATE)
RETURNS INT
BEGIN
DECLARE num_days INT;
SET num_days = DATEDIFF(date2, date1);
RETURN num_days;
END;

Now, I want to know the number of days between Christmas and New Year 2022, we can use the following code to call the above stored function:

SELECT days_between('2022-12-25', '2023-01-01');

The above code will return 7, which is between the two dates number of days.

6. Conclusion

So far, we have understood the concepts, syntax and examples of MySQL stored procedures and stored functions. These objects can help us better organize SQL code and improve database performance and maintainability. In practical applications, stored procedures and stored functions are widely used in data warehouses, business intelligence, applications and other fields. However, be careful not to overuse them, as this may reduce the readability and maintainability of your code.

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