The 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.BEGIN
andEND
: 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', 'alice@example.com'), (2, 'Bob', 'bob@example.com'), (3, 'Charlie', 'charlie@example.com');
This creates a new database named example_db
, creates a table named users
with three columns (id
, name
, and email
), and inserts some sample data into the table.
Create a Stored Procedure
let’s create a simple stored procedure that retrieves all the users from the users
table:
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 users
table.
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:
CALL procedure_name(parameter_list);
CALL get_users();
This will execute the get_users
stored procedure and return a result set with all the users from the users
table.
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.