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 PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. 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. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of 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 some large or complex processing (such as manipulating a large dataset to produce a summarised result). The latter will often run faster as a stored procedure than if it had been implemented as, for example, a program running on a client computer and communicating with the database by sending SQL queries and receiving results. Stored procedures can also be a good thing when a database is manipulated from many external programs. By embedding "business logic" in the database using stored procedures, the need to embed the same logic in all the programs which access the data is reduced. This can simplify the creation and, particularly, the maintenance of the programs involved.
|
|
