Starting with MySQL Stored Procedure

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 a bunch of queries that run 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:

1. Starting with MySQL Stored Procedure (Current one)
2. Parameters in MySQL Stored Procedures
3. Conditional Controls in MySQL Stored Procedure
4. Loops in MySQL Stored Procedure
5. Transactions in MySQL Stored Procedure
6. Cursors in MySQL Stored Procedure

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:

1. Delimiter
2. Create Simple Stored Procedure
3. Call your Stored Procedure
4. Modify your Stored Procedure
5. Delete yout Stored Procedure

Delimiter

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.

Create Simple Stored Procedure

Now let’s create a very basic stored procedure and understand the different parts of the same.

[cc lang=”mysql”]
DELIMITER $$

CREATE PROCEDURE `first_sp` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ‘First SP at Expertdeveloper’
BEGIN
SELECT ‘Hello Expert Developer!’;
END
$$
[/cc]

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.

Call your Stored Procedure

Calling your store procedure is very simple. Have a look at below SQL statement for the same.

[cc lang=”mysql”]
CALL stored_procedure_name (param1, param2, ….);
[/cc]

So to call our above stored procedure we need to execute below SQL statement.

[cc lang=”mysql”]
CALL first_sp();
[/cc]

Isn’t is very easy???

Modify your Stored Procedure

Just like an alter table we can edit our stored procedure also. Instead of ALTER TABLE we will have ALTER PROCEDURE statement.

Delete yout Stored Procedure

To drop procedure you just need call below statement:

[cc lang=”mysql”]
DROP PROCEDURE IF EXISTS procedure_name;
[/cc]

So in our case it would be something like below:

[cc lang=”mysql”]
DROP PROCEDURE IF EXISTS first_sp;
[/cc]

Conclusion

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.