Filter
Top Products
Benefits of procedures
230
Benefits of procedures
Procedures are defined in the database, separate from any one database
application. This separation provides a number of advantages.
Standardization
Procedures allow standardization of any actions that are performed by more
than one application program. The action is coded once and stored in the
database. The applications need only call the procedure to achieve the desired
result. If the implementation of the action evolves over time, any changes are
made in only one place, and all applications that use the action automatically
acquire the new functionality.
Efficiency
When used in a database implemented on a network server, procedures are
executed on the database server machine. They can access the data in the
database without requiring network communication. This means that 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 a procedure is created, it is checked for correct syntax and then stored in
the system tables. The first time it is required by any application, it is retrieved
from the system tables and compiled into the virtual memory of the server, and
executed from there. Subsequent executions of the same procedure will result
in immediate execution, since the compiled copy is retained. A procedure can
be used concurrently by several applications and recursively by one
application. Only one copy is compiled and kept in virtual memory.
Security
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.
Introduction to procedures
In order to use procedures, you need to understand how to do the following:
• Call procedures from a database application
• Create procedures
• Drop, or remove, procedures