Stored procedure is a great feature to write the reusable queries, I mean write once and use as much you need. Generally Stored procedures contains the bunch of queries which runs one after another.
I would like to inform you that I am starting the Article Series for MySQL Stored Procedure. In this serier I will cover below points:
In this first article we will cover very basic things with MySQL Stored Procedure. Just have a look at below list of points which I will cover in this article:
Delimiter is string or character which defines the end of your SQL statement. The reason to define the delemiter is as below:
Semicolon (;) is the default ending notification any SQL statement. But in stored procedure we can have multiple SQL statements so we can not consider semicolon as a ending indication. That’s why we are defining delimiter for each stored procedure in MySQL. Generally I use $$ as delimiter in my stored procedure.
Now let’s create a very basic stored procedure and understand the different parts of the same.
Very first sentance will create your procedure. The other next four things are optional characteristics of the procedure. Then BEGIN defines the start of the procedure body and END defines the end of the procedure body.
Have a look at those four optional characteristics in detail:
LANGUAGE: For portability purposes; the default value is SQL.
DETERMINISTIC: If the procedure always returns the same results, given the same input. This is for replication and logging purposes. The default value is NOT DETERMINISTIC.
SQL SECURITY: When we call the procedure it will check for the privileges of the user. DEFINER is the user who has created the procedure and INVOKER is the user who is calling the procedure. Default value is DEFINER.
COMMENT: This is just like a table comment, basic information of the stored procedure.
Stored Procedure names are case sensitive.
Calling your store procedure is very simple. Have a look at below SQL statement for the same.
So to call our above stored procedure we need to execute below SQL statement.
Isn’t is very easy???
Just like an alter table we can edit our stored procedure also. Instead of ALTER TABLE we will have ALTER PROCEDURE statement.
To drop procedure you just need call below statement:
So in our case it would be something like below:
So Here I have started a new series for MySQL Stored Procedures and I have completed the first article here. If you don’t want to miss other articles then subscribe to our feed via your email, Like us on Facebook or Follow us on Twitter.
Avinash Zala currently working at a leading web development company as a Sr. PHP Web Developer. He is the founder of the Xpert Developer. He has completed B.E. in Information Technology. Born and brought up in Ahmedabad. He loves to code and coding is all about passion for him. Follow him on twitter at Avinash Zala & Xpert Developer.
View all posts by: Avinash