A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, 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. 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 CALL statement[citation needed]
CALL procedure(…)
or
EXECUTE 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, 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. 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
Example1.
ed proc1.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 proc2.sql declare x number:=&a; y number:=&b; begin myproc(x,y); end; /
Now run the proc1 and then run the proc2 like
@proc1.sql
after executing the proc1 myproc will be created.
@proc2.sql
you will get the result.
[edit] Features
[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 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.
[edit] References
 |
This article does not cite any references or sources. Please help improve this article by adding citations to reliable sources. Unverifiable material may be challenged and removed. (October 2007) |
[edit] External links
- Multiple Queries in a Stored Procedure
- MySQL Stored Procedures step by step tutorial
- 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
- Guide for developing Stored Procedures in DB2
- PL/SQL Procedures
|
||||||||||||||||||||