MySQL Stored Procedure.


Those days I’m involving with the new project in my office and In this project we are heavily used mysql store procedures there. Because of that I’m decide to publish some technical post related to mysql store procedures. :)

I’m going to discuss this topic using series of article, in this series I will cover these topics

  •     Introduction about  MySQL Stored Procedure (Current one)
  •     How to handle Variables and Parameters in MySQL Stored Procedures
  •     How to use Conditional Controls in MySQL Stored Procedure
  •     How to handle Loops in MySQL Stored Procedure
  •     How to use Transactions in MySQL Stored Procedure and Sql Exception, SQL Warning Handling
  •     How to use Cursors in MySQL Stored Procedure
  •     How to Debugging MySQL Store Procedure
  •     How Write Unit Testing for MySQL Store Procedure

Ok next we move to identify what is the basic concept behind the store procedure. In this article I’m going to give the answers for these Questions

  1. What is the purpose of Store Procedures?
  2. What is the purpose of using Delimiter?
  3. How to Create Simple Stored Procedure?
  4. How to Call your Stored Procedure?
  5. How Delete Stored Procedure?
  6. How Modify Stored Procedure?

 1.  What is the purpose of Store Procedures?

Stored procedure is a great feature to write the reusable queries, I mean write once and use as much you need. Generally stored procedures contain the bunch of queries which runs one after another.

2. What is the purpose of using Delimiter?

Delimiter is ]character which defines the end of your SQL statement.

Why we define it ? O.o

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. if you need you can define your own delimiter in store procedure.

3. How to Create Simple Stored Procedure?

DELIMITER $$

CREATE PROCEDURE `sp_sample_procedure` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'comment goes here.'
BEGIN

-- sql querry (insert/select/update/delete)goes here.

END$$

CREATE PROCEDURE `sp_sample_procedure` () : This sentance will create your procedure.
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.

These are optional characteristics (LANGUAGE,DETERMINISTIC,SQL SECURITY,COMMENT)

4. How to Call your Stored Procedure?

Simply Executing sp name with CALL keyword like below SQL statement.


CALL sp_sample_procedure();

5. How Delete Stored Procedure?

Simply executing Drop statement with sp name we can drop the sp, with drop statement it’s better us the IF EXISTS statement using this statement we can avoid the error coming if given procedure not found. in the execute time in the database.


DROP PROCEDURE IF EXISTS procedure_name;

6. How Modify Stored Procedure?

Same as the alter table we can edit our stored procedure also. we will have ALTER PROCEDURE statement.

DELIMITER $$

ALTER PROCEDURE `sp_sample_procedure` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'comment goes here.'
BEGIN

-- sql querry (insert/select/update/delete)goes here.

END$$

So Here I have completed the first article here. If you don’t want to miss other articles thensubscribe to feed via your email or Follow  on Twitter.

Thanks, Have a nice day ;)

About these ads

2 thoughts on “MySQL Stored Procedure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s