Detailed explanation of the stored procedures of mysql database

1. Foreword

MySQL is a popular relational database management system (RDBMS) that uses SQL statements to operate data. A stored procedure is a collection of SQL statements that can be used and called repeatedly. This article will introduce the creation, calling and instance operations of stored procedures in MySQL database.

2. Creation of MySQL stored procedures

1. Creating stored procedures

To create stored procedures in MySQL, you can use the following statement:

CREATE PROCEDURE procedure_name ([parameter_list])
BEGIN
    -- put your SQL statements here;
END;

• procedure_name: Stored procedure name.
• parameter_list: Parameter list, can be empty.
• BEGIN and END: The start and end characters of the SQL statement.

2. Parameters of the stored procedure

The stored procedure can contain 0 or more parameters. The syntax is as follows:

CREATE PROCEDURE procedure_name (IN|OUT|INOUT parameter_name data_type)

means passed in respectively , outgoing and incoming parameter types, parameter_name and data_type represent the parameter name and data type respectively.

3. Example

The following is an example of creating a stored procedure with parameters:

CREATE PROCEDURE get_employee (IN employee_id INT)
BEGIN
    SELECT * FROM employee WHERE id = employee_id;
END;

3. Calling MySQL stored procedures

Use stored procedures to implement complex business logic in MySQL. To call a stored procedure, you can use the following statement:

CALL procedure_name (parameter_list);

For example, when using the stored procedure created above, we can use the following statement to call:

CALL get_employee(1);

4. MySQL stored procedure example

Let’s look at a practical example below to implement account transfer operations.

1. Create a transfer stored procedure

CREATE PROCEDURE transfer(IN from_account INT, IN to_account INT, IN amount DECIMAL(10,2))
BEGIN
    DECLARE from_balance DECIMAL(10,2);
    DECLARE to_balance DECIMAL(10,2);

    START TRANSACTION;

    SELECT balance INTO from_balance FROM account WHERE id = from_account FOR UPDATE;
    SELECT balance INTO to_balance FROM account WHERE id = to_account FOR UPDATE;

    IF from_balance < amount THEN
        ROLLBACK;
        SELECT 'Insufficient balance' AS message;
    ELSE
        UPDATE account SET balance = from_balance - amount WHERE id = from_account;
        UPDATE account SET balance = to_balance + amount WHERE id = to_account;
        COMMIT;
        SELECT 'Transfer succeed!' AS message;
    END IF;
END;

2. Call a stored procedure

We can use the following statement to call a stored procedure:

CALL transfer(1, 2, 100);

Transfer 100 yuan from the user with account ID 1 to the user with account ID 2.

The above are the basic operations of MySQL database stored procedures. Of course, in actual development, there will be more scenarios where stored procedures need to be used, and developers need to design and call them according to actual needs.

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