Execute mysql stored procedure

Execute MySQL stored procedures

MySQL stored procedures are a set of SQL statements that are pre-compiled and stored on the server. They can accept input parameters and return output parameters, or they can execute SQL queries and produce result sets. Executing MySQL stored procedures can greatly simplify the database application development process and improve the efficiency of data management. Below are the steps to execute a MySQL stored procedure.

Step 1: Create a stored procedure

In MySQL, you can use the create procedure statement to create a stored procedure. The syntax is as follows:

CREATE [DEFINER = { user | CURRENT_USER }]

PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

Among them, DEFINER specifies the user name of the creator of the stored procedure. If DEFIER is not specified, the current user is used by default. sp_name is the name of the stored procedure, and proc_parameter specifies the input and output parameters of the stored procedure. Characteristic includes the attributes of the stored procedure, such as language type, data modification method, etc. routine_body is a collection of SQL statements executed by the stored procedure.

For example, create a simple MySQL stored procedure to query the information of a specified employee. It requires an employee ID as an input parameter and returns information such as the employee's name, department name, and salary. The DDL statement is as follows:

CREATE PROCEDURE get_employee_info(IN emp_id INT)
BEGIN
SELECT emp_name, dept_name, salary
FROM employees, departments
WHERE employees.emp_id = emp_id AND
departments.dept_id = employees.dept_id;
END;

In this example, we define an input parameter emp_id, which is used to specify the employee ID for which information is to be queried. The stored procedure uses a SQL SELECT statement to query information such as name, department name, and salary and returns it to the caller.

Step 2: Execute the stored procedure

To execute the MySQL stored procedure, you can use the CALL statement. The syntax is as follows:

CALL sp_name(argument_list);

Where, sp_name is the name of the stored procedure, and argument_list is the parameter list passed to the stored procedure. In our example, you can use the following statement to execute the stored procedure:

CALL get_employee_info(1001);

This will query the information of the employee whose employee ID is 1001, and add the name, department Information such as name and salary is returned to the caller.

If the stored procedure simply executes some SQL statements without returning results, you can use the following syntax to execute the stored procedure:

EXEC sp_name;

For example, the following is A simple MySQL stored procedure for doubling the salaries of employees in a specified department. It does this using an UPDATE statement, which returns no results:

CREATE PROCEDURE double_salary(IN dept_name VARCHAR(50))
BEGIN
UPDATE employees, departments
SET salary = salary * 2
WHERE employees.dept_id = departments.dept_id AND
departments.dept_name = dept_name;
END;

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

EXEC double_salary('Sales');

This will double the salary of all employees in the sales department.

Step 3: Delete the stored procedure

If the stored procedure is no longer needed, you can use the DROP PROCEDURE statement to delete it. The syntax is as follows:

DROP PROCEDURE sp_name;

where sp_name is the name of the stored procedure to be deleted.

Summary

MySQL stored procedure is a very powerful database application development tool that can greatly simplify coding and debugging work. By defining stored procedures, SQL code can be encapsulated into functions or procedures, thereby reducing the workload of repeatedly writing SQL statements and improving code reusability and maintainability. Therefore, it is very important for developers with certain knowledge of MySQL to be proficient in the use of stored procedures.

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