Definitions for procedures appear in the database, separately from any one database application. This separation provides a number of advantages.
Procedures standardize actions performed by more than one application program. By coding the action once and storing it in the database for future use, applications need only call the procedure to achieve the desired result repeatedly. And since changes occur in only one place, all applications using the action automatically acquire the new functionality if the implementation of the action changes.
Procedures used in a network database server environment can access data in the database without requiring network communication. This means they execute faster and with less impact on network performance than if they had been implemented in an application on one of the client machines.
When you create a procedure, it is automatically checked for correct syntax, and then stored in the system tables. The first time any application calls a procedure, it is compiled from the system tables into the virtual memory of the server and executed from there. Since one copy of the procedure remains in memory after the first execution, repeated executions of the same procedure happen instantly. As well, several applications can use a procedure concurrently, or one application can use it recursively.
Procedures provide security by allowing users limited access to data in tables that they cannot directly examine or modify.
Procedures, including user-defined functions, execute with the permissions of the procedure owner but can be called by any user that has been granted permission to do so.
This means that a procedure can (and usually does) have different permissions than the user ID that invoked it. Procedures provide security by allowing users limited access to data in tables that they cannot directly examine or modify.
