How to use stored procedures and functions in MySQL?

How to use stored procedures and functions in MySQL?

In MySQL, stored procedures and functions are a set of encapsulated SQL statements that can be called repeatedly. A stored procedure is a collection of SQL statements that can be executed on the server, while a function is an independent and reusable block of code. The difference between the two is that stored procedures can return multiple result sets, while functions can only return one value. This article will introduce in detail how to use stored procedures and functions in MySQL, and provide some code examples for readers' reference.

1. Use of stored procedures

1. Create stored procedures

In MySQL, you can use the CREATE PROCEDURE statement to create stored procedures. The following is a sample code to create a simple stored procedure:

DELIMITER //

CREATE PROCEDURE get_employee()
BEGIN
   SELECT * FROM employee;
END //

DELIMITER ;

In the above code, first use the DELIMITER statement to modify the separator to "//" to separate the definition and call of the stored procedure. Then use the CREATE PROCEDURE statement to create a stored procedure named get_employee, where between BEGIN and END is the code block of the stored procedure. In this example, the stored procedure queries all information from the table named employee and returns the results.

2. Calling stored procedures

After creating a stored procedure, you can use the CALL statement to call it. The following is a sample code that calls the above stored procedure:

CALL get_employee();

When calling a stored procedure, parameters can be passed. The following is an example of a stored procedure and call with parameters:

DELIMITER //

CREATE PROCEDURE get_employee_by_id(IN employee_id INT)
BEGIN
   SELECT * FROM employee WHERE id = employee_id;
END //

DELIMITER ;

CALL get_employee_by_id(1);

In the above code, an input parameter named employee_id is declared within the brackets of the CREATE PROCEDURE statement. This parameter is then used in the code of the stored procedure to filter the query results.

2. Use of functions

1. Creating functions

In MySQL, you can use the CREATE FUNCTION statement to create functions. The following is a sample code to create a simple function:

CREATE FUNCTION calculate_total(price INT, quantity INT) RETURNS INT
BEGIN
   DECLARE total INT;
   SET total = price * quantity;
   RETURN total;
END;

In the above code, the CREATE FUNCTION statement is used to create a function named calculate_total. The parameter list of a function is declared within parentheses. In this example there are two parameters: price and quantity. The function's code block is between BEGIN and END. In this example, the function calculates the total price of the item and returns the result.

2. Call the function

After you create the function, you can use the SELECT statement to call the function in the query. The following is a sample code for calling the above function:

SELECT calculate_total(10, 5);

When calling a function in a SELECT statement, you need to add parentheses after the function name and pass in the corresponding parameters. The above code will return 50, which is the result of calculating 10 times 5.

Summary:

In MySQL, stored procedures and functions are very useful functions that can improve the performance and reusability of the database. This article explains how to use stored procedures and functions in MySQL and gives corresponding code examples. By rationally using stored procedures and functions, you can simplify code writing and maintenance and improve the efficiency of database operations.

The above code examples can be used as reference for readers and can be modified and expanded according to specific needs. We hope that readers can make full use of the stored procedures and functions in MySQL to optimize their database operations based on the knowledge points provided in this article.

The above is the detailed content of How to use stored procedures and functions in MySQL?. For more information, please follow other related articles on the PHP Chinese website!