What is a Stored Procedure?

A stored procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like Transact-SQL for Microsoft SQL Server, PL/SQL for Oracle database, or PL/pgSQL for PostgreSQL. MySQL also supports stored procedures. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server and is generally faster at processing database requests. The database server has direct access to the data it needs to manipulate and only needs to send the final results back to the user, doing away with the overhead of communicating potentially large amounts of interim data back and forth.

Typical uses for stored procedures include data validation which is integrated into the database structure (stored procedures used for this purpose are often called triggers), or encapsulating an API for some large or complex processing (such as manipulating a large dataset to produce a summarised result). A stored procedure that runs a (possibly complex) series of queries will often run faster as a stored procedure than if it had been implemented as, for example, a program running on a client computer which communicates with the database by submitting the SQL queries one by one, receiving results from them, and then deciding what other queries may need to be run. By having the logic run inside the database engine, this eliminates numerous context switches and a great deal of network traffic.

Stored procedures can also simplify data management when a database is manipulated from many external programs. Embedding "business logic" in the database using stored procedures eliminates the need to duplicate the same logic in each of the programs which accesses the data. This simplifies the creation and maintenance of the programs involved, and can help avoid the data corruption that can be introduced if one or another of those external systems fails to have the validation logic upgraded properly.

In some systems, stored procedures can be used to control transaction management; in others, stored procedures run inside a transaction such that transactions are effectively invisible to them.

In most systems, there is some notion of compilation of stored procedures. This commonly has to do with the database engine determining some query plan that will be reused over and over when the procedure is called. The more extensive (and expensive) the set of optimizations that the compiler applies, the more worthwhile the reuse can be expected to be.

About this Terminology
This terminology is from The Wikipedia which is published under the GNU Free Documentation License.