Skip to main content

Learn about MySQL Stored Procedures


MySql 5 introduced the concept of stored procedure functionality. If you have already worked on other DBMS(Database management System) or Mysql,  you might be familiar with the concept of stored procedure. We will learn more about it in detail here.

What is mysql stored procedure?

Stored procedure is a set of SQL codes stored in a database server which can be invoked by a program, trigger or stored procedure itself.  Stored procedure is a way to execute tasks/business logic directly on your database server. Generic tasks can be performed  which are dependent on database table data.

so rather to go multiple time on database to fetch data into your program and perform your business logic stored procedure give some generic way of coding for your business logic and take data return or you can save your processed data into your database.

Let us take an example:-

A loan officer wants to change the floating interest levied for a customer’s loan account.
What is your normal course of action? (using your program?) From the database you will fetch capital, rate of interest, duration and calculate interest.  You will go back to the database and save data. In this case we enter the database twice. But, if we use a stored procedure we just need to write this operation within our stored procedure and call it through the program one time. You can fetch  capital, rate of interest and duration from the database and save data after processing.  We interrupt the database server only one time. Thanks to the stored procedure, conservation of server resources - check!

Advantages of Mysql Stored Procedure:


Multiple applications running in different environments sharing a database.

Business logic which is independent of programming language.

When security is a main concern use of stored procedure is vital.  By doing your operation through the database you can log  all performed actions.

Stored procedure does not give direct table access which is one more way to secure data and transactions.

Stored procedure increases performance of your application. When a stored procedure is created and compiled, it never goes to parser, directly fetch the record and execute. Whereas normal SQL query fired on database server get parsed every time so using stored procedure you can save parsing time.

If your application is big or your database server is on a remote system,  using stored procedure can decrease traffic between your database server and application server.

Since stored procedure is written in your database server and application calls it separately, the degree of reusability increase because despite  going in much detail you can call stored procedure to perform your action.

Disadvantages of using stored procedure :

Following are the situations where in we should avoid using mysql store procedure.

Sometimes use of stored procedure is a bit risky. Stored procedure follow "define one use many time" philosophy. Doing change in stored procedure directly affect your data so it should always be used very carefully. Stored procedure is a set of sql commands that forms a logic. This makes it very hard to debug.

Managing stored procedure is a little difficult because it does not have any object oriented paradigm.

Since stored procedure has its own advantages and disadvantages, before choosing the option of using stored procedure we should be very careful and decide whether we should use stored procedure or not.

Example :-

        As we have discussed earlier mysql stored procedures are simple SQL statements like normal query but  difference is that query never saved and when you will run stored procedure it will be saved in your RDBMS system.

Let us create a simple stored procedure which will select one string.

mysql > create procedure helloworld() Select 'hello test';
Query OK, 0 rows affected (0.00 sec)

To call this procedure you need to run the following mysql query

mysql > CALL helloworld();
Now this query will run select 'hello test'.
This is simple. Let us experiment with something more complex.
Mysql stored procedure never return value directly. Either you need to specify output parameter in your stored procedure or you have to put select statement inside the procedure which will fill data in your resource.

Writing a mysql procedure involves 3 steps

  1. Definition of the procedure:- Create procedure syntax with the name
  2. Definition  of the input parameter:- There are three types of parameters you can define. ‘in’ parameter, ‘out’ parameter and ‘inout’ parameter.
Using ‘in’ parameter you can define inputs of the stored procedure,
‘out’ parameter specifies the output parameter.
‘inout’  defines shared parameter, it can also be used either as input parameter or output parameter.
  1. Body of the procedure:- Normally we write within the BEGIN and END tag.
Let us create a simple stored procedure:

DELIMITER $$

CREATE
PROCEDURE `test`(IN capital DOUBLE , IN rate INT , IN duration INT , OUT interest DOUBLE)

BEGIN
SET interest = (capital * rate * duration)/100;
INSERT INTO `administrators`(`interest`) VALUES(interest);

END$$

DELIMITER ;

The procedure called  test  which takes inputs as capital , rate, duration and calculates interest and returns interest in output variable and interest into database.
Let us run this procedure.
mysql > call test(100 , 7 , 3 , @primary_interest);
mysql > select @primary_interest;
Now @primary_interest will give you the interest calculated by the stored procedure.
You can use input parameter input in query written within stored procedure.

Let us take an example:

DELIMITER $$
CREATE
PROCEDURE `restaurant`.`another_test`(IN restaurant_name VARCHAR(255))
BEGIN
SELECT * FROM `restaurants` WHERE `restaurants`.`name` = restaurant_name;
END$$
DELIMITER ;

As we discussed earlier, in mysql you can put all your database based business operations.  Writing a mysql stored procedure is almost similar to writing any high level program in any language.  All you have to learn is variable declaration, conditional operators and Mysql Cursor to write a mysql stored procedure program.

Variable Declaration and operation on variable in stored procedure:

Like other languages in mysql stored procedure you can declare variables too. You should declare the variable at the beginning of the code/program. Which means right after the BEGIN tag.

You can declare variable in mysql programming like this:

DECLARE i INT(3)
DECLARE j INT(9) DEFAULT 6;

