Gespeicherte Prozeduren (Datenbank-Engine)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Eine gespeicherte Prozedur in SQL Server ist eine Gruppe von einer oder mehreren Transact-SQL-Anweisungen oder einem Verweis auf eine Microsoft .NET Framework-CLR-Methode (Common Language Runtime). Prozeduren sind mit Konstrukten anderer Programmiersprachen vergleichbar, da sie folgende Fähigkeiten aufweisen:

  • Annehmen von Eingabeparametern und Zurückgeben mehrerer Werte in Form von Ausgabeparametern an das aufrufende Programm.

  • Aufnehmen von Programmierungsanweisungen, die Vorgänge in der Datenbank ausführen. Dies schließt auch den Aufruf anderer Prozeduren ein.

  • Zurückgeben eines Statuswerts an ein aufrufendes Programm, der Erfolg oder Misserfolg (sowie die Ursache für den Misserfolg) anzeigt.

Vorteile bei der Verwendung gespeicherter Prozeduren

In der folgenden Liste sind einige Vorteile bei der Verwendung von Prozeduren beschrieben.

Verringerter Netzwerkdatenverkehr zwischen Server und Client

Die Befehle in einer Prozedur werden als einzelner Codebatch ausgeführt. Dadurch lässt sich der Netzwerkdatenverkehr zwischen Server und Client erheblich reduzieren, da nur der Aufruf zum Ausführen der Prozedur über das Netzwerk gesendet wird. Wenn der Code nicht von einer Prozedur gekapselt würde, müsste jede einzelne Codezeile über das Netzwerk übertragen werden.

Mehr Sicherheit

Mithilfe einer Prozedur können mehrere Benutzer und Clientprogramme Vorgänge für zugrunde liegende Datenbankobjekte ausführen, selbst wenn die Benutzer und Programme keine direkten Berechtigungen für diese zugrunde liegenden Objekte aufweisen. Die Prozedur steuert, welche Prozesse und Aktivitäten ausgeführt werden, und schützt die zugrunde liegenden Datenbankobjekte. Dadurch ist es nicht erforderlich, Berechtigungen auf der individuellen Objektebene zu gewähren, was zu einer Vereinfachung der Sicherheitsebenen führt.

Die EXECUTE AS -Klausel kann in der CREATE PROCEDURE-Anweisung angegeben werden, damit das Annehmen der Identität eines anderen Benutzers unterstützt wird, oder um Benutzern bzw. Anwendungen das Ausführen bestimmter Datenbankaktivitäten zu ermöglichen, ohne dass sie über direkte Berechtigungen für die zugrunde liegenden Objekte und Befehle verfügen. Beispielsweise weisen bestimmte Aktionen wie etwa TRUNCATE TABLE keine erteilbaren Berechtigungen auf. Um TRUNCATE TABLE auszuführen, benötigt der Benutzer für die festgelegte Tabelle die ALTER-Berechtigungen. Die Erteilung der ALTER-Berechtigungen für eine Tabelle kann sich als nicht ideal erweisen, weil der Benutzer dann Berechtigungen besitzt, die über die Möglichkeit hinausgehen, die Tabelle abzuschneiden. Durch die Aufnahme der TRUNCATE TABLE-Anweisung in ein Modul und durch die Festlegung, dass das Modul als Benutzer mit der Berechtigung zur Tabellenänderung ausgeführt wird, können Sie die Berechtigungen zum Abschneiden der Tabelle auf alle Benutzer ausdehnen, denen Sie die EXECUTE-Berechtigungen für das Modul erteilen.

Wenn eine Anwendung eine Prozedur über das Netzwerk aufrufrt, ist nur der Aufruf zum Ausführen der Prozedur sichtbar. Daher haben böswillige Benutzer keine Möglichkeit, die Namen von Tabellen- und Datenbankobjekten einzusehen, eigene Transact-SQL-Anweisungen einzubetten oder wichtige Daten zu suchen.

Die Verwendung von Prozedurparametern bietet Schutz vor Angriffen durch Einschleusung von SQL-Befehlen. Da die Parametereingabe als Literalwert und nicht als ausführbarer Code behandelt wird, ist es für einen Angreifer schwieriger, einen Befehl in die Transact-SQL-Anweisungen innerhalb der Prozedur einzufügen und die Sicherheit zu gefährden.

Prozeduren können verschlüsselt werden und helfen, den Quellcode zu verbergen. Weitere Informationen finden Sie unter SQL Server Encryption.

Wiederverwendung von Code

Code für wiederholte Datenbankvorgänge ist besonders für die Kapselung in Prozeduren geeignet. Dies hat folgende Vorteile: Derselbe Code muss nicht ständig neu geschrieben werden, Codeinkonsistenzen werden verringert, und alle Benutzer oder Anwendungen, die über die notwendigen Berechtigungen verfügen, können auf den Code zugreifen und diesen ausführen.

Einfachere Wartung

Wenn Clientanwendungen Prozeduren aufrufen und Datenbankvorgänge auf die Datenebene beschränkt bleiben, müssen bei Änderungen in der zugrunde liegenden Datenbank nur die Prozeduren aktualisiert werden. Die Anwendungsebene bleibt von der Datenebene getrennt und ist von Änderungen an Datenbanklayouts, Beziehungen oder Prozessen nicht betroffen.

Verbesserte Leistung

