Stored procedure

Stored procedure

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 statementfact|date=February 2008

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.

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 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

Example1.

ed ex1.sqlcreate or replace procedure myproc(a in number,b in number)asadd number;beginadd:=a+b;dbms_output.put_line('The sum is'||add);end;/

Calling the procedure:

ed ex2.sql

declarex number:=&a;y number:=&b;beginmyproc(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.

Features

Statements can be stored in the database, together with the procedure. This can remove the compilation overhead that is typically required in situations where software applications send inline SQL queries to a database. However, most database systems implement statement caches to avoid repetitive compilation of dynamic SQL statements.

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.

Execution on a database server

Stored procedures 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.

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.

Security

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.

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.

References

External links

* [http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx MySQL Stored Procedures step by step tutorial]
* [http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html A guide to Stored Procedures using MySQL]
* [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://databases.about.com/od/sqlserver/a/storedprocedure.htm An overview of Stored Procedures using MS SQL]
* [http://www.sqlpl-guide.com Guide for developing Stored Procedures in DB2]


Wikimedia Foundation. 2010.

Игры ⚽ Поможем написать курсовую

Look at other dictionaries:

  • Stored Procedure — Der Begriff Gespeicherte Prozedur (GP) oder englisch Stored Procedure (SP) bezeichnet eine Funktion bestimmter Datenbankmanagementsysteme. In einer Stored Procedure können ganze Abläufe von Anweisungen unter einem Namen gespeichert werden, die… …   Deutsch Wikipedia

  • stored procedure — noun A named program or routine stored in a database. I think the overflow bug is in your stored procedure …   Wiktionary

  • Stored procedure — Хранимая процедура  объект базы данных, представляющий собой набор DDL, так и Содержание 1 Реализация хранимых процедур 2 Назначение и преимущества хранимых процедур[2] …   Википедия

  • Java stored procedure — A Java stored procedure is a set of SQL statements, written in the Java programming language, grouped together as an executable unit.A stored procedure is a program that is kept and executed within a database server. The procedure is called from… …   Wikipedia

  • Procedure stockee — Procédure stockée Une procédure stockée (ou stored procedure en anglais) est un ensemble d instructions SQL pré compilées, stockées sur le serveur, directement dans la base de données. Elles peuvent être exécutées sur demande : lancées par… …   Wikipédia en Français

  • Stored Procedures — Der Begriff Gespeicherte Prozedur (GP) oder englisch Stored Procedure (SP) bezeichnet eine Funktion bestimmter Datenbankmanagementsysteme. In einer Stored Procedure können ganze Abläufe von Anweisungen unter einem Namen gespeichert werden, die… …   Deutsch Wikipedia

  • Procédure stockée — En informatique, dans la technologie des bases de données, une procédure stockée (ou stored procedure en anglais) est un ensemble d instructions SQL pré compilées, stockées dans une base de données et exécutées sur demande par le SGBD qui… …   Wikipédia en Français

  • Java stored procedure — noun a set of SQL statements, developed in Java, grouped together as an executable unit …   Wiktionary

  • Electronically Stored Information — (ESI) is any type of information that can be stored electronically, including all current types of computer based information as well as any that might occur as a result of future changes and technological developments.Examples of Electronically… …   Wikipedia

  • Keyword Services Platform — The Keyword Services Platform (KSP) is a keyword research tool available through Microsoft adCenter, which contains a set of algorithms for providing information about keywords used in search engine queries.The KSP was originally conceived by… …   Wikipedia

Share the article and excerpts

Direct link
Do a right-click on the link above
and select “Copy Link”