Stored Procedures are a great feature that can help you build
logic into your database and improve data quality.
In this video, you’ll learn what a stored procedure is, and see some simple examples
of creating stored procedures in MySQL. We’ll see small examples that insert,
update, delete, and select records in a database.
A stored procedure is a collection of SQL statements that can be run.
It’s an object that is stored in the database. You can run the stored procedure after you create it,
and it can be run by other users or applications that can access the database.
Let’s start by creating a simple table. I’ve got a link for this
script on my GitHub repository in the description below if you want to copy it.
We’re going to create a new table called person. It has three fields: an ID, a name,
and an is_active field to indicate if the person is active or inactive.
We’ve got a couple of Insert statements here as well, to add some data to the table.
This is just to get us started, so our stored procedures will return some data.
We can run both of these statements, the Create Table and Insert, and they should be successful.
Now, let’s create our first stored procedure.
We’ll create a stored procedure that selects some data from a table.
To create a procedure, we write the Create Procedure command.
SQL is not case-sensitive, so you can enter it in either upper case or lower case, it’s up to you.
Next, we need to provide a name for our new procedure. Given that we will
be selecting data from our newly-created person table, let’s call it selectPerson.
After the name, we need to add a pair of brackets.
This is where we can add parameters if we need them, but for this example we don’t have any.
Next, we add the word BEGIN. I like to do this on a new line to make it more readable.
After the BEGIN keyword, we’ll leave a gap to add in the code that we want to run.
Then we add the END keyword on a new line.
Inside the BEGIN and END keyword is where we can add the code for the procedure to run.
We want to select some data from the person table, so let’s write a query for that.
We’ll write Select id, name, is_active from person, and add a semicolon.
So, this is our stored procedure. When we run this Create Procedure statement,
a new stored procedure is created on the database.
Let’s run this now. If you’re using MySQL Workbench,
you might see a bunch of red underlines here. But we’ll try to run it anyway.
When we run this script, we get an error in the output panel.
We can see some of the text in the Response column,
but we can copy the response value and paste it into the editor to see the full message.
The error says, you have an error in your SQL syntax, then more text,
then “use near BEGIN SELECT” and the rest of our query.
This error happens because MySQL thinks that the end of the statement that creates the procedure
is at the first semicolon that it sees. In this example, it’s at the end of the SELECT statement.
So, the database can’t create the stored procedure
because it doesn’t know where the rest of the Create Procedure statement is.
But we need to have the semicolon inside the procedure to make the Select statement work.
Couldn’t MySQL just know where the stored procedure ends, like other databases do?
Well, it can, but we need to tell it. The semicolon character is called a delimiter,
and we need to indicate in our script that one delimiter should be used at the end of
the procedure and another should be used at the end of statements inside the procedure.
To do this, we add the Delimiter keyword before the Create Procedure keyword. This will let us
set the delimiter for our script, which is the character that indicates the end of a statement.
After the delimiter keyword, we need to specify the character to use.
You can use many different characters, but a common one used is the dollar sign.
So we have delimiter $ here.
Then, we have the rest of our Create Procedure command.
After our End statement, we change the semicolon to the dollar sign.
This indicates that this is the end of the Create Procedure statement.
Finally, we add a Delimiter command at the end, and specify the original semicolon value.
This means any statements after this will use the
semicolon as the delimiter, which is the default setting.
Now we can run the statement again.
Run the entire script, and not just the one statement, so that all of the code is run.
This statement ran successfully. We did not get an error.
Now we have created the stored procedure, the next step is to run it.
To do this, we use the CALL command.
Type CALL, then the procedure name, then open and close brackets,
then a semicolon. The brackets are there to accept any parameters in the procedure.
We haven’t included any when creating our procedure, so these brackets are empty for now.
Run this command. The procedure will be run.
You’ll see the results at the bottom of the window. It looks just like the results of a
Select query. This is because our stored procedure ran a Select query inside it.
So that’s how you can create a simple stored procedure to select data.
Let’s see a few more examples.
Now we will create a stored procedure to insert data.
It starts the same way as the first example. We write Create Procedure. Then we give it a name.
Let’s say we want to insert a new person record with this procedure,
so we can call it insertPerson.
We then have the BEGIN on a new line, and the END on a few lines below that.
We need to have those Delimiter statements before and after our
command, so our procedure can be created.
What do we put inside our procedure?
We want to add an Insert statement here. So, we enter Insert Into Person,
then specify the columns, which are id, name, and is_active.
We then specify values.
After the bracket is where we add in the values we want to insert.
But, what do we insert? How do we enter the values we want to insert?
This is where parameters can be used. A parameter is an input to a stored procedure that is used
within the code in the stored procedure. The value is provided when the procedure is run.
For us, this means we can provide the values for the insert statement
as parameters when we run the procedure.
To do this, let’s add one parameter. We add a parameter by adding brackets
after the name of the procedure. Inside the brackets, we specify a few things.
First, we add the word IN. This means the parameter is an IN parameter,
which is a type of parameter where we provide the value when we run the procedure and the
value is used inside the procedure. The other type is an OUT parameter,
which is calculated inside the procedure and provided to whatever calls the procedure.
Next, we have the name of the parameter.
We’ll call ours personName, just so we don’t get it confused with the name column in the table.
Finally, we have the data type. This is a name of the person,
so we’ll give it a varchar type and a length that matches the name column
We close the brackets before the begin keyword.
Now we have specified a parameter, we can use this in our Insert statement.
For the ID column, ideally we would have this auto-generated or provided as a parameter,
but to keep it simple for now we’ll just add a static value of 8.
For the name, we specify the name of the parameter which was personName.
For the is_active, we can set that to 1.
Now our procedure is complete.
We can run this script and the procedure is created.
Let’s call this stored procedure now.
We can write a Call statement, then the name of the procedure.
Let’s run this command.
When we do, we get this error here. It says,
“incorrect number of arguments for procedure, expected 1 got 0”.
What happened? This error is shown because we ran the stored procedure
that expected a parameter, but we did not provide a parameter.
Let’s fix that. To add a parameter when calling a procedure, we add a pair of brackets.
Inside the brackets we specify the value to use for the parameter.
Because it’s a string, we enclose it in quotes.
Now we can run the command again.
We can see here the command is successful. It shows a green tick and says 1 rows affected.
There is no Select output shown, because the procedure did not include any Select statements.
We can run a Select query here, just a simple one, and we can see the results.
We can see our new value is inserted into the table.
So that’s how you can create a procedure that inserts data. There’s a whole lot of logic
you could and should add to a procedure like this, but that’s the basic version.
What else can you do?
Let’s see an example of a stored procedure that updates some data.
We start with the same structure as the previous example, with the delimiter and create procedure.
We’ll call it updatePerson.
What parameters do we need? We need to know two things when we are updating a record:
what the new value should be, and what the criteria is for the rows to update.
Let’s say we want to update the person name, so the new person name value is one parameter.
And let’s say we want to work out which person to update based on the ID,
so that will be our second parameter. We separate them in the parameter section here with a comma.
Inside our procedure, we write our Update statement.
This Update statement will update the person name to the value provided as a parameter.
The Where clause will allow us to update only the person records with the provided ID.
Our procedure is now written. We can run this script and the procedure is created.
Now, let’s call it. We can call it in the same way as the other procedures.
This procedure is a little different as we have two parameters.
We add both parameters, separated by a comma. We have a name of Peter and an ID of 5.
We can run this statement, and can see it was successful.
How can we see what the data looks like?
We can run a Select query. We’ll run the same one as before to see all data in the table.
In our results, we can see that the record we specified in the update procedure has
the new value we specified. The record with an ID of 5 now has a name of Peter.
The final example we’ll cover is how to delete a row in a stored procedure.
It works in the same way as other procedures.
We’ll start with an empty procedure like this, which we’ve called delete person.
To delete a row, we need to know the criteria for the row to be deleted.
Let’s say we want to specify an ID, and delete that row. So we add that as a parameter.
Now, inside our procedure, we write a Delete statement.
We delete from our person table, and add a where clause that deletes based on the ID parameter.
The procedure is now written. Let’s run the script, and the procedure is created.
Let’s run the procedure. We can run it in a similar way to the other procedures,
using the CALL statement. We specify a parameter here.
In this example, we’ll provide an ID for a row that does not exist, just to see what happens.
When we run this command, we can see the procedure runs. There are no errors,
but in the output panel in MySQL Workbench it shows us that 0 rows were affected.
This indicates that no rows were deleted.
Let’s call the procedure again, but we’ll use an ID for a record that exists.
We get the same success message, but the response says that 1 row was deleted.
Now, we’ll run a select query to see the data. We can see the results here, and see that the
ID we specified is no longer found in the table. This means our delete statement is successful.
So we’ve seen some basic examples of stored procedures in MySQL. What else can you do?
You may want to add some error-handling logic to your procedure, such as checking
if a row exists before you update or delete it.
You may also want to add some validation on the input parameters,
such as if a value is not empty or matches a certain format.
There are a lot of other things you can do in the procedure as well.
Hopefully, this video helps you with your understanding of stored procedures in MySQL.
If you have any questions or requests, leave a comment below.
If you want to learn more about database design and SQL, visit my website at databasestar.com.
If you liked this video, consider subscribing to my channel.
Thanks for watching.