Stored Procedures: Advantages

When accessing a SQL database such as Oracle there are generally two ways of implementing your data access code, either by creating the SQL statements as a string and passing them to the database from your application, or by using stored procedures. A stored procedure on Oracle is created using the language PL/SQL. A stored procedure is an executable piece of code thats sits within the database and is called by your application, it is generally created and written outside of your application using tools supplied by the database vendor.

I am convinced that stored procedures offer valuable benefits over creating the SQL within your application. For future projects I will be spending far more time with stored procedures than I do currently. Below I have listed the main reasons that I believe stored procedures (in many circumstances) are the more beneficial and efficient way of working:

PL/SQL Code is Stored within the Database
This means that the code once written and installed within the database is available to all users of that database, in addition performance is likely to be considerably better than code sent from the application.

PL/SQL Makes it Easy to Process Huge Amounts of Data
Using the ‘cursor for’ loop you can query large amounts of database rows and cycle through them iteratively.

PL/SQL Allows you to Abstract the Logic away from the Application
By putting logic in the database we can ensure that even people who access the database without your client application will still have the data processed or presented according to the system requirements. This feature often requires the use of Triggers.

PL/SQL Supports Packages
A Package is a logical group of procedures or functions that work together. As they are all loaded into memory together performance is enhanced. Standard procedures are only loaded into memory when called.

and my personal favourite:

PL/SQL Code is Portable
The PL/SQL code can be deployed on any platform on which Oracle runs, so a procedure developed on a Unix or Linux based system can be moved over to run on a Win2K3 based system with no alterations. Try doing that if the logic is written in VB.NET instead !

Other DBA’s and Developers may have additional reasons that they use PL/SQL, the above list is my opinion only of the features that I consider the most important to me when considering PL/SQL.

Live Long and Prosper - Dr Spock