Gespeicherte Prozeduren in T-SQL – Erstellung, Änderung, Löschung. Erstellen gespeicherter Prozeduren in Microsoft SQL Server SQL erstellt eine gespeicherte Prozedur

Gespeicherte Prozedur ist eine spezielle Art von Transact-SQL-Anweisungspaket, das mit der SQL-Sprache und prozeduralen Erweiterungen erstellt wurde. Der Hauptunterschied zwischen einem Paket und einer gespeicherten Prozedur besteht darin, dass letztere als Datenbankobjekt gespeichert wird. Mit anderen Worten: Gespeicherte Prozeduren werden auf der Serverseite gespeichert, um die Leistung und Konsistenz wiederholbarer Aufgaben zu verbessern.

Die Datenbank-Engine unterstützt gespeicherte Prozeduren und Systemprozeduren. Gespeicherte Prozeduren werden auf die gleiche Weise erstellt wie alle anderen Datenbankobjekte, d. h. unter Verwendung der DDL-Sprache. Systemprozeduren werden von der Datenbank-Engine bereitgestellt und können für den Zugriff auf und die Änderung von Informationen im Systemkatalog verwendet werden.

Wenn Sie eine gespeicherte Prozedur erstellen, können Sie eine optionale Liste von Parametern definieren. Auf diese Weise akzeptiert die Prozedur bei jedem Aufruf die entsprechenden Argumente. Gespeicherte Prozeduren können einen Wert mit benutzerdefinierten Informationen oder im Fehlerfall eine entsprechende Fehlermeldung zurückgeben.

Die gespeicherte Prozedur wird vorkompiliert, bevor sie als Objekt in der Datenbank gespeichert wird. Die vorkompilierte Form der Prozedur wird in der Datenbank gespeichert und bei jedem Aufruf verwendet. Diese Eigenschaft gespeicherter Prozeduren bietet den wichtigen Vorteil, dass sie (in fast allen Fällen) wiederholte Prozedurkompilierungen eliminiert und entsprechende Leistungsverbesserungen erzielt. Diese Eigenschaft gespeicherter Prozeduren wirkt sich auch positiv auf die Datenmenge aus, die zwischen Datenbanksystem und Anwendungen ausgetauscht wird. Insbesondere der Aufruf einer gespeicherten Prozedur mit einer Größe von mehreren tausend Bytes erfordert möglicherweise weniger als 50 Bytes. Wenn mehrere Benutzer sich wiederholende Aufgaben mithilfe gespeicherter Prozeduren ausführen, kann der kumulative Effekt dieser Einsparungen erheblich sein.

Gespeicherte Prozeduren können auch für folgende Zwecke verwendet werden:

um ein Protokoll von Aktionen mit Datenbanktabellen zu erstellen.

Die Verwendung gespeicherter Prozeduren bietet ein Maß an Sicherheitskontrolle, das weit über die Sicherheit hinausgeht, die durch die Verwendung von GRANT- und REVOKE-Anweisungen bereitgestellt wird, die Benutzern unterschiedliche Zugriffsrechte gewähren. Dies ist möglich, weil die Berechtigung zum Ausführen einer gespeicherten Prozedur unabhängig von der Berechtigung zum Ändern der in der gespeicherten Prozedur enthaltenen Objekte ist, wie im nächsten Abschnitt beschrieben.

Gespeicherte Prozeduren, die Protokolle von Tabellenschreib- und/oder Lesevorgängen erstellen, bieten eine zusätzliche Option für die Datenbanksicherheit. Mit solchen Verfahren kann der Datenbankadministrator Änderungen überwachen, die von Benutzern oder Anwendungsprogrammen an der Datenbank vorgenommen werden.

Gespeicherte Prozeduren erstellen und ausführen

Gespeicherte Prozeduren werden mithilfe einer Anweisung erstellt VERFAHREN ERSTELLEN, die die folgende Syntax hat:

CREATE PROC proc_name [((@param1) type1 [ VARYING] [= default1] )] (, ...) AS Batch | EXTERNER NAME method_name Syntaxkonventionen

Der Parameter schema_name gibt den Namen des Schemas an, der vom Eigentümer der erstellten gespeicherten Prozedur zugewiesen wird. Der Parameter proc_name gibt den Namen der gespeicherten Prozedur an. Der Parameter @param1 ist ein Prozedurparameter (formales Argument), dessen Datentyp durch den Parameter type1 bestimmt wird. Prozedurparameter sind innerhalb der Prozedur lokal, ebenso wie lokale Variablen innerhalb des Pakets lokal sind. Prozedurparameter sind Werte, die vom Aufrufer an die Prozedur zur Verwendung in dieser übergeben werden. Der Parameter default1 gibt den Standardwert für den entsprechenden Prozedurparameter an. (Der Standardwert kann auch NULL sein.)

OUTPUT-Option gibt an, dass ein Prozedurparameter ein Rückgabeparameter ist und verwendet werden kann, um einen Wert von einer gespeicherten Prozedur an die aufrufende Prozedur oder das aufrufende System zurückzugeben.

Wie bereits erwähnt, wird die vorkompilierte Form einer Prozedur in der Datenbank gespeichert und bei jedem Aufruf verwendet. Wenn die gespeicherte Prozedur aus irgendeinem Grund bei jedem Aufruf kompiliert werden muss, verwenden Sie bei der Deklaration der Prozedur MIT RECOMPILE-Option. Durch die Verwendung der Option WITH RECOMPILE wird einer der wichtigsten Vorteile gespeicherter Prozeduren zunichte gemacht: die Leistungsverbesserung aufgrund einer einzelnen Kompilierung. Daher sollte die Option WITH RECOMPILE nur verwendet werden, wenn die von der gespeicherten Prozedur verwendeten Datenbankobjekte häufig geändert werden.

EXECUTE AS-Klausel Definiert den Sicherheitskontext, in dem die gespeicherte Prozedur nach ihrem Aufruf ausgeführt werden soll. Durch Festlegen dieses Kontexts kann die Datenbank-Engine die Auswahl von Benutzerkonten steuern, um Zugriffsberechtigungen für die Objekte zu überprüfen, auf die die gespeicherte Prozedur verweist.

Standardmäßig können nur Mitglieder der festen Serverrolle „sysadmin“ und der festen Datenbankrollen „db_owner“ oder „db_ddladmin“ die CREATE PROCEDURE-Anweisung verwenden. Mitglieder dieser Rollen können dieses Recht jedoch über die Anweisung an andere Benutzer vergeben GRANT-ERSTELLUNGSVERFAHREN.

Das folgende Beispiel zeigt, wie Sie eine einfache gespeicherte Prozedur für die Arbeit mit der Projekttabelle erstellen:

USE SampleDb; GO ERSTELLEN PROZEDUR ErhöhungBudget (@percent INT=5) AS UPDATE Projekt SET Budget = Budget + Budget * @percent/100;

Wie bereits erwähnt, verwenden Sie zum Trennen zweier Pakete GO-Anweisungen. Die CREATE PROCEDURE-Anweisung kann nicht mit anderen Transact-SQL-Anweisungen im selben Batch kombiniert werden. Die gespeicherte Prozedur „ErhöhenBudget“ erhöht die Budgets für alle Projekte um einen bestimmten Prozentsatz, der durch den Parameter „@percent“ bestimmt wird. Die Prozedur definiert außerdem einen Standardprozentwert (5), der verwendet wird, wenn dieses Argument bei der Ausführung der Prozedur nicht vorhanden ist.

Gespeicherte Prozeduren können auf nicht vorhandene Tabellen zugreifen. Mit dieser Eigenschaft können Sie Prozedurcode debuggen, ohne zuerst die entsprechenden Tabellen zu erstellen oder eine Verbindung zum Zielserver herzustellen.

Im Gegensatz zu primären gespeicherten Prozeduren, die immer in der aktuellen Datenbank gespeichert werden, ist es möglich, temporär gespeicherte Prozeduren zu erstellen, die immer in der temporären Systemdatenbank tempdb gespeichert werden. Ein Grund für die Erstellung temporär gespeicherter Prozeduren kann sein, die wiederholte Ausführung einer bestimmten Gruppe von Anweisungen beim Herstellen einer Verbindung zu einer Datenbank zu vermeiden. Sie können lokale oder globale temporäre Prozeduren erstellen. Dazu wird der Name der lokalen Prozedur mit einem einfachen #-Zeichen (#proc_name) und der Name der globalen Prozedur mit einem doppelten Zeichen (##proc_name) angegeben.