In mysql we use DECLARE tag to declare the variable. You have to specify the datatype of the variable to declare the variable. I recommend to specify size of the variable if it is required like int(4), For some of the variables you can not declare the variable without size like varchar.
Scope of the variable in mysql is limited to END tag. If you have declared the variable in stored procedure then after END tag variable will be lost. You can initialize the variable after declaration like this:-

DECLARE i INT(3);
SET i = 10;

In mysql you can take value in the variable from the query also from the help of INTO keyword. Example:

DECLARE student_name VARCHAR(23);
SELECT student.name INTO student_name FROM student_table.

Condition statement in Mysql programming:

Condition statement will give you power to execute code on the basis of the same value.  Similar to any high level programming language, you can add conditional statements in mysql stored procedure.

example:-

DECLARE count_student INT(5) default 0;
SELECT count(*) INTO count_student FROM student_table;
IF count_student > 5 THEN
      SELECT * FROM student_table;
ELSE
SELECT 'Very less student';
END IF;

For every IF statement in Mysql you have to specify END IF statement. In mysql you can also use ELSEIF for recursive statements. You can also use switch case based conditional statement in mysql, style of writing switch is a little different.

example:-

CASE
WHEN i >2 THEN
SELECT 'it is two';
WHEN i  < 2 THEN
Select 'it is less then 2';
ELSE
SELECT 'no eyse';
END CASE;

Every CASE will be closed with END CASE, Like a any program we have default tag with name ELSE.
Iteration control(Loop control) in mysql: In mysql you can use loop also as you do in your programming language. Here you can implement loop using WHILE , REPEAT and LOOP Tag.

Comments

Popular posts from this blog

How to Create a PDF file in Cakephp 2.0 using Fpdf

Step 1: Download FPDF folder from  http://www.fpdf.org/  . Step 2: Unzip the downloaded Fpdf file and name it “fpdf” or however you require and make sure that you use the same name while calling it. Step 3: Move the “fpdf” unzipped files to  your /app/Vendor directory within Cakephp. Now you should have the directory path as   /app/Vendor/fpdf. Step 4: Create a new Cakephp layout file for the pdfs. We will use this layout when serving a pdf to the client. Create a file called pdf.ctp inside of /app/View/Layouts. Add the following code to /app/View/Layouts/pdf.ctp Layout: /app/View/Layouts/pdf.ctp 1 2 3 4 <?php      header ( 'Content-Disposition: attachment; filename="downloaded.pdf"' ) ;      echo $content_for_layout ; ?> The header function above tells the browser that it is going to receive a file called download.pdf. If you want to change the name

Setup CakePHP Using Xampp On Windows

Step 1: Download XAMPP  and  CakePHP .   Step 2: Install Xampp Once you have installed Xampp (version 1.7.3) on your Windows with the default option, all your files will be located in the C:\xampp folder. Step 3: Mod Rewrite Module Once Xampp is installed as the local server, you can then proceed to enable mod_rewrite. To do so, you will have to open the httpd.conf file that is located in C:\xampp\apache\conf and uncomment by removing # from the following line: # LoadModule rewrite_module modules/mod_rewrite.so Step 4: Place CakePHP Files in a New Folder Extract the CakePHP (version 1.3.8) zip file and copy all its contents to your local web server, which in this instance is C:\xampp\htdocs\cakephp . I have decided to name the CakePHP folder as cakephp, and in it, you will find many files and folders for the framework, including app, cake, docs, vendors, .htaccess, and index.php. Step 5: Set Up Virtual Host Open the httpd-vhosts.conf file from the C:\xampp\apa

Installing Wamp on Windows

WAMP is an abbreviated name for the software stack Windows, Apache, MySQL, PHP. It is  derived from LAMP which stands for Linux, Apache, MySQL, and PHP. As the name implies, while LAMP is used on Linux servers, WAMP is used on Windows servers.  The “A” in WAMP stands for Apache.  Apache  is server software that is used to serve webpages. Whenever someone types in your WordPress website’s URL, Apache is the software that “serves” your WordPress site. The “M” in WAMP stands for MySQL.  MySQL  is a database management system. It’s job in the software stack is to store all of your website’s content, user profiles, comments, etc. The “P” in WAMP stands for PHP. PHP is the programming language that WordPress is written in. It is also the piece that holds the entire software stack together. It runs as a process in Apache and communicates with the MySQL database to dynamically build your webpages. Download the wamp for the url  http://www.wampserver.com/en/download.php .  You

Dynamic Sitemap Generation plugin in Cakephp

Here for the SEO implementation we need to generate the sitemap.xml in our application which is accessed by the webmaster tool. So here i am outlined the steps to generate the Xml file . 1. Lets think we have controller by name sitemap,Inside that create an action by name sitemap and paste the following code    public function sitemap(){     $this->layout='ajax';     $this->RequestHandler->respondAs('xml');     $listData = $this->Sitemap->find('all',/*Conditions if you have any*/);     $this->set(compact('listData')); } I through variable $listData to render all data( Keywords,Title,Url,etc ...) that will be shown in sitemap.xml.  This   depends   on the   dynamic link  what   we want to   show  in sitemap.xml.For request handler to work include the RequestHandler component public $components = array('RequestHandler'); Step 2. Create View based on action sitemap On structure MVC as default we need create sitemap.