What is a stored procedure, and how do you create one in MySQL?

A stored procedure is a collection of SQL statements designed to perform specific tasks, stored in the database and executed by calling the procedure's name. Using stored procedures helps us reuse SQL statements, reduce network traffic, improve database performance, encapsulate logic, and enhance security.

The basic syntax for creating a stored procedure in MySQL is as follows:

sql
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
    -- SQL statements
END;

Where procedure_name is the name of the stored procedure, parameter_list is the list of parameters passed to the procedure, which can be IN, OUT, or INOUT parameters. Between BEGIN and END, you can include the SQL statements to be executed.

For example, suppose we need to create a simple stored procedure to query information about an employee. We can do the following:

sql
DELIMITER //

CREATE PROCEDURE GetEmployeeInfo(IN emp_id INT)
BEGIN
    SELECT name, position, salary FROM employees WHERE id = emp_id;
END //

DELIMITER ;

In this example:

• DELIMITER // and DELIMITER ; are used to change MySQL's statement terminator because stored procedures may contain multiple statements, requiring a different delimiter to distinguish between statement terminators and command terminators.
• GetEmployeeInfo is the name of the stored procedure.
• (IN emp_id INT) defines an input parameter emp_id of type INT.
• Between BEGIN and END, we define a query statement to retrieve the name, position, and salary of the employee with id equal to emp_id.

To execute this stored procedure, you can use the following command:

sql
CALL GetEmployeeInfo(123);

Here, 123 is the value passed to the emp_id parameter.

Using stored procedures not only streamlines the execution of SQL statements and reduces errors but also improves the efficiency and security of database operations.