Explaining Stored Procedure Examples with Images in MySQL
A MySQL stored procedure is a set of pre-compiled SQL statements stored in a database to perform specific tasks. Using stored procedures can reduce database workload, improve performance, and reduce network traffic. In this article, we will introduce the basic syntax of MySQL stored procedures and provide a simple example to demonstrate how to create and call a stored procedure.
Create a stored procedure.
To create a stored procedure, you first need to specify the name of the stored procedure using the CREATE PROCEDURE statement, and write the SQL statements of the stored procedure between BEGIN and END. Here is a simple example:
DELIMITER //
CREATE PROCEDURE get_employee_data()
BEGIN
SELECT * FROM employees;
END //
DELIMITER ;
In the example above, we created a stored procedure named get_employee_data that retrieves data of all employees from a table called employees.
Invoke a stored procedure
To invoke a stored procedure, simply use the CALL statement followed by the name of the procedure. Here is an example of calling the stored procedure created above:
CALL get_employee_data();
Example
Suppose we have a table called employees, which includes the names and salary information of the employees. Below is an example table:
We can create a stored procedure to calculate the average salary of employees, as shown below:
DELIMITER //
CREATE PROCEDURE calculate_avg_salary()
BEGIN
SELECT AVG(salary) AS avg_salary FROM employees;
END //
DELIMITER ;
Next, we can invoke this stored procedure to retrieve the average salary of employees.
CALL calculate_avg_salary();
summarize
In this article, we present the basic syntax of MySQL stored procedures and a simple example to demonstrate how to create and call stored procedures. Stored procedures can improve database performance and reduce network traffic, making them an important tool in managing MySQL databases. We hope this article is helpful to you!
