Reading Time: 15 minutes

4,984 Views

Inside this article we will discuss about Basics Overview of MySQL Stored Procedures. Additionally, we will see how to create using PhpMyAdmin Interface Tool.

MySQL stored procedures are a method of encapsulating SQL statements into a reusable, modular package. They are created and saved in the database, where they can be called by applications or other procedures to perform complex tasks.

Learn More –

Need to Know – What is MySQL ?

MySQL is simply a database management tool that allows us to manage all about relational databases. It is open source platform. It means we can use MySQL without purchase or buy.

MySQL is very easy in comparison with other available database software. Other available Database tools are Oracle or Microsoft SQL Server.

What are MySQL stored procedures ?

A MySQL procedure (also called as mysql stored procedure) is a subroutine like partial functions which is stored in database. A mysql stored procedure has it’s name, a list of parameters and SQL statements. All most all RDBMS stands for relational database management system supports this mysql stored procedure features.

In other words,

A MySQL stored procedure is a segment part of SQL statements stored inside the database catalogue. A stored procedure can be called by MySQL triggers, other stored procedures and applications code logic’s for PHP and all.

If we back to PhpMyAdmin, click on the database – information_schema. Keep in mind this is default provided database, we haven’t created it. Have a look this image.

MySQL Database View for Routines Catalogue — Database: **information_schema** view

If we scroll down into the tables list we should see

Table Routines — Database: **information_schema** -> Table: **ROUTINES**

This table contains all the routines i.e stored procedure we have created in mysql including all databases

Have a look the list of routines what we have created listed there.

If we want to create routine for any application.

Create Database
Click on Routines, have a look

Let’s see all it’s advantages and disadvantages.

Advantages of MySQL stored procedures

MySQL stored procedures have many advantages, so here we have only few which are most important to know.

MySQL stored procedures help to increase in the performance of the database based applications. When we create procedures they compiled and stored into the database.
MySQL stored procedures help to reduce the application and database based generated traffic. It is because stored procedures instead of sending multiple SQL statements for every call for any database operation it sends only the name of the procedure and it’s defined parameters.
They are re-usable MySQL components in any application.
MySQL Stored procedures are more secure because of it’s availability at database level not at application level.

Each point of this section we will see in upcoming articles of this course when we create and define the stored procedures.

Along with these most important features in terms of performance also we have some disadvantages.

Let’s see the disadvantages of using mysql stored procedure.

Disadvantages of MySQL stored procedures

Here are some points which we have kept into disadvantages using mysql stored procedures.

If suppose we make an application which is stored procedures based, then the memory usage of each connection of procedure that is using will increase database processing exponentially. Additionally, if we overuse the logical operations inside procedures, then the CPU processing usage will increase accordingly. The database server actually not well-designed for performing these internal logical operations.
Stored procedure concept is for performing a specific operation without using too many logical operations.
When we work with stored procedures, then while getting errors it will not easy to debug errors. Few database drivers allow to debug only. MySQL allow users to debug.
While operating with the database stored procedures we need a skilled engineer to operate. Development and maintenance of database little bit costly.

How we use PhpMyAdmin Interface for MySQL Stored Procedure?

We have two options available to create stored procedure in MySQL.

By Using Code
By Using PhpMyAdmin Manual Interface.

Here, we will see by using phpmyadmin interface.

Let’s open any database of your PhpMyAdmin. In our case we have opened a DB named as “Cloud”. Click on Routines – a tab option at upper part of interface. Have a look,

This Routines section will help us to create Stored Procedures. Now when we click we will get a page where we have the option like – Add Routine. Inside that section we also get a list of already created stored procedures in that chosen database. Have a look to get more idea.

When we click on Add Routine it will open a popup which is a form layout. This form actually create stored procedures and mysql function. Stored Procedure/Function form which helps to take values from our end and will create the stored procedures. Let’s first see about that popup form and their input values.

Explanation of Stored Procedure Interface

As we see inside the input fields of Add Routine Popup.

Routine name : We need Routine Name (It’s stored procedure name or it will be function name). We don’t need to pass parenthesis. We only write for example : get_my_products. That’s it.
Type : This Add Routine Interface as I said it’s not only for stored procedures. We can create our own custom functions as well. So if we want to create Stored Procedure we need to select type = PROCEDURE.
Parameters : This is all about for parametrized stored procedures. As we know we have three types of parameters like IN, OUT & INOUT.
- IN – Used as Input Parameters
- OUT – Used as Output Parameters
- INOUT – Used as Input & Output parameter. According to the need of store procedure of parameters we basically pass here. Right now we only create a very basic procedure without parameters. We will about params in next articles.
Definition : Stored Procedure body or function body will go here. This is for the functional part. If we compare with Code. It’s a block of code between the keyword BEGIN and END.
Is deterministic : This is used when stored procedure is generating same output data. For example:
- Query: Select * from tbl_products; -> This query is generating same information again and again when we call -So, It is deterministic
- Query: Select * from tbl_products where id = ? -> Inside this query we have a dynamic value which outputs data on the basis of id value. So here will get dynamic data. It is Non deterministic.
Definer : It contains the information about the username & host name of phpmyadmin. For example like root@localhost. root is username of mysql and localhost is the host name of local system.
Security type : This will be a dropdown values which contains two values. Values: Definer & Invoker. So as in the previous step we are using definer where we have set root@localhost. In this case we need to select Definer.
SQL data access : Inside this we have available values are No SQL, CONTAINS SQL, READS SQL DATA & MODIFIES SQL DATA. As we have seen that inside the keyword block of BEGIN and END we should have SQL statements so we will select CONTAINS SQL option.
Comment : Optional parameter(input field). If we want to pass some comments we can write. This is for our understanding nothing else.

Create & Call a Stored Procedure – Example

Now we have completed idea about input fields. Let’s pass all needed parameters.

Parameters we need like to create MySQL Stored procedure

Stored Procedure name
Type
Definition
Is deterministic
Definer
Security Type
Sql Data Access

Have a look we have taken all values.

Code Snippet for body here,

BEGIN
       SELECT *  FROM tbl_products;     
END

Now, when we click on Go button it will create the stored procedure. Output screen will be something like this.

Great we have now created stored procedure.

For calling a stored procedure we have two options

By Code
By Tool

Calling Stored Procedure by Code

Let’s see how can we call by code. For calling via code, hold for a second and remember last article what we did. We have used the Command: Call Stored_Procedure_name().

Same we need to do here as well to call the stored procedures.

Go the SQL Tab and type CALL sp_get_products()

Press Go button to run this command or Press Ctrl + Enter. We will get the output as

Calling Stored Procedure by Tool

Inside PhpMyAdmin Interface we will see execute button to run our stored procedures. List of stored procedures and their execute buttons you will get when you click on Routines Tab of Database.

When we press Execute button to run stored procedures we will get the same output as above.

Article QA – Interview Questions

Here, we will see few important questions from this article. These questions are important for interview.

What is a MySQL Stored Procedure ?
Where we can find all created stored procedures & routines in MySQL ?
List some advantages of using Stored Procedure in MySQL
Write some disadvantages of Stored Procedure.
Can you write the TAB NAME of database where we do mysql procedures related tasks.
What is the table name of database catalogue where all mysql stored procedures stored ?
What are the parameters we need to pass to into PhpMyAdmin Tool to create a Stored procedure ?
Please list parameters type which we can pass to stored procedure.
What this “Is deterministic” means ?
How can we create a stored procedure which can select all data from a table called “users” ?

We hope this article helped you to learn about Basics Overview of MySQL Stored Procedures in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.