How to create and use stored procedures in MySQL

MySQL is a widely used relational database management system, in which stored procedures are a special programming structure that can be used to complete a series of specific tasks. This article will introduce the basic concepts of MySQL stored procedures and show how to create and use stored procedures in MySQL with simple examples.

1. What is a MySQL stored procedure?

A stored procedure is a pre-compiled SQL code block, which contains a series of SQL statements, flow control statements and variable definition statements, and can be called and executed through a separate name. Stored procedures can be created in MySQL and stored on the database server, and simply called when they need to be used. The advantage of using stored procedures is that a series of operations and calculation logic can be gathered together, making maintenance and management easier.

2. The basic syntax of MySQL stored procedures

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

DELIMITER $$

CREATE PROCEDURE procedure_name(IN parameter_name DATATYPE, OUT parameter_name DATATYPE, INOUT parameter_name DATATYPE)

BEGIN

    -- SQL逻辑代码块

END $$

Among them, CREATE PROCEDURE is the keyword for creating stored procedures, procedure_name is the custom stored procedure name, IN, OUT and INOUT are the three ways to pass parameters, parameter_name is the parameter name of the stored procedure, and DATATYPE is the data type of the parameter. In the SQL logic code block, the functions of the stored procedure can be realized through SQL statements, flow control statements and variable definition statements.

3. Example of MySQL stored procedure

The following will use a simple example to show how to use MySQL stored procedure.

Example: Calculate the result of adding two numbers

Suppose we need a stored procedure to calculate the result of adding two numbers and return the result to the caller. Then you can follow the following steps:

1. Use the DELIMITER statement to redefine the terminator. This is because MySQL uses the semicolon ";" as the terminator by default, and you also need to use the semicolon as the terminator in the stored procedure. The terminator of the statement, so it needs to be redefined.

DELIMITER $$

2. Create a stored procedure named "add_two_numbers" and define two IN type parameters a and b.

CREATE PROCEDURE add_two_numbers(IN a INT, IN b INT)

3. Write a SQL logic code block between BEGIN and END to calculate the result of adding two numbers and store the result in a variable named "result".

BEGIN
    DECLARE result INT;

    SET result = a + b;

    SELECT result;
END $$

4. Use the SELECT statement to call the stored procedure and pass in the values ​​of parameters a and b.

SELECT add_two_numbers(3, 5);

5. Execute the above code and the output result is 8.

The complete sample code is as follows:

DELIMITER $$

CREATE PROCEDURE add_two_numbers(IN a INT, IN b INT)
BEGIN
    DECLARE result INT;

    SET result = a + b;

    SELECT result;
END $$

SELECT add_two_numbers(3, 5);

4. Summary

MySQL stored procedure is a method that helps improve SQL programming efficiency and manage database maintenance work. technology. This article introduces how to create and use stored procedures in MySQL through a simple example. When you need to write complex SQL logic code blocks, you can consider using stored procedures to achieve more efficient and convenient database management.

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