Why do we need stored routines?

In this multi-part series I will explain the need of stored routines commonly know as stored procedures and functions , how to manage them, how to declare variables, programming blocks (loops, how to perform condition checks etc) .

The first part talks about why should we create stored routines.

What are stored routines?

Stored Routines are set of SQL statements that can be stored in the database. They can consist of variables required to perform those operations, call to other stored procedures, call to in-built stored routines of the database.

Why do we need a stored routine in any database ? That too when there so many programming languages to take care of the requirements by interacting with the database

The answer to this is very simple :

1. In a scenario where the system is to be so designed that no one can access the tables directly for example in banking applications.
   Through this approach the tables names, their structures, relationships are all well hidden and what the end developer needs to know is few details like name of stored procedure/function, parameters required and in case of functions what it will return.
2. When multiple applications are being built around the same database and they routinely perform the same operations. Stored routines help in maintaining the integrity of these operations and the data also.
   It also saves developers effort of writing the same business rules again and again.
3. Stored routines allow developers to access Database Libraries, which might reduce the development effort because if written outside database there might be a need to create these functions.
4. Application performance improves but at the cost of database performance

In the next part we will see how to manage stored routines.

Next:
Manage Stored Routines