MySQL Stored Procedure

These days I'm involved with a new project in my office and in this project we are using mysql store procedures heavily. So I decided to write up a post on mysql store procedures. :)

I'm going to discuss this topic in a series of articles -- in this series I will cover:

•   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 then subscribe to feed via your email or Follow  on Twitter.

Thanks, Have a nice day ;)