Eine lokal temporär gespeicherte Prozedur kann nur von dem Benutzer ausgeführt werden, der sie erstellt hat, und nur, während er mit der Datenbank verbunden ist, in der sie erstellt wurde. Eine globale temporäre Prozedur kann von allen Benutzern ausgeführt werden, jedoch nur so lange, bis die letzte Verbindung, auf der sie ausgeführt wird (normalerweise die Verbindung des Erstellers der Prozedur), beendet wird.

Der Lebenszyklus einer gespeicherten Prozedur besteht aus zwei Phasen: ihrer Erstellung und ihrer Ausführung. Jede Prozedur wird einmal erstellt und viele Male ausgeführt. Die gespeicherte Prozedur wird mit ausgeführt EXECUTE-Anweisungen ein Benutzer, der Eigentümer einer Prozedur ist oder über die EXECUTE-Berechtigung für den Zugriff auf diese Prozedur verfügt. Die EXECUTE-Anweisung hat die folgende Syntax:

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] value | [@parameter1=] @variable ] | DEFAULT).. Syntaxkonventionen

Mit Ausnahme des Parameters return_status haben alle Parameter der EXECUTE-Anweisung dieselbe logische Bedeutung wie dieselben Parameter der CREATE PROCEDURE-Anweisung. Der Parameter return_status gibt eine Ganzzahlvariable an, die den Rückgabestatus der Prozedur speichert. Ein Wert kann einem Parameter entweder über eine Konstante (Wert) oder eine lokale Variable (@variable) zugewiesen werden. Die Reihenfolge der Werte benannter Parameter ist nicht wichtig, aber die Werte unbenannter Parameter müssen in der Reihenfolge angegeben werden, in der sie in der CREATE PROCEDURE-Anweisung definiert sind.

DEFAULT-Klausel stellt den Standardwert für einen Prozedurparameter bereit, der in der Prozedurdefinition angegeben wurde. Wenn eine Prozedur einen Wert für einen Parameter erwartet, für den kein Standardwert definiert wurde und der Parameter fehlt oder das Schlüsselwort DEFAULT angegeben ist, tritt ein Fehler auf.

Wenn die EXECUTE-Anweisung die erste Anweisung eines Stapels ist, kann das Schlüsselwort EXECUTE weggelassen werden. Es ist jedoch sicherer, dieses Wort in jedes Paket aufzunehmen. Die Verwendung der EXECUTE-Anweisung wird im folgenden Beispiel gezeigt:

USE SampleDb; EXECUTE CreatingBudget 10;

Die EXECUTE-Anweisung in diesem Beispiel führt die gespeicherte Prozedur CreatingBudget aus, die das Budget aller Projekte um 10 % erhöht.

Das folgende Beispiel zeigt, wie eine gespeicherte Prozedur zum Verarbeiten von Daten in den Tabellen „Employee“ und „Works_on“ erstellt wird:

Die Beispielprozedur „ModifyEmpId“ veranschaulicht die Verwendung gespeicherter Prozeduren als Teil des Prozesses zur Aufrechterhaltung der referenziellen Integrität (in diesem Fall zwischen den Tabellen „Employee“ und „Works_on“). Eine ähnliche gespeicherte Prozedur kann innerhalb einer Triggerdefinition verwendet werden, die tatsächlich referenzielle Integrität bietet.

Das folgende Beispiel zeigt die Verwendung einer OUTPUT-Klausel in einer gespeicherten Prozedur:

Diese gespeicherte Prozedur kann mit den folgenden Anweisungen ausgeführt werden:

DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT; PRINT N"Gelöschte Mitarbeiter: " + konvertieren(nvarchar(30), @quantityDeleteEmployee);

Dieses Verfahren zählt die Anzahl der Projekte, an denen der Mitarbeiter mit der Personalnummer @empId arbeitet, und weist den resultierenden Wert dem Parameter ©counter zu. Nachdem alle Zeilen für eine bestimmte Personalnummer aus den Tabellen „Employee“ und „Works_on“ gelöscht wurden, wird der berechnete Wert der Variablen @quantityDeleteEmployee zugewiesen.

Der Parameterwert wird nur dann an die aufrufende Prozedur zurückgegeben, wenn die OUTPUT-Option angegeben ist. Im obigen Beispiel übergibt die Prozedur „DeleteEmployee“ den Parameter „@counter“ an die aufrufende Prozedur, sodass die gespeicherte Prozedur einen Wert an das System zurückgibt. Daher muss der Parameter @counter sowohl in der OUTPUT-Option bei der Deklaration einer Prozedur als auch in der EXECUTE-Anweisung beim Aufruf angegeben werden.

WITH RESULTS SETS-Klausel der EXECUTE-Anweisung

In SQL Server 2012 geben Sie für die EXECUTE-Anweisung Folgendes ein: WITH RESULTS SETS-Klausel, mit dem Sie bei Erfüllung bestimmter Bedingungen die Form der Ergebnismenge einer gespeicherten Prozedur ändern können.

Die folgenden zwei Beispiele helfen, diesen Satz zu erklären. Das erste Beispiel ist ein einführendes Beispiel, das zeigt, wie das Ergebnis aussehen könnte, wenn die WITH RESULTS SETS-Klausel weggelassen wird:

Bei der Prozedur EmployeesInDept handelt es sich um eine einfache Prozedur, die die Personalnummern und Nachnamen aller Mitarbeiter einer bestimmten Abteilung anzeigt. Die Abteilungsnummer ist ein Prozedurparameter und muss beim Aufruf angegeben werden. Die Ausführung dieser Prozedur erzeugt eine Tabelle mit zwei Spalten, deren Überschriften mit den Namen der entsprechenden Spalten in der Datenbanktabelle übereinstimmen, d. h. ID und Nachname. Um die Kopfzeilen von Ergebnisspalten (sowie deren Datentyp) zu ändern, verwendet SQL Server 2012 die neue WITH RESULTS SETS-Klausel. Die Anwendung dieses Satzes wird im folgenden Beispiel gezeigt:

USE SampleDb; EXEC EmployeesInDept „d1“ WITH RESULT SETS (( INT NOT NULL, [LastName] CHAR(20) NOT NULL));

Das Ergebnis der Ausführung einer auf diese Weise aufgerufenen gespeicherten Prozedur ist wie folgt:

Wie Sie sehen, können Sie durch Ausführen einer gespeicherten Prozedur mithilfe der WITH RESULT SETS-Klausel in der EXECUTE-Anweisung die Namen und Datentypen der Spalten in der von der Prozedur erzeugten Ergebnismenge ändern. Somit bietet diese neue Funktionalität eine größere Flexibilität bei der Ausführung gespeicherter Prozeduren und der Platzierung ihrer Ergebnisse in einer neuen Tabelle.

Ändern der Struktur gespeicherter Prozeduren

Auch die Datenbank-Engine unterstützt die Anweisung VERFAHREN ÄNDERN um die Struktur gespeicherter Prozeduren zu ändern. Die ALTER PROCEDURE-Anweisung wird normalerweise verwendet, um Transact-SQL-Anweisungen innerhalb einer Prozedur zu ändern. Alle Parameter der ALTER PROCEDURE-Anweisung haben die gleiche Bedeutung wie die gleichen Parameter der CREATE PROCEDURE-Anweisung. Der Hauptzweck dieser Anweisung besteht darin, das Überschreiben vorhandener Rechte für gespeicherte Prozeduren zu vermeiden.

Die Datenbank-Engine unterstützt CURSOR-Datentyp. Dieser Datentyp wird zum Deklarieren von Cursorn in gespeicherten Prozeduren verwendet. Mauszeiger ist ein Programmierkonstrukt, das zum Speichern der Ergebnisse einer Abfrage (normalerweise einer Reihe von Zeilen) verwendet wird und es Benutzern ermöglicht, dieses Ergebnis Zeile für Zeile anzuzeigen.

Um eine oder eine Gruppe gespeicherter Prozeduren zu löschen, verwenden Sie DROP PROCEDURE-Anweisung. Nur der Besitzer oder Mitglieder der festen Rollen db_owner und sysadmin können eine gespeicherte Prozedur löschen.

Gespeicherte Prozeduren und die Common Language Runtime

SQL Server unterstützt die Common Language Runtime (CLR), mit der Sie verschiedene Datenbankobjekte (gespeicherte Prozeduren, benutzerdefinierte Funktionen, Trigger, benutzerdefinierte Aggregationen und benutzerdefinierte Datentypen) mit C# und Visual Basic entwickeln können. Mit der CLR können Sie diese Objekte auch über das gemeinsame Laufzeitsystem ausführen.

Die Common Language Runtime wird mit der Option aktiviert und deaktiviert clr_enabled Systemprozedur sp_configure, das durch Anweisung zur Ausführung gestartet wird NEU KONFIGURIEREN. Das folgende Beispiel zeigt, wie Sie die Systemprozedur sp_configure verwenden können, um die CLR zu aktivieren:

