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 ( CALL
or 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 INSERT
needed 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. "
implementation
Database system | Implementation language |
---|---|
Db2 | SQL PL |
Firebird | PSQL (partly also Oracle's PL / SQL) |
Informix | S. |
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 PROCEDURE
andCREATE FUNCTION
Syntax. 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