Stored Procedure

A stored procedure is a subroutine available to connected relational database system applications. Stored procedures must be called or invoked, as they are sets of SQL and programming commands that perform very specific functions. Most major relational database systems (e.g., SQL Server, Oracle, MySQL, Postgres and others) provide support for stored procedures.

This term is also known as proc or storedproc.

Stored procedures are used when an application needs to perform a complex task using relational database information. An example might be a loan loan application to determine a customer’s repayment ability and creditworthiness. To check the customer’s repayment ability, the loan officer compares the customer’s average monthly income to his monthly account withdrawal sum over a 24-month period. To verify creditworthiness, the loan officer submits the customer’s ID or social security number to a credit reporting website.

Both actions above are complex and difficult to achieve using basic SQL commands. In addition, the customer loan approval process may be performed at varying times for different customers (i.e., the same action is repeated several times), but different customer information is associated with each action.

The above situation is an ideal example of when storedprocs should be used--i.e., when performing a complex action using a combination of SQL, Procedural Language/Structured Query Language (PL/SQL) or external programming language, e.g., Java or C++. Second, the same action is performed repeatedly, and the only changes are the parameters or data to be processed.

Stored procedures usually provide a performance benefit over writing application code, for the following two reasons:

• They do not incur extra inter-program communication between the database and external application.
• Do not need to be compiled and executed for each instance, as storedprocs are compiled only once.
  

Stored procedures are stored as part of the database’s data dictionary, rather than the application that references the database. When storedprocs call other storedprocs, this is known as a setup of nested stored procedures.