MySQL Stored procedure
- MySQL Stored procedure
Easy as pie hint. Sometimes in a development you need some business logic on the database side. In MySQL (just as in Oracle) you can use stored procedures and call them inside your application. It is like leaving some business logic to the database, and that business logic is executed on the database either. But let’s look at an easy easy…easy example.
We assume here that we already have a database containing a product table. We are going to create a procedure which is going to return the list of the products. Of course, it has no use because you could do the select in you own code. But the thing here is to just show you the feature so you can get further on in exploring stored procedures in MySQL. Creating a procedure is like using pl/SQL in Oracle so it has powerful features that you could use to enhance a model (lookups, and so on ….).
Here goes a procedure :
DELIMITER $$ DROP PROCEDURE IF EXISTS getAllProducts$$ CREATE PROCEDURE getAllProducts(category varchar(100)) BEGIN SELECT * FROM products where categ = category; END$$ DELIMITER ;
A little bit of explanation.
First of, what is that DELIMITER thing ?
It sets a delimiter for ending the stored procedure. The default delimiter for MySQL commands is the semicolon, but semicolons are used inside stored procedures at the the end of statements. You can use like in the example but anything goes. However, try to stick to conventions.
It is of good behaviour to use a “DROP … IF EXISTS” statement in order to enable a good maintenance of your system (and to avoid error with already existing procedures).
The rest is pretty self explanatory ! We begin by the CREATE statement and we end with the DELIMITER set back to the default semicolon.
In between you have the business logic which is delimited by the “BEGIN” and “END$$” (notice the delimiter !!) statements.
In this example I have also incorporated a variable for the category selection. It’s implementation is quite classic as you can see. But don’t forget to specify the type of variable !
These parameters are called IN variables. Any modification you do to this IN variable cannot be retrieved from the calling application. You would have to use OUT variables for that.
To call the procedure
In you app just execute the following sql statement as a MySQL query.
//sql statement call getAllProducts();
And you’re done !