Stored procedure
From Free net encyclopedia
Template:Cleanup-date A stored procedure (sometimes called a sproc) is a program (or procedure) which is physically stored within a database. The exact implementation of a stored procedure varies from one database to another. In most cases however, stored procedures allow for an API to be defined for a database, rather than having a client application interact with the tables and other database objects directly.
Stored procedures are supported by most major database vendors in some form, but there is debate amongst developers about the advantages of using stored procedures. Some people use them frequently, while others prefer to avoid using them at all.
Typical uses for stored procedures include data validation, integrated into the database structure. Stored procedures used for this purpose are often called triggers. Another common use is the encapsulation of an API for some large or complex processing that might require the execution of several SQL queries, such as manipulating a large dataset to produce a summarised result.
Contents |
Implementation
Stored procedures are often, but not always, used as a method for executing SQL queries on the database objects in a way abstracted from a client application. Although variations of SQL tend to be the primary languages supported by database vendors, many systems provide language extensions that allow for developers to write scripts that are more complex than than what can be easily expressed using SQL alone. For some databases, dedicated and complex imperative programming languages have been adapted to be used for writing stored procedures.
Most database systems that support stored procedures do so using proprietary languages, or languages that are otherwise specific to the vendor. For example, Microsoft SQL Server allows for stored procedures to be written using Transact-SQL. Oracle supports PL/SQL, PostgreSQL supports PL/pgSQL, and MySQL supports stored procedures that adhere closely to the SQL:2003 standard.
Advantages and trade-offs
A variety of advantages can be obtained through the use of stored procedures.
Pre-compilation of queries
Queries implemented as stored procedures will run faster in many circumstances, as they can be pre-compiled with the query plan being largely calculated once, rather than every time the query is submitted. This removes the SQL compilation overhead that is typically required in situations where software applications send inline SQL queries to a database.
Pre-compilation also means that some database systems can produce inefficient query plans for stored procedures, because some information is typically unavailable at the time that the query plan is generated. The design of an efficiently optimised query plan can vary greatly, depending on factors such as the specific variables being used in a query, and statistics about data in the tables that a query accesses. If this information is not fully available at the time of compilation, as is often the case with stored procedures, the pre-compiled query plans may result in very bad execution times compared with queries that are compiled at runtime, with all relevant information available. Some critics also argue that a database system can cache the compiled form of previously submitted SQL queries, meaning that explicitly pre-compiling queries may not be as useful.
Execution on a specialised server
Stored procedures are run directly within the database engine. In a production system, this typically means that the procedures run entirely on a specialised database server, which has direct access to the data being accessed, and may be faster at processing database requests.
Locality of program execution
A stored procedure, which may be composed of a complex series of queries, will often run faster combined 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. With a client involved, the client would typically need to request intermediate results, and react to them in turn.
By having complex logic run inside the database engine via a stored procedure, numerous context switches and a great deal of network traffic can be eliminated. The database server 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.
Simplification of data management
Stored procedures allow for business logic to be embedded as an API in the database, which can simplify data management. By providing an API that implements business logic within the database using stored procedures, the need to duplicate logic within client programs is lessened or eliminated. If managed appropriately, this may result in a lesser likelihood of data becoming corrupted through the use of client programs that are out of date, or that have not been updated as intended.
Some critics claim that databases should be for storing data only, and that business logic should only be implemented by writing a business layer of code, through which client applications should access the data.
Security
Carefully written stored procedures may allow for fine grained security permissions to be applied to a database. For example, client programs might be restricted from accessing the database via any means except those that are provided by the available stored procedures.
Other uses
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.
See also
Related Links
A guide to Stored Procedures using MySQLde:Stored Procedure es:Procedimientos almacenados fr:Procédure stockée it:Stored procedure nl:Opgeslagen procedure pl:Procedura składowana pt:Procedimento armazenado (banco de dados) ru:Хранимая процедура