USE SampleDb; EXEC sp_configure „clr_enabled“,1 NEU KONFIGURIEREN

Um eine Prozedur mithilfe der CLR zu erstellen, zu kompilieren und zu speichern, müssen Sie die folgende Schrittfolge in der gezeigten Reihenfolge ausführen:

Erstellen Sie eine gespeicherte Prozedur in C# oder Visual Basic und kompilieren Sie sie dann mit dem entsprechenden Compiler.

Gebrauchsanweisung MONTAGE ERSTELLEN, erstellen Sie die entsprechende ausführbare Datei.

Führen Sie die Prozedur mit der EXECUTE-Anweisung aus.

Die folgende Abbildung zeigt ein grafisches Diagramm der zuvor beschriebenen Schritte. Im Folgenden finden Sie eine detailliertere Beschreibung dieses Prozesses.

Erstellen Sie zunächst das benötigte Programm in einer Entwicklungsumgebung wie Visual Studio. Kompilieren Sie das fertige Programm mit einem C#- oder Visual Basic-Compiler in Objektcode. Dieser Code wird in einer Dynamic-Link-Library-Datei (.dll) gespeichert, die als Quelle für die CREATE ASSEMBLY-Anweisung dient, die den ausführbaren Zwischencode erstellt. Geben Sie als Nächstes eine CREATE PROCEDURE-Anweisung aus, um den ausgeführten Code als Datenbankobjekt zu speichern. Führen Sie abschließend die Prozedur mit der bekannten EXECUTE-Anweisung aus.

Das folgende Beispiel zeigt den Quellcode für eine gespeicherte Prozedur in C#:

Verwenden von System.Data.SqlClient; mit Microsoft.SqlServer.Server; öffentliche Teilklasse StoredProcedures ( public static int CountEmployees() ( int rows; SqlConnection Connection = new SqlConnection("Context Connection=true"); Connection.Open(); SqlCommand cmd = Connection.CreateCommand(); cmd.CommandText = "select count(*) as „Anzahl der Mitarbeiter“ „ + „vom Mitarbeiter“; rows = (int)cmd.ExecuteScalar(); Connection.Close(); return rows; ) )

Dieses Verfahren implementiert eine Abfrage zum Zählen der Anzahl der Zeilen in der Employee-Tabelle. Mithilfe von Direktiven am Anfang eines Programms geben Sie die Namensräume an, die zum Ausführen des Programms erforderlich sind. Mit diesen Direktiven können Sie Klassennamen im Quellcode angeben, ohne die entsprechenden Namespaces explizit anzugeben. Als nächstes wird die StoredProcedures-Klasse definiert, für die SqlProcedure-Attribut, wodurch der Compiler darüber informiert wird, dass es sich bei dieser Klasse um eine gespeicherte Prozedur handelt. Die Methode CountEmployees() ist im Klassencode definiert. Über eine Instanz der Klasse wird eine Verbindung zum Datenbanksystem hergestellt SQLConnection. Um eine Verbindung zu öffnen, wird die Open()-Methode dieser Instanz verwendet. A CreateCommand()-Methode ermöglicht Ihnen den Zugriff auf eine Instanz einer Klasse SqlCommnd, an den der erforderliche SQL-Befehl übergeben wird.

Im folgenden Codeausschnitt:

Cmd.CommandText = „select count(*) as „Anzahl der Mitarbeiter“ „ + „vom Mitarbeiter“;

verwendet eine SELECT-Anweisung, um die Anzahl der Zeilen in der Employee-Tabelle zu zählen und das Ergebnis anzuzeigen. Der Befehlstext wird angegeben, indem die CommandText-Eigenschaft der cmd-Variablen auf die von der CreateCommand()-Methode zurückgegebene Instanz festgelegt wird. Als nächstes heißt es ExecuteScalar()-Methode SqlCommand-Instanz. Diese Methode gibt einen Skalarwert zurück, der in einen ganzzahligen Datentyp konvertiert und der Zeilenvariablen zugewiesen wird.

Sie können diesen Code jetzt mit Visual Studio kompilieren. Ich habe diese Klasse zu einem Projekt namens CLRStoredProcedures hinzugefügt, sodass Visual Studio eine Assembly mit demselben Namen und der Erweiterung *.dll kompiliert. Das folgende Beispiel zeigt den nächsten Schritt beim Erstellen einer gespeicherten Prozedur: das Erstellen des ausführbaren Codes. Bevor Sie den Code in diesem Beispiel ausführen, müssen Sie den Speicherort der kompilierten DLL-Datei kennen (normalerweise im Debug-Ordner des Projekts).

USE SampleDb; ERSTELLEN SIE ASSEMBLY CLRStoredProcedures AUS „D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll“ MIT PERMISSION_SET = SAFE

Die CREATE ASSEMBLY-Anweisung verwendet verwalteten Code als Eingabe und erstellt ein entsprechendes Objekt, für das Sie gespeicherte CLR-Prozeduren, benutzerdefinierte Funktionen und Trigger erstellen können. Diese Anweisung hat die folgende Syntax:

CREATE ASSEMBLY Assemblyname [Autorisierung Eigentümername] FROM (DLL-Datei) Syntaxkonventionen

Der Parameter „assembly_name“ gibt den Namen der Assembly an. Die optionale AUTHORIZATION-Klausel gibt den Rollennamen als Besitzer dieser Assembly an. Die FROM-Klausel gibt den Pfad an, in dem sich die zu ladende Assembly befindet.

WITH PERMISSION_SET-Klausel ist eine sehr wichtige Klausel der CREATE ASSEMBLY-Anweisung und muss immer angegeben werden. Es definiert den Satz von Berechtigungen, die dem Assemblercode gewährt werden. Der SAFE-Berechtigungssatz ist der restriktivste. Assemblercode, der über diese Rechte verfügt, kann nicht auf externe Systemressourcen wie Dateien zugreifen. Der Rechtesatz EXTERNAL_ACCESS ermöglicht dem Assemblercode den Zugriff auf bestimmte externe Systemressourcen, während der Rechtesatz UNSAFE den uneingeschränkten Zugriff auf Ressourcen sowohl innerhalb als auch außerhalb des Datenbanksystems ermöglicht.

Um Assembler-Codeinformationen zu speichern, muss der Benutzer in der Lage sein, eine CREATE ASSEMBLY-Anweisung abzusetzen. Der Besitzer der Assembly ist der Benutzer (oder die Rolle), der die Anweisung ausführt. Sie können einen anderen Benutzer zum Eigentümer der Assembly machen, indem Sie die AUTHORIZATION-Klausel der CREATE SCHEMA-Anweisung verwenden.

Die Datenbank-Engine unterstützt auch ALTER ASSEMBLY- und DROP ASSEMBLY-Anweisungen. ALTER ASSEMBLY-Anweisung Wird verwendet, um die Assembly auf die neueste Version zu aktualisieren. Diese Anweisung fügt auch Dateien hinzu oder entfernt sie, die der entsprechenden Baugruppe zugeordnet sind. DROP ASSEMBLY-Anweisung Entfernt die angegebene Assembly und alle zugehörigen Dateien aus der aktuellen Datenbank.

Das folgende Beispiel zeigt, wie Sie eine gespeicherte Prozedur basierend auf dem verwalteten Code erstellen, den Sie zuvor implementiert haben:

USE SampleDb; ERSTELLEN SIE PROZEDUR CountEmployees ALS EXTERNEN NAME CLRStoredProcedures.StoredProcedures.CountEmployees

Die CREATE PROCEDURE-Anweisung im Beispiel unterscheidet sich von derselben Anweisung in den vorherigen Beispielen dadurch, dass sie Folgendes enthält Parameter EXTERNER NAME. Diese Option gibt an, dass der Code von der Common Language Runtime generiert wird. Der Name in diesem Satz besteht aus drei Teilen:

Assemblyname.Klassenname.Methodenname

Assembly_Name – gibt den Namen der Assembly an;

Klassenname – gibt den Namen der allgemeinen Klasse an;

method_name – optionaler Teil, gibt den Namen der Methode an, die innerhalb der Klasse definiert ist.

Die Ausführung der CountEmployees-Prozedur wird im folgenden Beispiel gezeigt:

USE SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count – Rückgabe 7

Die PRINT-Anweisung gibt die aktuelle Anzahl von Zeilen in der Employee-Tabelle zurück.

Bei der Arbeit mit SQL Server können Benutzer eigene Prozeduren erstellen, die bestimmte Aktionen implementieren. Gespeicherte Prozeduren sind vollwertige Datenbankobjekte und daher wird jedes von ihnen in einer bestimmten Datenbank gespeichert. Ein direkter Aufruf einer gespeicherten Prozedur ist nur möglich, wenn er im Kontext der Datenbank erfolgt, in der sich die Prozedur befindet.

Arten von gespeicherten Prozeduren

SQL Server verfügt über verschiedene Arten von gespeicherten Prozeduren.

Gespeicherte Systemprozeduren sind für die Durchführung verschiedener Verwaltungsaktionen konzipiert. Mit ihrer Hilfe werden nahezu alle Tätigkeiten der Serveradministration durchgeführt. Wir können sagen, dass gespeicherte Systemprozeduren eine Schnittstelle sind, die die Arbeit mit Systemtabellen ermöglicht, bei der es sich letztendlich um das Ändern, Hinzufügen, Löschen und Abrufen von Daten aus Systemtabellen von Benutzer- und Systemdatenbanken handelt. Gespeicherte Systemprozeduren haben das Präfix „sp_“, werden in der Systemdatenbank gespeichert und können im Kontext jeder anderen Datenbank aufgerufen werden.

Benutzerdefinierte gespeicherte Prozeduren implementieren bestimmte Aktionen. Gespeicherte Prozeduren sind ein vollwertiges Datenbankobjekt. Dadurch befindet sich jede gespeicherte Prozedur in einer bestimmten Datenbank, wo sie ausgeführt wird.

Temporär gespeicherte Prozeduren sind nur für kurze Zeit vorhanden und werden danach automatisch vom Server zerstört. Sie werden in lokal und global unterteilt. Lokale temporär gespeicherte Prozeduren können nur über die Verbindung aufgerufen werden, in der sie erstellt werden. Wenn Sie eine solche Prozedur erstellen, müssen Sie ihr einen Namen geben, der mit einem einzelnen #-Zeichen beginnt. Wie alle temporären Objekte werden gespeicherte Prozeduren dieses Typs automatisch gelöscht, wenn der Benutzer die Verbindung trennt oder der Server neu gestartet oder gestoppt wird. Globale temporär gespeicherte Prozeduren stehen jeder Verbindung von einem Server zur Verfügung, der über dieselbe Prozedur verfügt. Um es zu definieren, geben Sie ihm einfach einen Namen, der mit den Zeichen ## beginnt. Diese Prozeduren werden gelöscht, wenn der Server neu gestartet oder gestoppt wird oder wenn die Verbindung in dem Kontext, in dem sie erstellt wurden, geschlossen wird.

Löst aus

Löst aus sind eine Art gespeicherte Prozedur. Sie werden ausgeführt, wenn ein DML-Operator (Data Manipulation Language) für die Tabelle ausgeführt wird. Trigger werden zur Überprüfung der Datenintegrität und auch zum Zurücksetzen von Transaktionen verwendet.

Auslösen ist eine kompilierte SQL-Prozedur, deren Ausführung vom Auftreten bestimmter Ereignisse in der relationalen Datenbank abhängig ist. Die Verwendung von Triggern ist für Datenbankbenutzer größtenteils sehr praktisch. Dennoch ist ihr Einsatz oft mit zusätzlichen Ressourcenkosten für I/O-Operationen verbunden. Wenn mit gespeicherten Prozeduren oder Anwendungsprogrammen die gleichen Ergebnisse (mit viel weniger Overhead) erzielt werden können, ist die Verwendung von Triggern nicht praktikabel.

Löst aus ist ein spezielles SQL-Server-Tool zur Aufrechterhaltung der Datenintegrität in einer Datenbank. Integritätseinschränkungen, Regeln und Standardeinstellungen erreichen möglicherweise nicht immer das gewünschte Maß an Funktionalität. Oft ist es notwendig, komplexe Datenverifizierungsalgorithmen zu implementieren, um deren Zuverlässigkeit und Realität sicherzustellen. Darüber hinaus müssen Sie manchmal Änderungen in Tabellenwerten überwachen, damit die zugehörigen Daten nach Bedarf geändert werden können. Trigger können als eine Art Filter betrachtet werden, die wirksam werden, nachdem alle Vorgänge gemäß Regeln, Standardwerten usw. abgeschlossen wurden.

Auslösen ist eine spezielle Art einer gespeicherten Prozedur, die automatisch vom Server gestartet wird, wenn versucht wird, Daten in Tabellen zu ändern, denen Trigger zugeordnet sind. Jeden Auslösen ist an eine bestimmte Tabelle gebunden. Alle dadurch vorgenommenen Datenänderungen werden als eine Transaktion betrachtet. Wenn ein Fehler oder eine Verletzung der Datenintegrität festgestellt wird, wird die Transaktion zurückgesetzt. Änderungen sind daher untersagt. Auch alle vom Trigger bereits vorgenommenen Änderungen werden rückgängig gemacht.

Erstellt auslösen nur der Datenbankeigentümer. Mit dieser Einschränkung können Sie versehentliche Änderungen an der Struktur von Tabellen, Möglichkeiten zum Verbinden anderer Objekte usw. vermeiden.

Auslösen Es ist ein sehr nützliches und zugleich gefährliches Mittel. Wenn also die Logik seiner Operation falsch ist, können Sie leicht eine ganze Datenbank zerstören, daher müssen Trigger sehr sorgfältig debuggt werden.

Im Gegensatz zu einer regulären Unterroutine auslösen wird implizit ausgeführt, wenn ein Triggerereignis auftritt, und hat keine Argumente. Die Aktivierung wird manchmal als Auslösen des Auslösers bezeichnet. Mithilfe von Triggern werden folgende Ziele erreicht:

Validierung der Richtigkeit der eingegebenen Daten und Durchsetzung komplexer Datenintegritätsbeschränkungen, die mithilfe von für eine Tabelle festgelegten Integritätsbeschränkungen nur schwer oder gar nicht aufrechtzuerhalten sind;

Ausgabe von Warnungen, die Sie daran erinnern, bestimmte Aktionen auszuführen, wenn Sie eine auf eine bestimmte Weise implementierte Tabelle aktualisieren;

Ansammlung von Auditinformationen durch Aufzeichnung von Informationen über die vorgenommenen Änderungen und die Personen, die sie durchgeführt haben;

Replikationsunterstützung.

Das Grundformat des CREATE TRIGGER-Befehls ist unten dargestellt:

<Определение_триггера>::=

CREATE TRIGGER Triggername

VORHER | NACH<триггерное_событие>

AN<имя_таблицы>

<список_старых_или_новых_псевдонимов>]

<тело_триггера>

Auslöserereignisse bestehen aus dem Einfügen, Löschen und Aktualisieren von Zeilen in einer Tabelle. Im letzteren Fall können Sie für das Auslöseereignis bestimmte Tabellenspaltennamen angeben. Der Zeitpunkt des Auslösers wird mithilfe der BEFORE-Schlüsselwörter bestimmt ( Auslösen wird ausgeführt, bevor die damit verbundenen Ereignisse ausgeführt werden) oder AFTER (nachdem sie ausgeführt werden).

Die vom Trigger durchgeführten Aktionen werden für jede vom Ereignis abgedeckte Zeile (FOR EACH ROW) oder nur einmal für jedes Ereignis (FOR EACH STATEMENT) angegeben.

Falsch geschriebene Trigger können zu schwerwiegenden Problemen wie Deadlocks führen. Auslöser können viele Ressourcen über einen längeren Zeitraum blockieren, daher sollte besonderes Augenmerk auf die Minimierung von Zugriffskonflikten gelegt werden.

Auslösen kann nur in der aktuellen Datenbank erstellt werden, es ist jedoch möglich, innerhalb des Triggers auf andere Datenbanken zuzugreifen, einschließlich solcher, die sich auf einem Remote-Server befinden.

gespeicherte Prozedur ist nur möglich, wenn sie im Kontext der Datenbank durchgeführt wird, in der sich die Prozedur befindet.

Arten von gespeicherten Prozeduren

SQL Server hat mehrere Typen Gespeicherte Prozeduren.

System Gespeicherte Prozeduren Entwickelt, um verschiedene Verwaltungsaktionen durchzuführen. Mit ihrer Hilfe werden nahezu alle Tätigkeiten der Serveradministration durchgeführt. Wir können das systemisch sagen Gespeicherte Prozeduren sind eine Schnittstelle, die die Arbeit mit Systemtabellen ermöglicht, bei der es sich letztendlich um das Ändern, Hinzufügen, Löschen und Abrufen von Daten aus Systemtabellen von Benutzer- und Systemdatenbanken handelt. System Gespeicherte Prozeduren haben das Präfix sp_, werden in der Systemdatenbank gespeichert und können im Kontext jeder anderen Datenbank aufgerufen werden.
Brauch Gespeicherte Prozeduren bestimmte Maßnahmen umsetzen. Gespeicherte Prozeduren– ein vollwertiges Datenbankobjekt. Infolgedessen jeder gespeicherte Prozedur befindet sich in einer bestimmten Datenbank, in der es ausgeführt wird.
Vorübergehend Gespeicherte Prozeduren bestehen nur eine Weile und werden danach automatisch vom Server zerstört. Sie werden in lokal und global unterteilt. Lokal vorübergehend Gespeicherte Prozeduren können nur aus der Verbindung aufgerufen werden, in der sie erstellt wurden. Wenn Sie eine solche Prozedur erstellen, müssen Sie ihr einen Namen geben, der mit einem einzelnen #-Zeichen beginnt. Wie alle temporären Objekte Gespeicherte Prozeduren Dateien dieses Typs werden automatisch gelöscht, wenn der Benutzer die Verbindung trennt oder der Server neu gestartet oder gestoppt wird. Global temporär Gespeicherte Prozeduren stehen für alle Verbindungen von einem Server zur Verfügung, der über das gleiche Verfahren verfügt. Um es zu definieren, geben Sie ihm einfach einen Namen, der mit den Zeichen ## beginnt. Diese Prozeduren werden gelöscht, wenn der Server neu gestartet oder gestoppt wird oder wenn die Verbindung in dem Kontext, in dem sie erstellt wurden, geschlossen wird.

Erstellen, ändern und löschen Sie gespeicherte Prozeduren

Schaffung gespeicherte Prozedur beinhaltet die Lösung folgender Probleme:

Bestimmen der Art der erstellten gespeicherte Prozedur: temporär oder benutzerdefiniert. Darüber hinaus können Sie Ihr eigenes System erstellen gespeicherte Prozedur, geben Sie ihm einen Namen mit dem Präfix sp_ und platzieren Sie ihn in der Systemdatenbank. Dieses Verfahren ist im Kontext jeder lokalen Serverdatenbank verfügbar;
Zutrittsrechte planen. Beim Erstellen gespeicherte Prozedur Es ist zu berücksichtigen, dass es die gleichen Zugriffsrechte auf Datenbankobjekte hat wie der Benutzer, der es erstellt hat.
Definition gespeicherte Prozedurparameter. Ähnlich den Prozeduren, die in den meisten Programmiersprachen enthalten sind, Gespeicherte Prozeduren kann Eingabe- und Ausgabeparameter haben;
Code-Entwicklung gespeicherte Prozedur. Der Prozedurcode kann eine Folge beliebiger SQL-Befehle enthalten, einschließlich Aufrufen anderer Gespeicherte Prozeduren.

Erstellen Sie ein neues und ändern Sie ein vorhandenes gespeicherte Prozedur erledigt mit folgendem Befehl:

<определение_процедуры>::= (CREATE | ALTER ) procedure_name [;number] [(@parameter_name data_type ) [=default] ][,...n] AS sql_operator [...n]

Schauen wir uns die Parameter dieses Befehls an.

Mit den Präfixen sp_ , # , ## kann die erstellte Prozedur als System- oder temporäre Prozedur definiert werden. Wie Sie der Befehlssyntax entnehmen können, ist es nicht zulässig, den Namen des Besitzers anzugeben, der Eigentümer der erstellten Prozedur sein wird, sowie den Namen der Datenbank, in der sie gespeichert werden soll. Also, um das Geschaffene zu platzieren gespeicherte Prozedur In einer bestimmten Datenbank müssen Sie den Befehl CREATE PROCEDURE im Kontext dieser Datenbank ausgeben. Beim Abwenden vom Körper gespeicherte Prozedur Für Objekte derselben Datenbank können verkürzte Namen verwendet werden, d. h. ohne Angabe des Datenbanknamens. Wenn Sie auf Objekte zugreifen müssen, die sich in anderen Datenbanken befinden, ist die Angabe des Datenbanknamens obligatorisch.

Die Zahl im Namen ist eine Identifikationsnummer gespeicherte Prozedur, wodurch es in einer Gruppe von Prozeduren eindeutig identifiziert wird. Zur Vereinfachung der Verwaltung sind die Verfahren logischerweise vom gleichen Typ Gespeicherte Prozeduren können gruppiert werden, indem ihnen der gleiche Name, aber unterschiedliche Identifikationsnummern gegeben werden.

Um Eingabe- und Ausgabedaten in das erstellte zu übertragen gespeicherte Prozedur Es können Parameter verwendet werden, deren Namen, ebenso wie die Namen lokaler Variablen, mit dem @-Symbol beginnen müssen. Eins gespeicherte Prozedur Sie können mehrere Parameter durch Kommas getrennt angeben. Der Rumpf einer Prozedur sollte keine lokalen Variablen verwenden, deren Namen mit den Namen der Parameter dieser Prozedur übereinstimmen.

Um den Datentyp zu bestimmen, der dem entspricht Parameter einer gespeicherten Prozedur sind alle SQL-Datentypen geeignet, auch benutzerdefinierte. Der Datentyp CURSOR kann jedoch nur als verwendet werden Ausgabeparameter gespeicherte Prozedur, d.h. Angabe des Schlüsselworts OUTPUT.

Das Vorhandensein des Schlüsselworts OUTPUT bedeutet, dass der entsprechende Parameter Daten zurückgeben soll gespeicherte Prozedur. Dies bedeutet jedoch nicht, dass der Parameter nicht für die Übergabe von Werten geeignet ist gespeicherte Prozedur. Durch Angabe des Schlüsselworts OUTPUT wird der Server angewiesen, den Vorgang zu beenden gespeicherte Prozedur Weisen Sie den aktuellen Wert des Parameters der lokalen Variablen zu, die beim Aufruf der Prozedur als Wert des Parameters angegeben wurde. Beachten Sie, dass bei Angabe des Schlüsselworts OUTPUT der Wert des entsprechenden Parameters beim Aufruf der Prozedur nur über eine lokale Variable festgelegt werden kann. Alle Ausdrücke oder Konstanten, die für reguläre Parameter zulässig sind, sind nicht zulässig.

Das Schlüsselwort VARYING wird in Verbindung mit verwendet

Gespeicherte Prozedur gespeicherte Prozedur) ist ein benanntes Datenbankprogrammobjekt. SQL Server verfügt über verschiedene Arten von gespeicherten Prozeduren.

