Procedures
A Procedure is an executable object in the database server. In general, it consists of one or more SQL statements that have been precompiled. They are usually referred to as stored procedures or stored functions. A first attempt has been made to standardize procedure handling in RDBMS systems. The standard refers to procedures as persistent stored modules.
The main advantage of a procedure is that SQL statements are moved from the application to the database server. The application only needs to call the procedure to execute the SQL statements. Procedures are usually fast because the statements are precompiled and no time is needed to create access plans. They are commonly used to store business rules. Furthermore, procedures can make an application portable. The specific SQL code may vary on different database servers; as long as the procedure definition (its arguments and return values) does not change, the application still works.
The main disadvantage of a procedure is that SQL statements are moved from the application to the database server. A procedure must be written and compiled for every DBMS on which the calling application runs. Many DBMSs do not support procedures. The grammar to create a procedure is not standardized. Even though you can call procedures in a standard way, there is no standard creation mechanism.
The decision to use procedures must be made with the application environment in mind. If, for example, we are creating a vertical application that must run on any DBMS flavor, procedures should not be used. If, on the other hand, we are creating an in-house application on a DBMS that supports procedures, the use of procedures is more likely.
Procedures can have arguments that can be input, output, or input/output parameters. Input parameters are used to pass information to a procedure. Output parameters are used to pass information back to the calling application. Input/Output parameters are used to pass information to the procedure and, after the procedure has been called, pass information back to the calling application. It is also possible for a procedure to have a return value.
The Embedded SQL interface defines a complete set of messages to handle procedures. This set consists of: