Advantages and Disadvantages of Stored Procedure

Advantages of stored procedures:

the procedures/functions are stored in the database and are, therefore, executed on the database server which is likely to me more powerful than the clients which in turn means that stored procedures should run faster;
the code is stored in a pre-compiled form which means that it is syntactically valid and does not need to be compiled at run-time, thereby saving resources;
each user of the stored procedure/function will use exactly the same form of queries which means the queries are reused thereby reducing the parsing overhead and improving the scalability of applications;
as the procedures/functions are stored in the database there is no need to transfer the code from the clients to the database server or to transfer intermediate results from the server to the clients. This results in much less network traffic and again improves scalability;
when using PL/SQL packages, as soon as one object in the package is accessed, the whole package is loaded into memory which makes subsequent access to objects in the package much faster
stored procedures/functions can be compiled into “native” machine code making them even faster (available with Oracle 9i and above)

Disadvantages:

there is an overhead involved in switching from SQL to PL/SQL, this may be significant in terms of performance but usually this overhead is outweighed by performance advantages of using PL/SQL
more memory may be required when using packages as the whole package is loaded into memory as soon as any object in the package is accessed
native compilation can take twice as long as normal compilation

Despite the advantages listed above, there are some situations where the use of stored procedures is not recommended or may be infeasible.

Disadvantages

Applications that involve extensive business logic and processing could place an excessive load on the server if the logic was implemented entirely in stored procedures. Examples of this type of processing include data transfers, data traversals, data transformations and intensive computational operations. You should move this type of processing to business process or data access logic components, which are a more scalable resource than your database server.

Do not put all of your business logic into stored procedures. Maintenance and the agility of your application becomes an issue when you must modify business logic in T-SQL. For example, ISV applications that support multiple RDBMS should not need to maintain separate stored procedures for each system.

Writing and maintaining stored procedures is most often a specialized skill set that not all developers possess. This situation may introduce bottlenecks in the project development schedule.