Stored procedure

A stored procedure ( english stored procedure is) a function in database management systems , with the whole sequences of instructions from the database can be accessed client. It is thus an independent command that executes a sequence of stored commands. Stored procedures are stored in the data dictionary of the respective database.

General

By means of stored procedures, more frequently used processes that would otherwise be carried out by the client using many individual commands can be transferred to the database system and carried out with a single call ( CALLor EXECUTE) (see also client-server system ). Sometimes this increases the performance because less data has to be exchanged between the client and the database system and the database management system often runs on more powerful servers .

In addition to the usual syntax of the query language, mostly SQL , additional commands for flow control or evaluation of conditions can also be added to stored procedures. This means that they can be compared with the macro languages of certain application programs . The SQL used is often expanded to include manufacturer-specific functions. The use of other programming languages such as Java or C # is now partly possible.

Stored procedures help make an application much more secure. Because the client usually does not DELETE-, UPDATE- or INSERTneeded more Center access, it is not possible attackers, even databases to manipulate such. B. by SQL injection . The client only has the option of calling pre-defined procedures. In this way, software developers can avoid undesirable behavior from third parties.

“In these constructs, SQL injection attacks are almost impossible. However, there is still a theoretical chance. "

- Christopher Kunz : PHP Security (Page 143)

implementation

Database system	Implementation language
Db2	SQL PL
Firebird	PSQL (partly also Oracle's PL / SQL)
Informix	S. PL
Oracle	PL / SQL and Java
Microsoft SQL Server	Transact-SQL and various .NET Framework languages
MySQL	SQL: 2003
PostgreSQL	PL / pgSQL and many other own language constructs such as PL / Tcl, PL / Perl or PL / Python

Extended Stored Procedure

Extended Stored Procedure (abbreviation: XP ) is a form of the stored procedure that has been extended by Microsoft SQL . XPs allow more complex processes to be carried out through the use of DLLs . Depending on the Microsoft SQL version, various extended stored procedures are predefined, while administrators can add more.

Microsoft has now marked the use of XPs as deprecated , which means that future Microsoft SQL versions will no longer support extended procedures. However, Microsoft SQL Server 2012 still supports XPs. Microsoft names the use of the common language runtime environment as a replacement .

Examples

The following stored procedure in SQL has two parameters @VorlNr , @PersNr of the data type Integer

CREATE PROCEDURE GibVorlesungen
    @VorlNr INT,
    @PersNr INT
AS
    SELECT Vorlesung.VorlNr, Vorlesung.Titel, Professor.PersNr, Professor.Name
    FROM Professor INNER JOIN Vorlesung
    ON Professor.PersNr = Vorlesung.PersNr
    WHERE VorlNr = @VorlNr AND PersNr = @PersNr;

The call

EXECUTE GibVorlesungen 1001, 15;

could return the following result (see SQL view ):

Template no	title	PersNo	Surname
1001	Databases	15th	Vacationers

literature

Guy Harrison and Steven Feuerstein: MySQL Stored Procedure Programming . 1st edition. O'Reilly Media, 2006, ISBN 978-0-596-10089-6 , pp. 640 .
Informix Software: Informix Guide to SQL: Tutorial . 2nd Edition. Prentice Hall PTR, 1999, ISBN 978-0-13-016165-9 , pp. 350 .
Informix Software: Informix Guide to SQL: Reference and Syntax . 2nd Edition. Prentice Hall PTR, 1999, ISBN 978-0-13-016166-6 , pp. 1776 .

Web links

Firebird 2.0 Online Manual: Overview of PSQL Stored Procedures. Janus Software, accessed February 25, 2010 .
Peter Gulutzan: MySQL 5.0 Stored Procedures. (PDF; 588 kB) Oracle , May 2005, accessed on February 25, 2010 (English).
PostgreSQL Documentation: Procedural Languages. PostgreSQL Global Development Group, accessed February 25, 2010 .

Individual evidence

↑ Christopher Kunz, Stefan Esser: PHP security: Programming PHP / MySQL web applications securely . 3. Edition. dpunkt.verlag, Heidelberg 2008, ISBN 978-3-89864-535-5 .
↑ Informix Guide to SQL: Tutorial , Chapter 10, Creating & Using SPL Routines , p. 371
↑ Informix Guide to SQL: Reference and Syntax , Chapter 3, SPL Statements , p. 901
↑ MySQL 5.5 Reference Manual: CREATE PROCEDUREand CREATE FUNCTIONSyntax. Oracle , accessed February 25, 2010 .
↑ General Extended Stored Procedures (Transact-SQL). Microsoft MSDN
↑ Adding an Extended Stored Procedure to SQL Server. Microsoft MSDN
↑ Deprecated Database Engine Features in SQL Server 2012. Microsoft MSDN
^ Microsoft Docs: Create a Stored Procedure

[1] Christopher Kunz, Stefan Esser: PHP security: Programming PHP / MySQL web applications securely . 3. Edition. dpunkt.verlag, Heidelberg 2008, ISBN 978-3-89864-535-5 .

[2] Informix Guide to SQL: Tutorial , Chapter 10, Creating & Using SPL Routines , p. 371

[3] Informix Guide to SQL: Reference and Syntax , Chapter 3, SPL Statements , p. 901

[4] MySQL 5.5 Reference Manual: CREATE PROCEDUREand CREATE FUNCTIONSyntax. Oracle , accessed February 25, 2010 .

[5] General Extended Stored Procedures (Transact-SQL). Microsoft MSDN

[6] Adding an Extended Stored Procedure to SQL Server. Microsoft MSDN

[7] Deprecated Database Engine Features in SQL Server 2012. Microsoft MSDN

[8] Microsoft Docs: Create a Stored Procedure