mysql stored procedure execution

MySQL is an open source relational database management system. Compared with other relational database management systems, MySQL is one of the most commonly used systems. MySQL can perform complex data processing and business logic through stored procedures, improving the performance and efficiency of the database. The following describes how to execute MySQL stored procedures.

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 PROCEDURE procedure_name ([IN / OUT / INOUT] parameter_name data_type, [...])
BEGIN
    -- 存储过程体
END;

Among them:

• procedure_name: The name of the stored procedure.
• IN / OUT / INOUT: Parameter type of stored procedure, IN represents input parameters, OUT represents output parameters, and INOUT represents input and output parameters.
• parameter_name: The name of the parameter.
• data_type: The data type of the parameter, which can be any data type supported by MySQL.
• Stored procedure body: The code block that the stored procedure actually executes

For example, the following is a simple stored procedure example:

CREATE PROCEDURE hello_world()
BEGIN
    SELECT 'Hello, World!';
END;

2. Execute storage Procedure

The "CALL" statement is required to execute a stored procedure. The syntax is as follows:

CALL procedure_name([parameter_value, ...]);

Where:

• procedure_name: The name of the stored procedure to be executed.
• parameter_value: The parameter value to be passed to the stored procedure.

For example, we can use the following statement to execute the hello_world() stored procedure created above:

CALL hello_world();

The execution results are as follows:

+-------------+
| Hello, World! |
+-------------+
| Hello, World! |
+-------------+
1 row in set (0.00 sec)

3. Use the IN parameter

Use IN parameters to pass values ​​to stored procedures to perform some specific operations. For example, the following stored procedure will use the IN parameter to find the details of a specified user:

CREATE PROCEDURE get_user_details(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END;

When executing the stored procedure, we need to pass a user ID to the stored procedure. For example, the following is an example:

CALL get_user_details(1);

The execution result is as follows:

+----+----------+---------+------------+
| id | username | country | created_at |
+----+----------+---------+------------+
|  1 | john     | USA     | 2022-05-01 |
+----+----------+---------+------------+
1 row in set (0.00 sec)

4. Using OUT parameters

Using OUT parameters can remove the value from the stored procedure return. For example, the following stored procedure will use the OUT parameter to return the detailed information of the specified user:

CREATE PROCEDURE get_user_details(IN user_id INT, OUT username TEXT, OUT country TEXT, OUT created_at DATE)
BEGIN
    SELECT username, country, created_at INTO username, country, created_at FROM users WHERE id = user_id;
END;

When executing this stored procedure, we need to pass a user ID, and the stored procedure will return the detailed information of the user corresponding to that ID information. For example, the following is an example:

CALL get_user_details(1, @username, @country, @created_at);
SELECT @username, @country, @created_at;

The execution result is as follows:

+----------+---------+------------+
| @username | @country | @created_at |
+----------+---------+------------+
| john     | USA     | 2022-05-01 |
+----------+---------+------------+
1 row in set (0.00 sec)

5. Using INOUT parameters

Using INOUT parameters can pass a parameter and returns a value using that parameter. For example, the following stored procedure will use the INOUT parameter to update the details of a specified user:

CREATE PROCEDURE update_user_details(INOUT user_id INT, IN username TEXT, IN country TEXT, IN created_at DATE)
BEGIN
    UPDATE users SET username = username, country = country, created_at = created_at WHERE id = user_id;
END;

When executing this stored procedure, we need to pass a user ID, and the details to be updated. For example, here is an example:

SET @user_id = 1;
CALL update_user_details(@user_id, 'jane', 'UK', '2022-05-01');
SELECT @user_id;

The execution results are as follows:

+---------+
| @user_id |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

As you can see, the stored procedure successfully updated the user's details and returned the user ID.

By using stored procedures, efficient data processing and business logic can be performed in MySQL. This article describes how to create, execute, and use different types of stored procedures. Using stored procedures can greatly improve the performance and efficiency of the MySQL database and provide better support for our business.

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