mysql create stored procedure

MySQL is a widely used open source relational database management system that supports multiple programming languages ​​and development platforms. MySQL provides many powerful features, including stored procedures. This article will introduce how to create stored procedures in MySQL.

1. The concept of stored procedures

A stored procedure is a set of SQL statements and control flow statements that can be defined and executed in MySQL like a function. It can access and update data in the database and allows multiple SQL statements to be executed in a single call. Stored procedures can receive parameters and return results, so they are a very powerful database programming tool.

2. Create a stored procedure

In MySQL, you need to use the CREATE PROCEDURE statement to create a stored procedure. The basic syntax of the CREATE PROCEDURE statement is as follows:

CREATE PROCEDURE procedure_name ([parameter_list])
BEGIN
    -- 存储过程的SQL语句和控制流语句
END;

Among them, procedure_name is the name of the stored procedure, parameter_list is an optional stored procedure parameter list, and between BEGIN and END are the SQL statements and control flow statements of the stored procedure.

The following is a simple example that demonstrates how to create a simple stored procedure:

CREATE PROCEDURE get_users_count()
BEGIN
    SELECT COUNT(*) FROM users;
END;

In this example, we create a stored procedure named get_users_count, which uses SELECT COUNT( *) statement gets the number of users from the users table. Note that this stored procedure has no parameters and no return value.

3. Parameters of stored procedures

Stored procedures can receive parameters, and the parameters can be IN, OUT or INOUT types. The IN type parameter refers to the input parameter. We can pass it in as a parameter of the stored procedure, but its value cannot be changed. The OUT type parameter refers to the output parameter, and the stored procedure can change its value and pass it out as the return value. INOUT type parameters are both input and output parameters.

The following is an example that demonstrates how to use stored procedure parameters:

CREATE PROCEDURE get_user(IN user_id INT, OUT username VARCHAR(50))
BEGIN
    SELECT name INTO username FROM users WHERE id = user_id;
END;

In this example, we create a stored procedure named get_user, which receives an INT type named user_id parameters as input parameters, and use the SELECT INTO statement to obtain the name of the corresponding user from the users table and assign it to the output parameter username.

4. Control flow statements of stored procedures

The control flow statements of stored procedures include IF, CASE, LOOP and WHILE. We can use them to control the execution flow of stored procedures, making them more flexible and readable.

The following is an example that demonstrates how to use IF control flow statements in stored procedures:

CREATE PROCEDURE get_user(IN user_id INT)
BEGIN
    DECLARE username VARCHAR(50);
    IF user_id = 0 THEN
        SELECT 'Guest' INTO username;
    ELSE
        SELECT name INTO username FROM users WHERE id = user_id;
    END IF;
    SELECT username;
END;

In this example, we create a stored procedure named get_user. If the parameter user_id is entered The value is 0, then assign username to the string 'Guest', otherwise get the name of the corresponding user from the users table.

5. Exception handling of stored procedures

Stored procedures also provide an exception handling mechanism, allowing us to perform custom operations under abnormal circumstances. We can create exception handlers using DECLARE statement and raise exceptions using SIGNAL statement.

The following is an example that demonstrates how to handle exceptions in a stored procedure:

CREATE PROCEDURE insert_user(IN name VARCHAR(50), IN email VARCHAR(50))
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
        SELECT 'Error: Duplicate user' AS message;
    INSERT INTO users (name, email) VALUES (name, email);
    SELECT 'User inserted successfully' AS message;
END;

In this example, we create a stored procedure named insert_user and create an exception for it handler. If the inserted data already exists in the database, a SQLSTATE '23000' exception will be raised and a SELECT statement will be executed in the exception handler to display a custom error message.

6. Call of stored procedure

To call a stored procedure, use the CALL statement and pass the parameters to the stored procedure. For example:

CALL get_user(1);

In this example, we call the stored procedure named get_user and pass parameter 1 to it.

7. Deletion of stored procedures

To delete a stored procedure, use the DROP PROCEDURE statement. For example:

DROP PROCEDURE get_user;

In this example, we delete the stored procedure named get_user.

8. Conclusion

MySQL's stored procedure is a very useful database programming tool that can improve the performance and security of the database. In this article, we introduced how to create stored procedures in MySQL and demonstrated aspects such as stored procedure parameters, control flow statements, exception handling, and calls. If you haven't learned stored procedures yet, give it a try now!

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