Gespeicherte Systemprozeduren Gespeicherte Systemprozeduren) werden von DBMS-Entwicklern bereitgestellt und dazu verwendet, Aktionen mit dem Systemverzeichnis auszuführen oder Systeminformationen abzurufen. Ihre Namen beginnen normalerweise mit dem Präfix „sp_“. Sie führen alle Arten von gespeicherten Prozeduren mit dem Befehl EXECUTE aus, der auf EXEC abgekürzt werden kann. Beispielsweise erstellt die gespeicherte Prozedur sp_helplogins, die ohne Parameter ausgeführt wird, zwei Berichte über Kontonamen (Englisch) Logins) und die entsprechenden Benutzer in jeder Datenbank (Englisch) Benutzer).

EXEC sp_helplogins;

Um eine Vorstellung von den Aktionen zu geben, die mit gespeicherten Systemprozeduren ausgeführt werden, Tabelle 10.6 zeigt einige Beispiele. Insgesamt gibt es in SQL Server mehr als tausend gespeicherte Systemprozeduren.

Tabelle 10.6

Beispiele für gespeicherte SQL Server-Systemprozeduren

Der Benutzer kann gespeicherte Prozeduren in Benutzerdatenbanken und in der Datenbank für temporäre Objekte erstellen. Im letzteren Fall handelt es sich um die gespeicherte Prozedur zeitlich. Wie bei temporären Tabellen muss der Name einer temporär gespeicherten Prozedur mit dem Präfix „#“ beginnen, wenn es sich um eine lokale temporäre gespeicherte Prozedur handelt, oder mit „##“, wenn es sich um eine globale handelt. Eine lokale temporäre Prozedur kann nur innerhalb der Verbindung verwendet werden, in der sie erstellt wurde, eine globale kann auch innerhalb anderer Verbindungen verwendet werden.

Programmierbare SQL Server-Objekte können entweder mit Transact-SQL-Tools oder -Assemblys erstellt werden (Englisch) Assembly) in der CRL-Umgebung (Common Language Runtime) des Microsoft.Net Framework. In diesem Tutorial wird nur die erste Methode behandelt.

