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.
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
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
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.
The increasing adoption of stored procedures led to the introduction of procedural elements to the SQL language in the and 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 standard
ed ex1.sql create or replace procedure myproc(a in number,b in number)as add number; begin add:=a+b; dbms_output.put_line('The sum is'||add); end; /
Calling the procedure:
ed ex2.sqlNow run the proc1 and then run the proc2 like
declare x number:=&a; y number:=&b; begin myproc(x,y); end; /
@proc1.sqlafter executing the proc1 myproc will be created.
@proc2.sqlyou will get the result.
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.
However, note that unnecessary or excessive procedural statement execution in the database server (typically a singular shared resource) may impair overall enterprise system performance - i.e., while application servers can often be dramatically scaled horizontally for increased processing capacity, the same is not generally or as easily accomplished for database servers.
Therefore, a growing school of thought advocates the database be used for what it's best at - i.e., a very efficient file cabinet. Thereby restricting any database-local procedural executions to only very specific cases rather than the old school ubiquity - instead advocating the use of advanced object oriented domain class ontologies and reusable parameterized SQL generation.
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. This allows the client program to access the database system as a non-privileged, or restricted user, whereby preserving role based access and achieving more complete abstraction of application code from database code.