Stored procedures are a collection of SQL, PL/SQL, and Java statements that allow a programmer to shift the code from the application side to the database side. This enables the business rules and application logic to be stored as procedures within the database. The main advantages of using stored procedures and functions are as follows:
The complex business rules processing is performed within the database on the server side. Therefore, this change of processing from the application on the client side to the database on the server side enhances and improves the performance of the application.
The procedural code stored within the database can be reused anywhere within the application.
When a stored procedure is executed for the first time, the shared SQL area in the System Global Area (SGA) of the database stores the parsed versions of the executed commands. After this, whenever the command is executed, it takes the advantage of the parsing, which was performed the first time, and improves the performance of the procedure's execution.
a Developmental efforts are reduced by keeping the code in a stored procedure. As the business rules are part of the database, they need not be re-written in each code module of the application; and therefore, saves the creation time of the application.
As the code is stored in a common place, any change can be easily managed; and therefore, reduces the maintenance effort.
The complex business rules processing is performed within the database on the server side. Therefore, this change of processing from the application on the client side to the database on the server side enhances and improves the performance of the application.
The procedural code stored within the database can be reused anywhere within the application.
When a stored procedure is executed for the first time, the shared SQL area in the System Global Area (SGA) of the database stores the parsed versions of the executed commands. After this, whenever the command is executed, it takes the advantage of the parsing, which was performed the first time, and improves the performance of the procedure's execution.
a Developmental efforts are reduced by keeping the code in a stored procedure. As the business rules are part of the database, they need not be re-written in each code module of the application; and therefore, saves the creation time of the application.
As the code is stored in a common place, any change can be easily managed; and therefore, reduces the maintenance effort.