Oracle stored procedures and triggers are faster than traditional code, which leads to an increase in popularity. As application code moves away from external programs and into the database engine, DBAs need to understand the related memory requirements for Oracle stored procedures and know how to manage stored procedures for optimal database performance.
There are many compelling benefits to putting all Oracle SQL inside stored procedures, including:
* Better performance. Oracle stored procedures load once into the shared pool and remain there unless they become paged out. Subsequent executions of the stored procedure are far faster than executions of external code.
* Coupling of data with behavior. DBAs can use naming conventions to couple relational tables with the behaviors associated with a table (using Oracle stored procedures as "methods"). If all behaviors associated with the employee table are prefixed with the table name--employee.hire, employee.give_raise, for example--the data dictionary can be queries to list all behaviors associated with a table (select * from dba_objects where owner = 'EMPLOYEE'), and it's easy to identify and reuse code.
* Isolation of code. Since all SQL is moved out of the external programs and into the Oracle stored procedures, the application programs become nothing more than calls to stored procedures. As such, it becomes very simple to swap out one database and swap in another one.
Read more about using stored procedures here:
http://oracle-tips.c.topica.com/maamAC1abMvSPbLGJrib/
Tips for Using Oracle Stored Procedures