Um gespeicherte Prozeduren zu erstellen, verwenden Sie die Anweisung CREATE PROCEDURE (kann auf PROC abgekürzt werden), deren Format unten angegeben ist:

CREATE (PROC I PROCEDURE) proc_name [ ; Nummer ]

[(gparameter data_type)

[„Standard] |

[MIT [ ,...N ] ]

[ZUR REPLIKATION]

AS ([ BEGIN ] sql_statement [;] [ ...n ] [ END ] )

Wenn eine gespeicherte Prozedur (oder ein Trigger, eine Funktion, eine Ansicht) mit der Option ENCRYPTION erstellt wird, wird ihr Code so transformiert, dass der Text unlesbar wird. Gleichzeitig wurde, wie in erwähnt, der verwendete Algorithmus von früheren Versionen von SQL Server übernommen und kann nicht als zuverlässiger Schutzalgorithmus betrachtet werden – es gibt Dienstprogramme, mit denen Sie die umgekehrte Konvertierung schnell durchführen können.

Die Option RECOMPILE gibt an, dass das System den Text bei jedem Aufruf der Prozedur neu kompiliert. Im Normalfall wird die beim ersten Durchlauf kompilierte Prozedur im Cache gespeichert, was eine Leistungssteigerung ermöglicht.

EXECUTE AS gibt den Sicherheitskontext an, in dem die Prozedur ausgeführt werden soll. Als nächstes einer der Werte f CALLER | SELBST | EIGENTÜMER | "Nutzername"). CALLER ist die Standardeinstellung und bedeutet, dass der Code im Sicherheitskontext des Benutzers ausgeführt wird, der dieses Modul aufruft. Dementsprechend muss der Benutzer Berechtigungen nicht nur für das programmierbare Objekt selbst, sondern auch für andere davon betroffene Datenbankobjekte haben. EXECUTE AS SELF bedeutet, den Kontext des Benutzers zu verwenden, der das programmierbare Objekt erstellt oder ändert. OWNER gibt an, dass der Code im Kontext des aktuellen Eigentümers der Prozedur ausgeführt wird. Wenn kein Eigentümer angegeben ist, wird der Eigentümer des Schemas angenommen, zu dem es gehört. Mit EXECUTE AS „user_name“ können Sie den Benutzernamen explizit angeben (in einfachen Anführungszeichen).

Für eine Prozedur können Parameter angegeben werden. Dabei handelt es sich um lokale Variablen, die zur Übergabe von Werten an eine Prozedur verwendet werden. Wenn ein Parameter mit dem Schlüsselwort OUTPUT (oder kurz OUT) deklariert wird, handelt es sich um einen Ausgabewert: Der Wert, der ihm in der Prozedur nach ihrem Abschluss übergeben wird, kann von dem Programm verwendet werden, das die Prozedur aufgerufen hat. Das Schlüsselwort READONLY bedeutet, dass der Wert des Parameters innerhalb der gespeicherten Prozedur nicht geändert werden kann.

Parametern können Standardwerte zugewiesen werden, die verwendet werden, wenn der Parameterwert beim Aufruf der Prozedur nicht explizit angegeben wird. Schauen wir uns ein Beispiel an:

CREATE PROC Surma (@a int, @b int=0,

©result int OUTPUT) AS

SETze @result=0a+0b

Wir haben eine Prozedur mit drei Parametern erstellt, und der Parameter @b hat den Standardwert =0, und der Parameter @result ist ein Ausgabeparameter: Er gibt den Wert an das aufrufende Programm zurück. Die durchgeführten Aktionen sind recht einfach: Der Ausgabeparameter erhält den Wert der Summe zweier Eingabeparameter.

Wenn Sie in SQL Server Management Studio arbeiten, finden Sie die erstellte gespeicherte Prozedur im Abschnitt „Programmierbare Datenbankobjekte“. (Englisch) Programmierbarkeit) im Unterabschnitt für gespeicherte Prozeduren (Abb. 10.2).

Beim Aufruf einer Prozedur können Sie sowohl Variablen als auch Konstanten als Eingabeparameter verwenden. Schauen wir uns zwei Beispiele an. Im ersten Fall werden die Eingabeparameter der Prozedur explizit als Konstanten angegeben und das Schlüsselwort OUTPUT wird für den Ausgabeparameter im Aufruf angegeben. Die zweite Option verwendet den Wert einer Variablen als ersten Eingabeparameter und gibt mit dem Schlüsselwort DEFAULT an, dass der Standardwert für den zweiten Parameter verwendet werden soll:

Reis. 10.2.

DECLARE @с int;

EXEC summa 10.5,@c OUTPUT;

DRUCKEN 0c; – 15 wird angezeigt

DECLARE Gi int = 5;

– Verwenden Sie beim Aufruf den Standardwert

EXEC summa Gi,DEFAULT , 0c OUTPUT;

DRUCKEN 0c; – 5 wird angezeigt

Betrachten wir nun ein Beispiel mit der Analyse des Returncodes, mit dem die Prozedur endet. Angenommen, wir müssen berechnen, wie viele Bücher in der Bookl-Tabelle in einem bestimmten Zeitraum von Jahren veröffentlicht wurden. Wenn außerdem das Anfangsjahr größer als das Endjahr ist, gibt die Prozedur „1“ zurück und zählt nicht, andernfalls zählen wir die Anzahl der Bücher und geben 0 zurück:

CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS

WENN 0FirsYear>0LastYear RETURN 1

SET @result= (SELECT COUNT(*) FROM dbo.Bookl

WO ZWISCHEN 0FirsYear UND 0LastYear);

Betrachten wir eine Variante des Aufrufs dieser Prozedur, bei der der Rückkehrcode in der Ganzzahlvariablen 0ret gespeichert wird und anschließend ihr Wert analysiert wird (in diesem Fall ist er 1). Die in der PRINT-Anweisung verwendete CAST-Funktion wird verwendet, um den Wert der Ganzzahlvariablen Gres in einen Zeichenfolgentyp zu konvertieren:

DECLARE 0ret int, Gres int

EXEC Gret = Rownum 2004, 2002, Gres OUT;

IF 0ret=l PRINT „Startjahr ist größer als Endjahr“

PRINT „Anzahl der Bücher“ + CAST(Gres as varchar(20))

Gespeicherte Prozeduren können nicht nur Daten aus einer Tabelle lesen, sondern auch Daten ändern und sogar Tabellen und eine Reihe anderer Datenbankobjekte erstellen.

Sie können jedoch keine Schemata, Funktionen, Trigger, Prozeduren und Ansichten aus einer gespeicherten Prozedur erstellen.

Das folgende Beispiel veranschaulicht sowohl diese Fähigkeiten als auch Probleme im Zusammenhang mit dem Umfang temporärer Objekte. Die folgende gespeicherte Prozedur prüft, ob die temporäre Tabelle #TaL2 vorhanden ist. Wenn diese Tabelle nicht existiert, wird sie erstellt. Anschließend werden die Werte zweier Spalten in die Tabelle #TaL2 eingetragen und der Inhalt der Tabelle mit der SELECT-Anweisung angezeigt:

CREATE PROC My_Procl (@id int, @name varchar(30))

WENN OBJECT_ID("tempdb.dbo.#Tab21) NULL IST

INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)

SELECT * FROM dbo. #Tab2 –№1

Bevor wir die gespeicherte Prozedur zum ersten Mal aufrufen, erstellen wir die darin verwendete temporäre Tabelle #TaL2. Achten Sie auf den EXEC-Operator. In den vorherigen Beispielen wurden Parameter „nach Position“ an die Prozedur übergeben, in diesem Fall wird jedoch ein anderes Format für die Parameterübergabe verwendet – „nach Name“, der Name des Parameters und sein Wert werden explizit angegeben:

CREATE TABLE dbo.#Tab2 (id int, name varchar(30));

EXEC My_Procl 0name="lvan", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

Im obigen Beispiel wird die SELECT-Anweisung zweimal verarbeitet: das erste Mal – innerhalb der Prozedur, das zweite Mal – vom aufrufenden Codefragment (markiert mit dem Kommentar „Nr. 2“).

Vor dem zweiten Aufruf der Prozedur löschen wir die temporäre Tabelle #TaL2. Anschließend wird aus der gespeicherten Prozedur eine temporäre Tabelle mit demselben Namen erstellt:

DROP TABLE dbo.#Tab2;

EXEC My_Procl 0name="Ivan", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

In diesem Fall zeigt nur die SELECT-Anweisung innerhalb der Prozedur (mit dem Kommentar „Xa 1“) Daten an. Die Ausführung von SELECT „No. 2“ führt zu einem Fehler, da die in der gespeicherten Prozedur erstellte temporäre Tabelle zum Zeitpunkt der Rückkehr der Prozedur bereits aus der tempdb-Datenbank gelöscht ist.

