A stored procedure (also termed proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP) is a
subroutine
In computer programming, a function or subroutine is a sequence of program instructions that performs a specific task, packaged as a unit. This unit can then be used in programs wherever that particular task should be performed.
Functions may ...
available to applications that access a
relational database management system
A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relatio ...
(RDBMS). Such procedures are stored in the database
data dictionary
A data dictionary, or metadata repository, as defined in the ''IBM Dictionary of Computing'', is a "centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format". ''Oracle'' defines it ...
.
Uses for stored procedures include
data-validation (integrated into the database) or
access-control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. To save time and memory, extensive or complex processing that requires execution of several
SQL statements can be saved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another.
Stored procedures may return
result set
An SQL result set is a set of rows from a database, as well as metadata about the query such as the column names, and the types and sizes of each column. Depending on the database system, the number of rows in the result set may or may not be kno ...
s, 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. Stored-procedure flow-control statements typically include
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.
Implementation
Stored procedures are similar to
user-defined function A user-defined function (UDF) is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment. UDFs are usually written for the requirement of its c ...
s (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.
CALL procedure(...)
or
EXECUTE procedure(...)
The exact and correct implementation of stored procedures varies from one database system to the other. Most major database vendors support them in some form. Depending on the database system, stored procedures can be implemented in a variety of
programming language
A programming language is a system of notation for writing computer programs. Most programming languages are text-based formal languages, but they may also be graphical. They are a kind of computer language.
The description of a programming ...
s, for example
SQL,
Java
Java (; id, Jawa, ; jv, ꦗꦮ; su, ) is one of the Greater Sunda Islands in Indonesia. It is bordered by the Indian Ocean to the south and the Java Sea to the north. With a population of 151.6 million people, Java is the world's List ...
,
C, or
C++
C++ (pronounced "C plus plus") is a high-level general-purpose programming language created by Danish computer scientist Bjarne Stroustrup as an extension of the C programming language, or "C with Classes". The language has expanded significan ...
. Stored procedures written in non-SQL languages may or may not execute SQL statements themselves.
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
SQL/PSM ( SQL/Persistent Stored Modules) is an ISO standard mainly defining an extension of SQL with a procedural language for use in stored procedures. Initially published in 1996 as an extension of SQL-92 (ISO/IEC 9075-4:1996, a version sometim ...
. That made SQL an
imperative programming language
In computer science, imperative programming is a programming paradigm of software that uses statements that change a program's state. In much the same way that the imperative mood in natural languages expresses commands, an imperative program co ...
. Most database systems offer proprietary and vendor-specific extensions, exceeding SQL/PSM. A standard specification for
Java stored procedure SQL/JRT, or ''SQL Routines and Types for the Java Programming Language'', is an extension to the SQL standard first published as ISO/IEC 9075-13:2002 (part 13 of SQL:1999). SQL/JRT specifies the ability to invoke static Java methods as routines fr ...
s exists as well as
SQL/JRT SQL/JRT, or ''SQL Routines and Types for the Java Programming Language'', is an extension to the SQL standard first published as ISO/IEC 9075-13:2002 (part 13 of SQL:1999). SQL/JRT specifies the ability to invoke static Java methods as routines fr ...
.
Comparison with static SQL
;Overhead: Because stored procedure statements are stored directly in the database, they ''may'' remove all or part of the compiling overhead that is typically needed in situations where software applications send inline (dynamic) SQL queries to a database. (However, most database systems implement ''statement caches'' and other methods to avoid repetitively compiling dynamic SQL statements.) Also, while they avoid some pre-compiled SQL, statements 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.
;Avoiding network traffic: A major advantage of stored procedures is that they can run directly within the
database engine
A database engine (or storage engine) is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database. Most database management systems include their own application ...
. 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 more important for complex series of SQL statements.
;Encapsulating business logic: Stored procedures allow programmers to embed
business logic In computer software, business logic or domain logic is the part of the program that encodes the real-world business rules that determine how data can be created, stored, and changed. It is contrasted with the remainder of the software that might ...
as an API in the database, which can simplify data management and reduce the need to encode the logic elsewhere in client programs. This can result in a lesser likelihood of data corruption by faulty client programs. The database system can ensure
data integrity
Data integrity is the maintenance of, and the assurance of, data accuracy and consistency over its entire Information Lifecycle Management, life-cycle and is a critical aspect to the design, implementation, and usage of any system that stores, proc ...
and
consistency
In classical deductive logic, a consistent theory is one that does not lead to a logical contradiction. The lack of contradiction can be defined in either semantic or syntactic terms. The semantic definition states that a theory is consistent ...
with the help of stored procedures.
;Delegating access-rights: In many systems, stored procedures can be granted access rights to the database that users who execute those procedures do not directly have.
;Some protection from SQL injection attacks: Stored procedures can be used to protect against injection attacks. Stored procedure parameters will be treated as data even if an attacker inserts SQL commands. Also, some DBMS will check the parameter's type. However, a stored procedure that in turn generates dynamic SQL using the input is still vulnerable to SQL injections unless proper precautions are taken.
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 A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example ...
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 allows database administrators 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.
Comparison with functions
* A function is a subprogram written to perform certain computations.
* A scalar function returns only one value (or NULL), whereas a table function returns a (relational) table comprising zero or more rows, each row with one or more columns.
* Functions must return a value (using the
RETURN
keyword), but for stored procedures this is not mandatory.
* Stored procedures can use
RETURN
keyword but with no value being passed.
* Functions could be used in
SELECT
statements, provided they do no data manipulation. However, procedures cannot be included in
SELECT
statements.
* A stored procedure can return multiple values using the
OUT
parameter, or return no value.
* A stored procedure saves the query compiling time.
* A stored procedure is a database object.
* A stored procedure is a material object.
Comparison with prepared statements
Prepared statement In database management systems (DBMS), a prepared statement, parameterized statement, or parameterized query is a feature used to pre-compile SQL code, separating it from data. Benefits of prepared statements are:
* efficiency, because they can b ...
s take an ordinary statement or query and parameterize it so that different literal values can be used at a later time. Like stored procedures, they are stored on the server for efficiency and provide some protection from SQL injection attacks. Although simpler and more declarative, prepared statements are not ordinarily written to use procedural logic and cannot operate on variables. Because of their simple interface and client-side implementations, prepared statements are more widely reusable between DBMS.
Comparison with smart contracts
Smart contract is a term applied to executable code stored on a
blockchain
A blockchain is a type of distributed ledger technology (DLT) that consists of growing lists of records, called ''blocks'', that are securely linked together using cryptography. Each block contains a cryptographic hash of the previous block, a ...
as opposed to an RDBMS. Despite the execution result consensus mechanisms of public blockchain networks differing in principle from traditional private or federated databases, they perform ostensibly the same function as stored procedures, albeit usually with a sense of value transaction.
Disadvantages
* Stored procedure languages are often vendor-specific. Changing database vendors usually requires rewriting existing stored procedures.
* Changes to stored procedures are harder to keep track of within a version control system than other code. Changes must be reproduced as scripts to be stored in the project history to be included, and differences in procedures can be harder to merge and track correctly.
* Errors in stored procedures cannot be caught as part of a compilation or build step in an application IDE - the same is true if a stored procedure went missing or was accidentally deleted.
* Stored procedure languages from different vendors have different levels of sophistication.
** For example, Postgres' pgpsql has more language features (especially via extensions) than Microsoft's T-SQL.
*Tool support for writing and debugging stored procedures is often not as good as for other programming languages, but this differs between vendors and languages.
** For example, both PL/SQL and T-SQL have dedicated IDEs and debuggers. PL/PgSQL can be debugged from various IDEs.
References
External links
Stored Procedures in MySQL FAQUsing a stored procedure in Sybase ASE
{{DEFAULTSORT:Stored Procedure
Database management systems
Subroutines