Eine Prozedur wird standardmäßig bei der ersten Ausführung kompiliert. Gleichzeitig wird ein Ausführungsplan erstellt, der für nachfolgende Ausführungen wiederverwendet wird. Da vom Abfrageprozessor kein neuer Plan erstellt werden muss, wird für die Verarbeitung der Prozedur normalerweise weniger Zeit benötigt.

Wenn es an den Tabellen oder Daten, auf die die Prozedur verweist, umfangreichere Änderungen gibt, wird die Prozedur aufgrund des vorkompilierten Plans möglicherweise langsamer ausgeführt. In diesem Fall kann die Leistung durch eine Neukompilierung der Prozedur und eine erzwungene Neuerstellung des Ausführungsplans verbessert werden.

Arten von gespeicherten Prozeduren

Benutzerdefiniert

Eine benutzerdefinierte Prozedur kann in einer benutzerdefinierten Datenbank sowie in allen Systemdatenbanken außer der Resource-Datenbank erstellt werden. Die Prozedur kann entweder in Transact-SQL oder als Verweis auf eine Microsoft .NET Framework-CLR-Methode (Common Language Runtime) entwickelt werden.

Temporäre Prozeduren

Temporäre Prozeduren stellen eine Art benutzerdefinierter Prozedur dar. Temporäre Prozeduren sind wie eine dauerhafte Prozedur, mit der Ausnahme, dass sie in tempdb gespeichert werden. Es gibt zwei Arten von temporären Prozeduren: lokale und globale temporäre Prozeduren. Sie unterscheiden sich hinsichtlich ihrer Namen, ihrer Sichtbarkeit und ihrer Verfügbarkeit. Die Namen lokaler temporärer Prozeduren beginnen mit einem einzelnen Nummernzeichen (#). Sie sind nur im Rahmen der aktuellen Benutzerverbindung sichtbar und werden gelöscht, sobald die Verbindung getrennt wird. Die Namen globaler temporärer Prozeduren beginnen mit zwei Nummernzeichen (##). Nachdem sie erstellt wurden, sind sie für jeden Benutzer sichtbar und werden am Ende der letzten Sitzung, in der die Prozedur verwendet wird, gelöscht.

System

Systemprozeduren sind in der Datenbank-Engine enthalten. Sie werden physisch in der internen, ausgeblendeten Resource-Datenbank gespeichert und erscheinen logisch im sys-Schema jeder systemdefinierten und benutzerdefinierten Datenbank. Außerdem verfügt die msdb-Datenbank über gespeicherte Systemprozeduren im dbo-Schema, die zum Planen von Warnungen und Aufträgen verwendet werden. Da Systemprozeduren mit dem Präfix sp_ beginnen, wird davon abgeraten, dieses Präfix beim Benennen benutzerdefinierter Prozeduren zu verwenden. Eine vollständige Liste der systemgespeicherten Prozeduren finden Sie unter Systemgespeicherte Prozeduren (Transact-SQL).

SQL Server unterstützt die Systemprozeduren, die eine Schnittstelle von SQL Server zu externen Programmen für verschiedene Wartungsaktivitäten bereitstellen. Diese erweiterten Prozeduren verwenden das Präfix xp_. Eine vollständige Liste der erweiterten Prozeduren finden Sie unter Gespeicherte allgemeine erweiterte Prozeduren (Transact-SQL).

Erweitert benutzerdefiniert

Mit erweiterten gespeicherten Prozeduren können externe Routinen in einer Programmiersprache wie C erstellt werden. Diese Prozeduren sind DLLs, die von einer Instanz von SQL Server dynamisch geladen und ausgeführt werden können.

Hinweis

Die erweiterten gespeicherten Prozeduren werden in einer zukünftigen Version von SQL Server entfernt. Verwenden Sie dieses Feature nicht in einer neuen Entwicklungsarbeit, und ändern Sie Anwendungen, die dieses Feature verwenden, so schnell wie möglich. Erstellen Sie stattdessen CLR-Prozeduren. Diese Methode bietet eine robustere und sicherere Alternative zum Schreiben von erweiterten Prozeduren.

Aufgabenbeschreibung Artikel
Beschreibt, wie eine gespeicherte Prozedur erstellt wird. Erstellen einer gespeicherten Prozedur
Beschreibt, wie eine gespeicherte Prozedur geändert wird. Ändern einer gespeicherten Prozedur
Beschreibt, wie eine gespeicherte Prozedur gelöscht wird. Löschen einer gespeicherten Prozedur
Beschreibt, wie eine gespeicherte Prozedur ausgeführt wird. Ausführen einer gespeicherten Prozedur
Beschreibt, wie Berechtigungen für eine gespeicherte Prozedur erteilt werden. Erteilen von Berechtigungen für eine gespeicherte Prozedur
Beschreibt, wie Daten von einer gespeicherten Prozedur an eine Anwendung zurückgegeben werden. Zurückgeben von Daten aus einer gespeicherten Prozedur
Beschreibt, wie eine gespeicherte Prozedur neu kompiliert wird. Erneutes Kompilieren einer gespeicherten Prozedur
Beschreibt, wie eine gespeicherte Prozedur umbenannt wird. Umbenennen einer gespeicherten Prozedur
Beschreibt, wie die Definition einer gespeicherten Prozedur angezeigt wird. Anzeigen der Definition einer gespeicherten Prozedur
Beschreibt, wie die Abhängigkeiten von einer gespeicherten Prozedur angezeigt werden. Anzeigen der Abhängigkeiten einer gespeicherten Prozedur
Beschreibt, wie Parameter in einer gespeicherten Prozedur verwendet werden. Parameter

Zugehöriger Inhalt