mysql stored procedure usage

Usage of MySQL stored procedures

MySQL stored procedures are a set of predefined SQL statements designed to complete specific tasks. Stored procedures can help improve database performance, improve maintainability, and also help reduce network traffic and security.

This article will introduce the basic syntax of MySQL stored procedures, as well as several practical examples.

1. Basic syntax

1. Create a stored procedure

The syntax for creating a stored procedure is as follows:

CREATE PROCEDURE procedure_name(
    [IN] input_parameter_name data_type,
    [OUT] output_parameter_name data_type,
    [INOUT] input_output_parameter_name data_type
)
BEGIN
    -- SQL语句
END;

Among them, procedure_name represents the name of the stored procedure, input_parameter_name Indicates the name of the input parameter, output_parameter_name indicates the name of the output parameter, and input_output_parameter_name indicates the name of the input and output parameters.

2. Call a stored procedure

The syntax for calling a stored procedure is as follows:

CALL procedure_name([input_parameter_value], [output_parameter_value], [input_output_parameter_value]);

Among them, procedure_name represents the name of the stored procedure to be called, and input_parameter_value represents the parameter to be passed into the stored procedure. Input parameters, output_parameter_value represents the return value of the output parameter, and input_output_parameter_value represents the value of the input and output parameters.

2. Practical examples

1. Use stored procedures to query student scores

The following is a simple example of querying student scores through stored procedures:

CREATE PROCEDURE get_student_grade(IN student_name VARCHAR(255))
BEGIN
    SELECT * FROM student_grade WHERE name = student_name;
END;

This stored procedure receives a student's name as an input parameter and outputs the student's grade information.

2. Use a stored procedure to update the administrator password

The following is another example of updating the administrator password through a stored procedure:

CREATE PROCEDURE update_admin_password(IN admin_id INT, IN new_password VARCHAR(255))
BEGIN
    UPDATE admin SET password=new_password WHERE id=admin_id;
END;

This stored procedure receives the administrator's ID and new password as input parameters, and updates the administrator's password.

3. Use stored procedures to delete expired data

The following is an example of deleting expired data through stored procedures:

CREATE PROCEDURE delete_expired_data(IN table_name VARCHAR(255), IN expire_date DATE)
BEGIN
    DELETE FROM table_name WHERE date < expire_date;
END;

This stored procedure receives the table name and expiration date as input parameters , and delete expired data in the specified table.

3. Summary

This article mainly introduces the basic syntax and practical examples of MySQL stored procedures. Stored procedures can help us complete more work by improving the performance and maintainability of the database. However, when using stored procedures, we need to pay attention to security and avoid problems such as SQL injection attacks. It can also be seen from the above examples that stored procedures can be used to update data, query data, and delete data. It is a very practical function in the MySQL database.

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