Why we use stored procedures in application
Applications that use stored procedures have the following advantages:
1. Procedural Language Capability: –
PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops). Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
2. Stored Procedures are pre-compiled:-
Once created, that can be used again and again without doing compilation. That will save compilation time and get result in less time.
3. Reduced development cost and increased reliability:-
In a database application environment, many codes are repeated. Repeated work might include returning a fixed set of data, or performing the same set of multiple requests to a database. By reusing one common procedure, a procedure can provide a highly efficient way to address these recurrent situations.
4. Reduced network bandwidth between clients and servers
A client application passes control to a stored procedure on the db server. The stored procedure performs intermediate processing on the db server, without transmitting unused data across the network. Only the records that are actually required by the client application are transmitted. Using a stored procedure can result in reduced network bandwidth and better overall performance.
5. Enhanced hardware and software capabilities/Reuse business logic :-
User can share application logic between many application and module via procedure. Developer no need to fire queries again and again for same kind of work. In that way bug possibilities are very less. Applications that use stored procedures have access to increased memory and disk space on the server computer. These applications also have access to software that is installed only on the database server. You can distribute the executable business logic across machines that have sufficient memory and processors
6. Easy Troubleshooting:-
Stored procedures are modular. This is a good thing from a maintenance standpoint. When query trouble arises in your application, you would likely agree that it is much easier to troubleshoot a stored procedure than an embedded query buried within many lines of GUI code.
7. Improved security
By including database privileges with stored procedures that use static SQL, the database administrator (DBA) can improve security. The DBA or developer who builds the stored procedure must have the database rights that the stored procedure requires. Users of the client applications that call the stored procedure do not need such rights. This can reduce the number of users who require rights. Also developer can provide junior people to call privilege instead of edit It will keep some business logic confidential and secure.
8. Centralized security, administration, and maintenance for common routines
By managing shared logic in one place at the server, you can simplify security, administration, and maintenance. Client applications can call stored procedures that run SQL queries with little or no additional processing.