Stored procedure
 |
This article does not cite any references or sources. Please help improve this article by adding citations to reliable sources. Unsourced material may be challenged and removed. (December 2009) |
A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, StoredProc, or SP) are actually stored in the database data dictionary.
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. Extensive or complex processing that requires the execution of several SQL statements is moved 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 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.[1]
CALL procedure(…)
or
EXECUTE procedure(…)
Stored procedures may 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, 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.
Contents |
[edit] 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, C, or C++. Stored procedures written in non-SQL programming 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. That made SQL an imperative programming language. Most database systems offer proprietary and vendor-specific extensions, exceeding SQL/PSM.
| Database System | Implementation Language |
|---|---|
| Microsoft SQL Server | Transact-SQL and various .NET Framework languages |
| Oracle | PL/SQL or Java |
| DB2 | SQL/PL or Java |
| Informix | SPL |
| PostgreSQL | PL/pgSQL, can also use own function languages such as pl/perl or pl/php |
| Firebird | PSQL (Fyracle also supports portions of Oracle's PL/SQL) |
| MySQL | own stored procedures, closely adhering to SQL:2003 standard. |
[edit] 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 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.
[edit] Comparison with dynamic SQL
Overhead: Because stored procedure statements are stored directly in the database, they may remove all or part of the compilation overhead that is typically required in situations where software applications send inline (dynamic) SQL queries to a database. (However, most database systems implement "statement caches" and other mechanisms to avoid repetitive compilation of dynamic SQL statements.) In addition, while they avoid some overhead, 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.
Avoidance of network traffic: A major advantage with stored procedures is that they 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.
Encapsulation of business logic: 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. The database system can ensure data integrity and consistency with the help of stored procedures.
Delegation of access-rights: In many systems, stored-procedures can be granted access rights to the database which the users who will 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 DBMSs will check the parameter's type.
[edit] Comparison with functions
- A function is a subprogram written to perform certain computations and return a single value.
- Functions must return a value (using the
RETURNkeyword), but for stored procedures this is not compulsory. - Stored procedures can use
RETURNkeyword but without any value being passed. - Functions could be used in
SELECTstatements, provided they don’t do any data manipulation. However, procedures cannot be included inSELECTstatements. - A function can have only
INparameters, while stored procedures may haveOUTorINOUTparameters. - A stored procedure can return multiple values using the
OUTparameter or return no value at all.
[edit] Disadvantages
Stored procedures are "defined once, used many times." If any changes are necessary, the (one and only one) definition of the stored procedure must be replaced. Dynamic SQL, of course, allows any SQL query to be issued at any time. Any change to a stored procedure instantly impacts every other piece of software, report, etc. (inside or outside of the DBMS) which directly or indirectly refers to it. It is not always possible to determine with certainty exactly what those impacts will be, nor what changes can safely be made without adversely impacting something else.
For various reasons, many organizations strictly limit who is allowed to define and issue a query against the database. Programmers and other users may therefore find themselves having no choice but to implement inefficient solutions to their problems using what stored procedures are available to them, whether or not the procedures are appropriate for this particular ancillary task.
Though not directly related to stored procedures, the movement of business logic to the DBMS is problematic since it is the layer with the more complex scalability issues. Furthermore, some modern DBMS systems (notably from Microsoft SQL Server 2000 onwards) don't offer any performance benefits of using stored procedures against precompiled queries: they are compiled and cached in the same manner as dynamic SQL.[citation needed]
[edit] Reference
[edit] External links
- A guide to Stored Procedures using MySQL
- An overview of PostgreSQL Procedural Language support
- Using a stored procedure in Sybase ASE
- PL/SQL Procedures
|
|||||||||||||||||
