Stored Procedure

1. Overview

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure so that the stored procedure can act based on the parameter value(s) that is passed.

1.1 Define Stored Procedure

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

1.2 Execute a Stored Procedure

EXEC procedure_name;

2. Advantages

To help you build powerful database applications, stored procedures provide several advantages including better performance, higher productivity, ease of use, and increased scalability.

2.1 Maintainability

• Because scripts are in one location, updates and tracking of dependencies based on schema changes becomes easier. You can group all the required SQL statements in a procedure and execute them at once.
• Once you compile a stored procedure you can use it in any number of applications. If any changes are needed you can just change the procedures without touching the application code.

2.2 Testing

Can be tested independent of the application

2.3 Isolation of Business Rules

Having Stored Procedures in one location means that there’s no confusion of having business rules spread over potentially disparate code files in the application

• Using procedures, you can avoid repetition of code moreover with these you can use additional SQL functionalities like calling stored functions.

• You can call PL/SQL stored procedures from Java and Java Stored procedures from PL/SQL

2.4 Speed / Optimization

• Stored procedures are cached on the server
• Execution plans for the process are easily reviewable without having to run the application
• Since stored procedures are compiled and stored, whenever you call a procedure the response is quick.
• Since procedures are stored on the database server which is faster than client. You can execute all the complicated quires using it, which will be faster.

2.5 Utilization of Set-based Processing

• The power of SQL is its ability to quickly and efficiently perform set-based processing on large amounts of data; the coding equivalent is usually iterative looping, which is generally much slower

2.6 Security

• Limit direct access to tables via defined roles in the database
• Provide an “interface” to the underlying data structure so that all implementation and even the data itself is shielded.
• Securing just the data and the code that accesses it is easier than applying that security within the application code itself

3. Disadvantages

3.1 Limited Coding Functionality

Stored procedure code is not as robust as app code, particularly in the area of looping (not to mention that iterative constructs, like cursors, are slow and processor intensive)

3.2 Portability

Complex Stored Procedures that utilize complex, core functionality of the RDBMS used for their creation will not always port to upgraded versions of the same database. This is especially true if moving from one database type (Oracle) to another (MS SQL Server).

3.3 Testing

Any data errors in handling Stored Procedures are not generated until runtime

3.4 Location of Business Rules

• Since SP’s are not as easily grouped/encapsulated together in single files, this also means that business rules are spread throughout different Stored Procedures. App code architecture helps to ensure that business rules are encapsulated in single objects.
• There is a general opinion that business rules / logic should not be housed in the data tier

3.5 Utilization of Set-based Processing

Too much overhead is incurred from maintaining Stored Procedures that are not complex enough. As a result, the general consensus is that simple SELECT statements should not be bound to Stored Procedures and instead implemented as inline SQL.

3.6 Cost

• Depending on your corporate structure and separation of concern for development, there is the potential that Stored Procedure development could potentially require a dedicated database developer. Some businesses will not allow developers access to the database at all, requiring instead a separate DBA. This will automatically incur added cost.
• Some companies believe (and sometimes it’s true, but not always) that a DBA is more of a SQL expert than an application developer, and therefore will write better Stored Procedures. In that case, an extra developer in the form of a DBA is required.

4. Alternatives to Stored Procedures

4.1 In-line or Parameterized Queries

These are written within the application code itself

4.2 Object Relational Mapping (ORM)

It provides an abstraction to the database without having to manually write data access classes. At this point, most major platforms offer some form of ORM software.

5. Reference

https://docs.oracle.com/cd/F49540_01/DOC/java.815/a64686/01_intr3.htm

https://www.quora.com/What-are-the-advantages-of-using-an-SQL-stored-procedures

https://www.tutorialspoint.com/what-are-the-advantages-of-stored-procedures

https://www.seguetech.com/advantages-and-drawbacks-of-using-stored-procedures-for-processing-data/