Today I want to share the wisdom I acquired during instructor led training on the topic of stored procedures. Most of the information about procedures applies to functions as well. PL/SQL code that needs to be reused is not normally contained in an anonymous block. Instead it is placed in a named block such as a procedure.A benefit of using procedures has to do with how the code is cached in the database,. The SGA is the area of the database which holds your code,. It uses a least recently used aging process to keep frequent code in memory. If a procedure is used often, it will most likely stay in memory and be faster to call. You also have the option to manually pin objects down in the SGA for performance reasons.
Oracle applications such as forms also contain procedures. However unlike other procedures, the code for these procedures does not reside in the database. They reside with the application. In the example of the forms application, the forms stored procedures are housed in the forms application server.
You create a procedure using DDL. Starting in Oracle 10g, the procedure compilation process will give out warnings as well as errors if there are any. These warning are non critical recommendations to make changes that affect performance for example.
A stand-alone procedure’s name must be unique within the schema. Procedures can optionally take a number of parameters. There are rules governing the type and use of these parameters. I will cover that in a future post.
