CREATE PROCEDURE statement in MySQL is used to create a new stored procedure in the database. A stored procedure is a set of SQL statements that are stored in the database server and can be called from various applications or scripts. The syntax for creating a stored procedure in MySQL is as follows:
CREATE PROCEDURE procedure_name(parameter_list) BEGIN -- SQL statements here END;
Let’s break down this syntax:
CREATE PROCEDURE: This is the MySQL command that is used to create a stored procedure.
procedure_name: This is the name you want to give to your stored procedure.
parameter_list: This is an optional list of input parameters that you can pass to the stored procedure when it is called. Each parameter is specified with a name and a data type.
END: These keywords mark the beginning and end of the stored procedure’s code block.
SQL statements: These are the actual SQL statements that make up the code of the stored procedure.
Sure! Here’s an example of how to create and execute a simple stored procedure in MySQL:
First, let’s create a new database and a table to store some data:
CREATE DATABASE example_db; USE example_db; CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) ); INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'email@example.com'), (2, 'Bob', 'firstname.lastname@example.org'), (3, 'Charlie', 'email@example.com');
This creates a new database named
example_db, creates a table named
users with three columns (
Create a Stored Procedure
let’s create a simple stored procedure that retrieves all the users from the
DELIMITER // CREATE PROCEDURE get_users() BEGIN SELECT * FROM users; END // DELIMITER ;
This stored procedure is named
get_users and has no input parameters. It simply executes a
SELECT statement to retrieve all the rows from the
Note that we’re using the
DELIMITER command here to specify a different delimiter (
//) for the stored procedure definition. This is necessary because the stored procedure definition contains a semicolon, which is also used as a statement delimiter in MySQL. By using a different delimiter, we can avoid confusing MySQL’s parser.
Executing a stored procedure
To execute a stored procedure in MySQL, you can use the
CALL statement followed by the name of the stored procedure and any input parameters that it requires. Here is the syntax:
This will execute the
get_users stored procedure and return a result set with all the users from the
That’s it! This is a very simple example, but it should give you an idea of how stored procedures work in MySQL. You can create more complex stored procedures with input parameters, output parameters, conditionals, loops, and more.