Sie können eine gespeicherte Prozedur mit der DROP PROCEDURE-Anweisung löschen. Sein Format wird unten dargestellt. Sie können mehrere gespeicherte Prozeduren mit einer Anweisung löschen, indem Sie sie durch Kommas getrennt auflisten:

DROP (PROC I PROCEDURE) (Prozedur) [

Löschen wir zum Beispiel die zuvor erstellte Summa-Prozedur:

DROP PROC summa;

Mit der ALTER PROCEDURE-Anweisung können Sie Änderungen an einer vorhandenen Prozedur vornehmen (und diese sogar neu definieren) (zulässig).

Abkürzung PROC). Mit Ausnahme des Schlüsselworts ALTER entspricht das Format der Anweisung im Wesentlichen dem von CREATE PROCEDURE. Lassen Sie uns beispielsweise die DBO-Prozedur ändern. rownum und legt fest, dass es im Sicherheitskontext des Eigentümers ausgeführt wird:

ALTER PROC dbo.rownum (SFirsYear int,

SLastYear int, Sresult int OUTPUT)

WITH EXECUTE AS Owner – installierbare Option

WENN 0FirsYear>0LastYear RETURN 1 ELSE BEGIN

SET 0result= (SELECT COUNT(*) FROM dbo.Bookl

WO ZWISCHEN SFirsYear UND SLastYear);

In manchen Fällen kann es notwendig sein, einen Befehl dynamisch zu generieren und auf dem Datenbankserver auszuführen. Dieses Problem kann auch mit dem EXEC-Operator gelöst werden. Das folgende Beispiel ruft Datensätze aus der Tabelle „Bookl“ ab, wenn das Attribut „Jahr“ dem durch die Variable angegebenen Wert entspricht:

DECLARE 0y int = 2000;

EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;

Durch die Ausführung dynamisch generierter Anweisungen werden die Voraussetzungen für die Durchführung von Computerangriffen wie „SQL-Injection“ geschaffen. (Englisch) SQL-Injektion). Der Kern des Angriffs besteht darin, dass der Angreifer seinen eigenen SQL-Code in eine dynamisch generierte Abfrage einfügt. Dies tritt typischerweise auf, wenn die zu ersetzenden Parameter den Ergebnissen der Benutzereingabe entnommen werden.

Lassen Sie uns das vorherige Beispiel leicht ändern:

DECLARE 0 und varchar(100);

SET 0у="2ООО"; – Wir haben dies vom Benutzer erhalten

Wenn wir davon ausgehen, dass wir den in der SET-Anweisung zugewiesenen Zeichenfolgenwert vom Benutzer erhalten haben (egal wie beispielsweise über eine Webanwendung), dann veranschaulicht das Beispiel das „normale“ Verhalten unseres Codes.

DECLARE 0 und varchar(100);

SET 0у="2000; DELETE FROM dbo.Book2"; – Injektion

EXEC("SELECT * FROM dbo.Book2 WHERE ="+0y);

In solchen Fällen wird empfohlen, nach Möglichkeit die gespeicherte Systemprozedur sp_executcsql zu verwenden, mit der Sie die Art der Parameter steuern können, was eines der Hindernisse für SQL-Injections darstellt. Ohne das Format im Detail zu betrachten, schauen wir uns ein Beispiel an, das dem zuvor vorgestellten ähnelt:

EXECUTE sp_executesql

N"SELECT * FROM dbo.Bookl WHERE =0y",

Dadurch wird explizit der Typ des in der Abfrage verwendeten Parameters angegeben, und SQL Server steuert ihn während der Ausführung. Der Buchstabe „N“ vor den Anführungszeichen gibt an, dass es sich um eine Unicode-Literalkonstante handelt, wie es die Prozedur erfordert. Einem Parameter kann nicht nur ein konstanter Wert, sondern auch der Wert einer anderen Variablen zugewiesen werden.

Das Konzept der gespeicherten Prozeduren wird definiert. Bietet Beispiele für das Erstellen, Ändern und Verwenden gespeicherter Prozeduren mit Parametern. Die Definition der Eingabe- und Ausgabeparameter wird angegeben. Es werden Beispiele für das Erstellen und Aufrufen gespeicherter Prozeduren bereitgestellt.

Das Konzept einer gespeicherten Prozedur

Gespeicherte Prozeduren sind Gruppen miteinander verbundener SQL-Anweisungen, deren Verwendung die Arbeit des Programmierers einfacher und flexibler macht gespeicherte Prozedur ist oft viel einfacher als eine Folge einzelner SQL-Anweisungen. Gespeicherte Prozeduren sind eine Reihe von Befehlen, die aus einer oder mehreren SQL-Anweisungen oder -Funktionen bestehen und in kompilierter Form in einer Datenbank gespeichert werden. Ausführung in der Datenbank Gespeicherte Prozeduren Anstelle einzelner SQL-Anweisungen hat der Benutzer folgende Vorteile:

die notwendigen Operatoren sind bereits in der Datenbank enthalten;
Sie haben alle die Bühne passiert Parsing und liegen im ausführbaren Format vor; Vor Ausführen einer gespeicherten Prozedur SQL Server generiert dafür einen Ausführungsplan, führt dessen Optimierung und Kompilierung durch;
Gespeicherte Prozeduren Unterstützung Modulare Programmierung, da Sie damit große Aufgaben in unabhängige, kleinere und einfacher zu verwaltende Teile aufteilen können;
Gespeicherte Prozeduren kann andere verursachen Gespeicherte Prozeduren und Funktionen;
Gespeicherte Prozeduren kann von anderen Arten von Anwendungsprogrammen aufgerufen werden;
allgemein, Gespeicherte Prozeduren schneller ausführen als eine Folge einzelner Anweisungen;
Gespeicherte Prozeduren einfacher zu verwenden: Sie können aus Dutzenden oder Hunderten von Befehlen bestehen, aber um sie auszuführen, müssen Sie nur den Namen des gewünschten Befehls angeben gespeicherte Prozedur. Dadurch können Sie die Größe der vom Client an den Server gesendeten Anfrage und damit die Belastung des Netzwerks reduzieren.

Das Speichern von Vorgängen am selben Ort, an dem sie ausgeführt werden, reduziert die über das Netzwerk übertragene Datenmenge und verbessert die Gesamtsystemleistung. Anwendung Gespeicherte Prozeduren vereinfacht die Wartung von Softwaresystemen und deren Änderungen. Typischerweise werden alle Integritätsbeschränkungen in Form von Regeln und Datenverarbeitungsalgorithmen auf dem Datenbankserver implementiert und stehen der Endanwendung als Satz zur Verfügung Gespeicherte Prozeduren, die die Datenverarbeitungsschnittstelle darstellen. Zur Gewährleistung der Datenintegrität sowie aus Sicherheitsgründen erhält die Anwendung in der Regel keinen direkten Zugriff auf die Daten – die gesamte Arbeit mit ihr erfolgt über einen Aufruf bestimmter Gespeicherte Prozeduren.

Dieser Ansatz macht es sehr einfach, Datenverarbeitungsalgorithmen zu ändern, die sofort allen Netzwerkbenutzern zur Verfügung stehen, und bietet die Möglichkeit, das System zu erweitern, ohne Änderungen an der Anwendung selbst vorzunehmen: einfach ändern gespeicherte Prozedur auf dem Datenbankserver. Der Entwickler muss die Anwendung nicht neu kompilieren, Kopien davon erstellen oder Benutzer anweisen, mit der neuen Version zu arbeiten. Benutzer bemerken möglicherweise nicht einmal, dass Änderungen am System vorgenommen wurden.

Gespeicherte Prozeduren existieren unabhängig von Tabellen oder anderen Datenbankobjekten. Sie werden vom Client-Programm aufgerufen, ein anderes gespeicherte Prozedur oder Auslöser. Der Entwickler kann die Zugriffsrechte verwalten gespeicherte Prozedur, seine Ausführung erlauben oder verbieten. Code ändern gespeicherte Prozedur nur durch seinen Eigentümer oder ein Mitglied einer festen Datenbankrolle zulässig. Bei Bedarf können Sie das Eigentum daran von einem Benutzer auf einen anderen übertragen.

Gespeicherte Prozeduren in der MS SQL Server-Umgebung

Bei der Arbeit mit SQL Server können Benutzer eigene Prozeduren erstellen, die bestimmte Aktionen implementieren. Gespeicherte Prozeduren sind vollwertige Datenbankobjekte und werden daher jeweils in einer bestimmten Datenbank gespeichert. Direktwahl gespeicherte Prozedur ist nur möglich, wenn sie im Kontext der Datenbank durchgeführt wird, in der sich die Prozedur befindet.

