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.

For 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 thevariable 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.

Source: CodeWebber