A stored procedure is a subroutine in a relational database system. It's stored in the data dictionary and is available to applications that can access the database. Common functions performed by stored procedures include access control and data validation. A stored procedure centralizes code that exists in multiple applications, making it easier to maintain. Stored procedure can call other stored procedures, which allows them to be nested.
A user-defined function (UDF) is similar to a stored procedure, except that a UDF can be used like an expression in a Structured Query Language (SQL) statement. A stored procedure is invoked with the CALL statement. A stored procedure can return the result of a SELECT statement, also known as a result set. An application can then process the result set with a cursor.
Stored procedures can declare cursors to move through a result set, and they also can declare variables to process data. SQL provides these capabilities through commands such as WHILE, LOOP and REPEAT. A stored procedure can also modify variables, receive variables and return variables.
Most database systems support stored procedures, although the specific implementation depends on the database system. Some database systems allow stored procedures to be implemented in programming languages such as C++ or Java. Stored procedures written in these languages might not be able to execute SQL statements.
Some database systems can use stored procedures to control transaction management. They also can run stored procedures inside a transaction, so that the transaction is transparent to the stored procedure. A condition handler can invoke a stored procedure, which allows database administrators to track errors. A database trigger such as an insert or update can also invoke a stored procedure.
Stored procedures can eliminate much of the compilation overhead of inline, or dynamic, SQL statements. Dynamic SQL generally needs to be compiled, although most databases use statement caching to avoid the repeated compilation of dynamic SQL. Stored procedures are already stored directly in the database and therefore don't need to be compiled. This also allows stored procedures to reduce network traffic, because they run inside the database.
This advantage is somewhat offset by the fact that stored procedures make it more difficult to optimize an execution plan. Some arguments in the stored procedure call aren't available at compilation time, which can impair the performance of stored procedures. The impact that stored procedures have on performance depends on the configuration and implementation of the database.
