I was doing some routine code review this past week when I came across something I wasn’t familiar with. A very cryptic looking chunk of SQL. It turns out the code I was looking at was a Stored Procedure.
What is a Stored Procedure? A Stored Procedure is a set of SQL statements with an assigned name that’s stored in the database in compiled form so that it can be shared by a number of programs. This concept should be familiar, if you have been developing for any bit of time: method definition and execution. Sounds like a great way to DRY up some SQL queries or aggregations.
When would you want to use a Stored Procedure? You can leverage the power of these procedures when you want to move intensive work to the DB. One example may be when you want to create a lot of seed data for your application. You can use a Stored Procedure to create, update, and aggregate your data at the database level.
The structure of a procedure can contain…
It is recommended to surround your procedure with BEGIN and END to improve readability, however a multi line statement will require the use of BEGIN and END. Quick tip, changing the SQL delimiter to something other than a
; will allow you to use a semicolon in your method. Let’s look at an example.
First see how we use a
; in the where clause. This is allowed because we have changed the delimiter to a
$$. Notice how we END our statement to close the above definition and swap the delimiter back to
And call your procedure.
Lets look at some more demo examples.
First lets create a database. Inside your terminal open a mysql instance and enter
So we created a db named my_db, added a person table, and filled that person table with 5 Harry Potter Pals. Now lets create our very first procedure.
If you ever want to check your stored procedure, in a mysql prompt do the following.
Stored Procedures can also take parameters. These can be IN, OUT or INOUT.
IN are the inputs to the procedure.
OUT are the outputs from the procedure.
INOUT are both input and outputs for the procedure.
Lets look at some examples.
In the above statement,
INTO, sets the second argument passed into the first_name value found.
Since the second value passed in is
@harry, this variable is set inside the SQL statement. Now we can get the value stored in
@harry from running the procedure.
SELECT @harry; // returns @harry set to 'Harry'
Now that we have an understanding of IN and OUT, I will let you try and work through an INOUT example.
First set a value in a mysql prompt.
SET @val=10. Then use an INOUT procedure and pass
@val into the method.
After you have created your INOUT procedure,
@val should have a new value when you run a select on the variable.
Now go procedure all the things!