Arten von gespeicherten Prozeduren

SQL Server hat mehrere Typen Gespeicherte Prozeduren.

System Gespeicherte Prozeduren Entwickelt, um verschiedene Verwaltungsaktionen durchzuführen. Mit ihrer Hilfe werden nahezu alle Tätigkeiten der Serveradministration durchgeführt. Wir können das systemisch sagen Gespeicherte Prozeduren sind eine Schnittstelle, die die Arbeit mit Systemtabellen ermöglicht, bei der es sich letztendlich um das Ändern, Hinzufügen, Löschen und Abrufen von Daten aus Systemtabellen von Benutzer- und Systemdatenbanken handelt. System Gespeicherte Prozeduren haben das Präfix sp_, werden in der Systemdatenbank gespeichert und können im Kontext jeder anderen Datenbank aufgerufen werden.
Brauch Gespeicherte Prozeduren bestimmte Maßnahmen umsetzen. Gespeicherte Prozeduren– ein vollwertiges Datenbankobjekt. Infolgedessen jeder gespeicherte Prozedur befindet sich in einer bestimmten Datenbank, in der es ausgeführt wird.
Vorübergehend Gespeicherte Prozeduren bestehen nur eine Weile und werden danach automatisch vom Server zerstört. Sie werden in lokal und global unterteilt. Lokal vorübergehend Gespeicherte Prozeduren können nur aus der Verbindung aufgerufen werden, in der sie erstellt wurden. Wenn Sie eine solche Prozedur erstellen, müssen Sie ihr einen Namen geben, der mit einem einzelnen #-Zeichen beginnt. Wie alle temporären Objekte Gespeicherte Prozeduren Dateien dieses Typs werden automatisch gelöscht, wenn der Benutzer die Verbindung trennt oder der Server neu gestartet oder gestoppt wird. Global temporär Gespeicherte Prozeduren stehen für alle Verbindungen von einem Server zur Verfügung, der über das gleiche Verfahren verfügt. Um es zu definieren, geben Sie ihm einfach einen Namen, der mit den Zeichen ## beginnt. Diese Prozeduren werden gelöscht, wenn der Server neu gestartet oder gestoppt wird oder wenn die Verbindung in dem Kontext, in dem sie erstellt wurden, geschlossen wird.

Erstellen, ändern und löschen Sie gespeicherte Prozeduren

Schaffung gespeicherte Prozedur beinhaltet die Lösung folgender Probleme:

Bestimmen der Art der erstellten gespeicherte Prozedur: temporär oder benutzerdefiniert. Darüber hinaus können Sie Ihr eigenes System erstellen gespeicherte Prozedur, geben Sie ihm einen Namen mit dem Präfix sp_ und platzieren Sie ihn in der Systemdatenbank. Dieses Verfahren ist im Kontext jeder lokalen Serverdatenbank verfügbar;
Zutrittsrechte planen. Beim Erstellen gespeicherte Prozedur Es ist zu berücksichtigen, dass es die gleichen Zugriffsrechte auf Datenbankobjekte hat wie der Benutzer, der es erstellt hat.
Definition gespeicherte Prozedurparameter. Ähnlich den Prozeduren, die in den meisten Programmiersprachen enthalten sind, Gespeicherte Prozeduren kann Eingabe- und Ausgabeparameter haben;
Code-Entwicklung gespeicherte Prozedur. Der Prozedurcode kann eine Folge beliebiger SQL-Befehle enthalten, einschließlich Aufrufen anderer Gespeicherte Prozeduren.

Erstellen Sie ein neues und ändern Sie ein vorhandenes gespeicherte Prozedur erledigt mit folgendem Befehl:

<определение_процедуры>::= (CREATE | ALTER ) PROC procedure_name [;number] [(@parameter_name data_type ) [=default] ][,...n] AS sql_operator [...n]

Schauen wir uns die Parameter dieses Befehls an.

Das Schlüsselwort VARYING wird in Verbindung mit dem Parameter OUTPUT verwendet, der vom Typ CURSOR ist. Es bestimmt das Ausgabeparameter Es wird eine Ergebnismenge geben.

Das Schlüsselwort DEFAULT stellt den entsprechenden Wert dar Standardparameter. Daher müssen Sie beim Aufruf einer Prozedur den Wert des entsprechenden Parameters nicht explizit angeben.

Da der Server den Abfrageausführungsplan und den kompilierten Code zwischenspeichert, werden beim nächsten Aufruf der Prozedur die vorgefertigten Werte verwendet. In einigen Fällen ist es jedoch dennoch erforderlich, den Prozedurcode neu zu kompilieren. Durch die Angabe des Schlüsselworts RECOMPILE wird das System angewiesen, einen Ausführungsplan zu erstellen gespeicherte Prozedur jedes Mal, wenn sie anruft.

Der Parameter FOR REPLICATION ist erforderlich, wenn Daten repliziert und die erstellten Daten aktiviert werden gespeicherte Prozedur als Artikel zur Veröffentlichung.

Das Schlüsselwort ENCRYPTION weist den Server an, den Code zu verschlüsseln gespeicherte Prozedur, was Schutz vor der Verwendung proprietärer Algorithmen bieten kann, die die Arbeit implementieren gespeicherte Prozedur.

Das Schlüsselwort AS wird am Anfang des Hauptteils selbst platziert gespeicherte Prozedur, d.h. eine Reihe von SQL-Befehlen, mit deren Hilfe diese oder jene Aktion ausgeführt wird. Im Rumpf der Prozedur können nahezu alle SQL-Befehle verwendet, Transaktionen deklariert, Sperren gesetzt und weitere aufgerufen werden. Gespeicherte Prozeduren. Ausstieg aus gespeicherte Prozedur kann mit dem RETURN-Befehl erfolgen.

Entfernen einer gespeicherten Prozedur ausgeführt durch den Befehl:

DROP PROCEDURE (prozedurname) [,...n]

Ausführen einer gespeicherten Prozedur

Für eine gespeicherte Prozedur ausführen Der verwendete Befehl lautet:

[[ EXEC [ UTE] procedure_name [;number] [[@parameter_name=](value | @variable_name) |][,...n]

Wenn der Anruf gespeicherte Prozedur nicht der einzige Befehl im Paket ist, ist das Vorhandensein des EXECUTE-Befehls erforderlich. Darüber hinaus ist dieser Befehl erforderlich, um eine Prozedur aus dem Hauptteil einer anderen Prozedur oder eines anderen Triggers aufzurufen.

Die Verwendung des Schlüsselworts OUTPUT beim Aufruf einer Prozedur ist nur für Parameter zulässig, die wann deklariert wurden Erstellen einer Prozedur mit dem Schlüsselwort OUTPUT.

Wenn beim Aufruf einer Prozedur das Schlüsselwort DEFAULT für einen Parameter angegeben wird, wird es verwendet Standardwert. Selbstverständlich ist das angegebene Wort DEFAULT nur für die Parameter zulässig, für die es definiert ist Standardwert.

Die Syntax des EXECUTE-Befehls zeigt, dass Parameternamen beim Aufruf einer Prozedur weggelassen werden können. Allerdings muss der Benutzer in diesem Fall die Werte für die Parameter in der gleichen Reihenfolge angeben, in der sie wann aufgelistet wurden Erstellen einer Prozedur. Dem Parameter zuweisen Standardwert, Sie können es bei der Auflistung nicht einfach überspringen. Wenn Sie die Parameter weglassen möchten, für die es definiert ist Standardwert, reicht es aus, beim Aufruf die Parameternamen explizit anzugeben gespeicherte Prozedur. Darüber hinaus können Sie auf diese Weise Parameter und deren Werte in beliebiger Reihenfolge auflisten.

Beachten Sie, dass beim Aufruf einer Prozedur entweder Parameternamen mit Werten oder nur Werte ohne Parameternamen angegeben werden. Eine Kombination ist nicht gestattet.

Beispiel 12.1. Prozedur ohne Parameter. Entwickeln Sie ein Verfahren zur Ermittlung der Namen und Kosten der von Ivanov gekauften Waren.

CREATE PROC my_proc1 AS SELECT Product.Name, Product.Price*Transaction.Quantity AS Cost, Customer.Last Name FROM Customer INNER JOIN (Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode) ON Customer.CustomerCode=Transaction.CustomerCode WHERE Kunde .Nachname='Ivanov' Beispiel 12.1. Verfahren zur Erlangung der Namen und Werte der von Ivanov gekauften Waren.