Advantages of using stored procedures

With stored procedures, you can avoid network transfer of large amounts of data that is obtained as part of intermediate results in a long sequence of queries. Additionally, you can deploy client database applications as client/server pieces.

Stored procedures written in embedded static SQL have the following additional advantages:

Performance: Static SQL is prepared at precompile time and has no run time overhead of access plan (package) generation.
Encapsulation (information hiding): Users do not must know the details about database objects in order to access them. Static SQL can help enforce this encapsulation.
Security: Users' access privileges are encapsulated within the packages associated with the stored procedures, so you are not required to grant explicit access to each database object. For example, you can grant a user run access for a stored procedure that selects data from tables for which the user does not have SELECT privilege.