A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a sproc or SP) are actually stored in the database. In computer science, a subroutine (function, method, procedure, or subprogram) is a portion of code within a larger program, which performs a specific task and is relatively independent of the remaining code. ... A relational database is a database that conforms to the relational model, and refers to a databases data and schema (the databases structure of how that data is arranged). ... A database management system (DBMS) is computer software designed for the purpose of managing databases. ... This article does not cite any references or sources. ...

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures and all applications call the procedures only. In computer science, data validation is the process of ensuring that a program operates on clean, correct and useful data. ... In security, specifically physical security, the term access control refers to the practice of restricting entrance to a property, a building, or a room to authorized persons. ...

Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement A User Defined Function, or UDF, is an extension to an SQL database server (like MySQL) that can be compiled for use in an SQL query. ...

 CALL procedure(…) 

Stored procedures can return result sets, i.e. the results of a SELECT statement. Such result sets can be processed using cursors by other stored procedures by associating a result set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. The standard Structured Query Language provides IF, WHILE, LOOP, REPEAT, and CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared. An SQL result set is a set of rows from a database, as well as meta-information about the query such as number of results returned and the column names. ... In database packages, the term cursor refers to a control structure for the successive traversal (and potential processing) of records in a result set as returned by a query. ...

Implementation

The exact and correct implementation of stored procedure varies from one database system to another. Most major database vendors support them in some form. Depending on the database system, stored procedures can be implemented in a variety of programming languages, for example SQL, Java, or C and C++. Stored procedures written in non-SQL programming languages may or may not execute SQL statements themselves. A programming language is an artificial language that can be used to control the behavior of a machine, particularly a computer. ... SQL (IPA: or IPA: ), commonly expanded as Structured Query Language, is a computer language designed for the retrieval and management of data in relational database management systems, database schema creation and modification, and database object access control management. ... “Java language” redirects here. ... C is a general-purpose, block structured, procedural, imperative computer programming language developed in 1972 by Dennis Ritchie at the Bell Telephone Laboratories for use with the Unix operating system. ... For a WikiBook on programming with C++, see Wikibooks: C++ Programming. ...

The increasing adoption of stored procedures led to the introduction of procedural elements to the SQL language in the SQL:1999 and SQL:2003 standards in the part SQL/PSM. That made SQL an imperative programming language. Most database systems offer proprietary and vendor-specific extensions, exceeding SQL/PSM. For example, Microsoft SQL Server allows for stored procedures to be written using Transact-SQL; Oracle calls its dialect PL/SQL, DB2 uses SQL/PL , PostgreSQL provides PL/pgSQL and also allows users to define their own function languages such as pl/perl or pl/php, and MySQL supports their own stored procedures that try to adhere closely to the SQL:2003 standard. In computer science, imperative programming, as opposed to declarative programming, is a programming paradigm that describes computation in terms of a program state and statements that change the program state. ... Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. ... Transact-SQL (TSQL) is an enhanced version of the SQL relational database language. ... The term Oracle database may refer either to the database management system (DBMS) software released by Oracle Corporation as Oracle RDBMS, or to any of the individual databases managed by such software. ... PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporations proprietary server-based extension to the SQL database language, and emulates the Ada programming language. ... DB2 is one of IBMs lines of relational database management system (or, as IBM now calls it, data server) software products within IBMs broader Information Management Software line. ... PostgreSQL is a free software object-relational database management system (ORDBMS), released under a BSD-style license. ... PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language) is a procedural language built into the PostgreSQL RDBMS. It closely resembles Oracles PL/SQL language. ... MySQL (pronounced (IPA) , my S Q L) is a multithreaded, multi-user SQL database management system (DBMS)[1] which has, according to MySQL AB, more than 10 million installations. ...

Advantages

A variety of advantages can be obtained through the use of stored procedures.

Pre-compilation of SQL statements

SQL statements implemented as stored procedures in some cases run faster, as they can be pre-compiled. Execution plans for compiled statements can be stored in the database, together with the procedure. This can remove the compilation overhead that is typically required in situations where software applications send inline SQL queries to a database. However, most database systems implement statement caches to avoid repetitive compilation of dynamic SQL statements.

In addition, pre-compiled SQL statements, while avoiding some overhead, add to the complexity of creating an optimal execution plan because not all arguments of the SQL statement are supplied at compile time. Depending on the specific database implementation and configuration, mixed performance results will be seen from stored procedures versus generic queries or user defined functions .

Execution on a database server

Stored procedures can run directly within the database engine. In a production system, this typically means that the procedures run entirely on a specialized database server, which has direct access to the data being accessed. The benefit here is that network communication costs can be avoided completely. This becomes particularly important for complex series of SQL statements.

Simplification of data management

Stored procedures allow for business logic to be embedded as an API in the database, which can simplify data management and reduce the need to encode the logic elsewhere in client programs. This may result in a lesser likelihood of data becoming corrupted through the use of faulty client programs. Thus, the database system can ensure data integrity and consistency with the help of stored procedures. Business Logic is a non-technical term generally used to describe the functional algorithms which handle information exchange between a database and a user interface. ...

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. However, the use of stored procedures does not preclude the use of a business layer. TG

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 transparent to them. Stored procedures can also be invoked from a database trigger or a condition handler. For example, a stored procedure may be triggered by an insert on a specific table, or update of a specific field in a table, and the code inside the stored procedure would be executed. Writing stored procedures as condition handlers also allow DBAs to track errors in the system with greater detail by using stored procedures to catch the errors and record some audit information in the database or an external resource like a file. A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. ...

External links

• A guide to Stored Procedures using MySQL
• An overview of PostgreSQL Procedural Language support
• Using a stored procedure in Sybase ASE
• An overview of Stored Procedures using MS SQL