STORE PROCEDURE

A stored procedure is 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 acces 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 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

CALL procedure(…)

or

EXECUTE procedure(…)

Stored procedures can return result set, i.e. the results of a SELECT statement. Such result sets can be processed using cursor 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.
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 language, 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
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.

 from : wikipedia