Editing Stored procedure

{{Short description|Subroutine available to applications that access relational database management systems}}
{{refimprove|date=June 2024}}
A '''stored procedure''' (also termed '''prc''', '''proc''', '''storp''', '''sproc''', '''StoPro''', '''StoredProc''', '''StoreProc''', '''sp''', or '''SP''') is a [[subroutine]] available to applications that access a [[relational database management system]] (RDBMS). Such procedures are stored in the database [[data dictionary]].

Uses for stored procedures include [[data validation|data-validation]] (integrated into the database) or [[access control|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]]s, i.e., the results of a <code>SELECT</code> statement.  Such result sets can be processed using [[Cursor (databases)|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 <code>IF</code>, <code>WHILE</code>, <code>LOOP</code>, <code>REPEAT</code>, and <code>CASE</code> 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]]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 <code>CALL</code> statement.<ref>{{Cite web |title=Db2 12 - Application programming and SQL - Calling a stored procedure from your application |url=https://www.ibm.com/docs/en/db2-for-zos/12?topic=zos-calling-stored-procedure-from-your-application |access-date=2022-05-26 |website=www.ibm.com |language=en-us}}</ref>

 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]]s, for example [[SQL]], [[Java (programming language)|Java]], [[C (programming language)|C]], or [[C++]]. 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]]. That made SQL an [[imperative programming language]]. Most database systems offer proprietary and vendor-specific extensions, exceeding SQL/PSM. A standard specification for [[Java stored procedure]]s exists as well as [[SQL/JRT]].

{| class="wikitable sortable"
|-
! Database system
! Implementation language
|-
| [[CUBRID]]
| [[Java (programming language)|Java]]
|-
| [[IBM Db2]]
| [[SQL PL]] (close to the [[SQL/PSM]] standard) or [[Java (programming language)|Java]]
|-
| [[Firebird (database server)|Firebird]]
| PSQL (Fyracle also supports portions of Oracle's PL/SQL)
|-
| [[Informix]]
| [[Java (programming language)|Java]]
|-
| [[Interbase]]
| Stored Procedure and Trigger Language
|-
| [[Microsoft SQL Server]]
| [[Transact-SQL]] and various [[.NET Framework]] languages
|-
| [[MySQL]], [[MariaDB]]
| own stored procedures, closely adhering to [[SQL/PSM]] standard
|-
| [[NuoDB]]
| [[SQL]] or [[Java (programming language)|Java]]
|-
| [[Virtuoso Universal Server|OpenLink Virtuoso]]
| Virtuoso SQL Procedures (VSP);<ref name="SQL Procedure Language Guide">{{cite web|url=http://docs.openlinksw.com/virtuoso/ch-sqlprocedures/|title=Chapter 11. SQL Procedure Language Guide|website=OpenLink documentation|access-date=11 September 2019}}</ref> also extensible via Java, C, and other programming languages
|-
| [[Oracle database|Oracle]]
| [[PL/SQL]] or [[Java (programming language)|Java]]
|-
| [[PostgreSQL]]
| [[PL/pgSQL]], can also use own function languages such as PL/Tcl, PL/Perl or PL/Python<ref>{{cite web |title=Chapter 42. Procedural Languages |url=https://www.postgresql.org/docs/current/xplang.html |website=PostgreSQL Documentation |access-date=20 November 2023 |language=en |date=9 November 2023}}</ref>
|-
| [[SAP HANA]]
| [https://help.sap.com/viewer/de2486ee947e43e684d39702027f8a94/2.0.03/en-US/297af2926307446cbbfb1a8f96fec941.html SQLScript] or [[R (programming language)|R]]
|-
| [[SAP Adaptive Server Enterprise|SAP ASE]]
| [[Transact-SQL]]
|-
| [[SAP SQL Anywhere]]
| [[T-SQL]], [[Watcom SQL]], [[Java (software platform)|Java]], or [[C (programming language)|C]]/[[C++]])
|-
| [[SQLite]]
| Not supported
|}

==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 when software applications send inline (dynamic) SQL queries to a database. (However, most database systems implement ''statement [[Cache (computing)|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]]. In a production system, this typically means that the procedures run entirely on a specialized database server with direct access to the data. The benefit is that it saves network costs, which stands out when a series of SQL statements are involved.

;Encapsulating business logic: Stored procedures allow programmers to embed [[business logic]] 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]] and [[Data consistency|consistency]] 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]] 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 <code>RETURN</code> keyword), but for stored procedures this is not mandatory.
* Stored procedures can use <code>RETURN</code> keyword but with no value being passed.
* Functions could be used in <code>SELECT</code> statements, provided they do no data manipulation. However, procedures cannot be included in <code>SELECT</code> statements.
* A stored procedure can return multiple values using the <code>OUT</code> 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]]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]] 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.
*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 ==
{{reflist}}

== External links ==
*[http://dev.mysql.com/doc/refman/5.7/en/faqs-stored-procs.html Stored Procedures in MySQL FAQ]
*[http://www.postgresql.org/docs/current/interactive/xplang.html An overview of PostgreSQL Procedural Language support]
*[http://www.petersap.nl/SybaseWiki/index.php/Stored_procedure Using a stored procedure in Sybase ASE]
*[http://infolab.stanford.edu/~ullman/fcdb/oracle/or-plsql.html#procedures PL/SQL Procedures]
*[http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/toc.htm Oracle Database PL/SQL Language Reference]
*[https://help.sap.com/viewer/de2486ee947e43e684d39702027f8a94/2.0.03/en-US/297af2926307446cbbfb1a8f96fec941.html SAP HANA SQLScript Reference]
{{databases}}

{{DEFAULTSORT:Stored Procedure}}
[[Category:Database management systems]]
[[Category:Subroutines]]