Create stored procedure mysql

MySQL is a popular open source relational database management system that excels at handling large amounts of data. Stored procedures are reusable blocks of code that interact with data tables and can be called in queries.

This article will introduce how to create stored procedures, and how to call and use them in MySQL.

1. Create a stored procedure

In MySQL, creating a stored procedure usually requires the following steps:

1. Connect to the MySQL database.

2. Execute the CREATE PROCEDURE command to define the stored procedure.

3. Define the logic in the stored procedure.

The following is a sample stored procedure:

DELIMITER //
 
CREATE PROCEDURE getUsers()
BEGIN
    SELECT * FROM users;
END //
 
DELIMITER ;

In the above example, we define a stored procedure named getUsers, which will return all records in the users table.

Here we use a special DELIMITER command to tell MySQL how to separate SQL queries from stored procedures.

Next, we use the CREATE PROCEDURE command to define the stored procedure. We specify the name of the stored procedure, the incoming parameters, the logic, and the output results.

Note that we use the BEGIN and END commands in the stored procedure to put the logic in a block of code.

Finally, we use the DELIMITER command to specify the new delimiter for the SQL query, which is a semicolon (;) by default. This is done to avoid syntax errors caused by using semicolons in stored procedures.

2. Call the stored procedure

Once you create the stored procedure, you can reference it in the query just like calling a function. For example:

CALL getUsers();

After calling the stored procedure, it will execute the logic defined in the middle and return the result.

3. Pass parameters

Stored procedures can use incoming parameters to execute different logic. Here is an example:

DELIMITER //
 
CREATE PROCEDURE getUserById(IN id INT)
BEGIN
    SELECT * FROM users WHERE id = id;
END //
 
DELIMITER ;

In the above example, we created a stored procedure named getUserById and defined an input parameter id. In the stored procedure, we use a WHERE statement to match the id and return the record.

When calling a stored procedure, parameters need to be passed. For example:

CALL getUserById(1);

This will return the user record with id 1.

4. Modify the stored procedure

In some cases, you may need to modify the stored procedure. This is easy to achieve. You can use the following command to modify the existing stored procedure:

ALTER PROCEDURE getUsers() 
BEGIN 
    SELECT * FROM users WHERE age > 18; 
END;

In the above example, we modified the getUsers stored procedure to only return user records older than 18 years old. This way, you can easily modify the stored procedure to suit different business needs.

5. Delete stored procedures

When you no longer need a stored procedure, you can use the following command to delete it:

DROP PROCEDURE getUsers;

The above command will delete the name getUsers stored procedure. Please note that deleting a stored procedure will permanently delete its definition and related records.

Summary

Stored procedures are one of the important features in MySQL. Creating stored procedures allows you to organize and reuse code efficiently and perform complex operations. With this article in hand, you should now be ready to write and call stored procedures. To learn more about MySQL, see the official documentation.

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