All about Mysql Stored Procedure..

Join us on Facebook 

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database and can be invoked by program or trigger or stored procedure itself. You can say, stored procedure is way to execute your business logic directly into database server.

Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version.

In stored procedure you can perform your some of generic tasks which are dependent to 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. By this Stored procedures reduce network traffic and improve performance.

e.g Suppose we have normal banking system & we want to update interest of loan of any specific user. For this, we need to fetch capital, rate of interest and duration and calculate interest and then we will go back to database and save data.

If we use stored procedure, then we need to write all operation within our stored procedure and we will call it through the program one time. For this particular case, we will fetch capital, rate of interest and duration within database(using stored procedure) and save data after processing. By this, we are only interrupting database server one time, which will in turn reduce server overhead.

Advantage of Stored Procedure:
1). By using stored procedure you can make your business logic independent of programming language.
2). When security is main concern use of stored procedure is vital. By doing your operation through the database you can log your all performed action.
3). If we are using stored procedure, then we do not have to access table directly which is one more way to secure the data and transaction.
4). Stored procedure increases performance of our application. When stored procedure created it compile and when we call it, it will never go to parser, instead it directly execute and fetch the record whereas normal SQL query fired on database server get parse every time so using stored procedure your can save parsing time.
5). If application is big or database server on remote system then by using stored procedure we can decrease the traffic between our database server and application server.
6). Since stored procedure is written in our database server and application call it separately then the degree of re-usability increase because despite to going in much detail we can call stored procedure to perform our action.

Disadvantages of Stored Procedure:
1). Sometime using stored procedure is bit risky. As it follows the principle of "define one use many time", by changing the stored procedure directly effect your data so it should always be use with very carefully.
2). Stored procedure are set of sql command form our logic, so sometime programmer need to debug the stored procedure. In mysql, it's very hard to debug the stored procedure.
3). Managing stored procedure is bit difficult as it does not have any object oriented paradigm like things.

e.g:
Let's take a simple example, which will simple select one string.
mysql > create procedure helloTest() Select 'hello';

Above sql statement will create a stored procedure in mysql system with name helloTest. To call this procedure execute below query:
mysql > CALL heloTest();

This will run select 'hello'.

Above stored procedure is quite simple, now we will take learn to create some more complex stored procedures. Before that let's take a quick overview of how to create a lil complex stored procedures.

1). Definition of the procedure: Create procedure syntax with the name
2). Definition of the parameters: 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. There are three type of parameter you can define. in parameter, out parameter and inout parameter.
2.1). IN parameter you can define inputs of the stored procedure,
2.2). OUT parameter you can specify the output parameter.
2.3). INOUT define shared parameter, it can be used either as input parameter or output parameter.
3). Third is the body of the procedure. Normally we write within the BEGIN and END tag.

e.g:
DELIMITER //
CREATE  PROCEDURE sp_test (IN capital DOUBLE , IN rate INT , IN duration INT , OUT interest DOUBLE)
BEGIN
SET interest = (capital * rate * duration)/100;
INSERT INTO audit_table(interest) VALUES(interest);
END //
DELIMITER ;
(We have first changed delimiter as // to mark end of stored procedure, and then reverted it back to previous delimiter.)

This will create a stored procedure with name sp_test, it take capital , rate and duration as input and return the interest in output variable after calculating it, it also insert the interest into database.

Let's run it.
mysql > call test(500 , 1.3 , 3 , @out_interest);
mysql > select @out_interest;

@out_interest will give you the interest calculated by the stored procedure 'sp_test'. In above case 'select @out_interest;' will return '19.5'.

We can use input parameter in the query written within stored procedure. e.g:

DELIMITER //
CREATE PROCEDURE new_sp_test (IN order_id VARCHAR(255))
BEGIN
SELECT * FROM Order WHERE Order.order_id = order_id;
END
END //
DELIMITER ;

##Variable Declaration in stored procedure: We can declare variables inside a stored procedure after BEGIN tag. In mysql we use DECLARE tag for declaring a variable. We have to specify the data type of the variable to declare the variable.

Scope of the variable in mysql is limited to END tag, that means after END tag variable will be lost.
e.g:
DECLARE i INT(3)
DECLARE j INT(9) DEFAULT 6;

We can initialize the variable after declaration, e.g:
DECLARE i INT(3);
SET i = 10;

In mysql we can take he value in the variable from the query also from the help of INTO keyword. e.g:
DECLARE orderId VARCHAR(23);
SELECT order.order_id INTO orderId FROM order;

##Condition statement in Mysql programming will give us power to execute code on the basis of the some value. e.g:
DECLARE order_count INT(5) default 0;
SELECT count(*) INTO order_count FROM order;
IF order_count > 5 THEN
    SELECT * FROM order;
ELSE
    SELECT 'Less than 5 orders';
END IF;

Always remember, for every IF statement in Mysql we have to specify END IF statement. In mysql we can also use ELSEIF for the recursive statements. e.g:
DECLARE order_count INT(5) default 0;
SELECT count(*) INTO order_count FROM order;
IF order_count =0 THEN
    SELECT 'No orders present';
ELSEIF order_count > 5 THEN
    SELECT * FROM order;
ELSE
    SELECT 'Less than 5 orders';
END IF;

We can also use switch case based conditional statement in mysql. e.g:

CASE
WHEN i > 2 THEN
    SELECT 'greater then two';
WHEN i < 2 THEN
    Select 'less then two';
ELSE
    SELECT 'other case';
END CASE;

Every CASE must be closed with END CASE.

-K Himaanshu Shukla...

Copyright © 2014 - ScrutinyByKHimaanshu