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, stellen Folgendes bereit: zusätzliche Möglichkeit Gewährleistung der 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 Besitzer 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 einer temporären Datenbank abgelegt werden Systembasis tempdb-Daten. 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. Für diesen Namen lokales Verfahren wird mit einem einzelnen #-Symbol (#proc_name) angegeben, und der Name einer globalen Prozedur wird mit einem doppelten # (##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 EXECUTE-Schlüsselwort 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 für die EXECUTE-Anweisung wird eingegeben 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 mit 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.

Das Bild unten zeigt grafisches Diagramm die zuvor beschriebenen Schritte. Unten finden Sie mehr detaillierte Beschreibung dieser Prozess.

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. Durch die Verwendung dieser Anweisungen können Sie angeben Quellcode Klassennamen ohne explizite Angabe der entsprechenden Namespaces. 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 zum Aktualisieren der Assembly verwendet letzte Version. 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.

Gespeicherte SQL-Prozeduren sind ausführbare Programmmodule, die in Form verschiedener Objekte gespeichert werden können. Mit anderen Worten handelt es sich um ein Objekt, das SQL-Anweisungen enthält. Diese gespeicherten Prozeduren können im Anwendungsclient ausgeführt werden, um sie abzurufen gute Leistung. Darüber hinaus werden solche Objekte häufig aus anderen Skripten oder sogar aus einem anderen Abschnitt aufgerufen.

Einführung

Viele Leute glauben, dass sie verschiedenen Verfahren ähneln (außer MS SQL). Vielleicht stimmt das. Sie haben ähnliche Parameter und können ähnliche Werte erzeugen. Darüber hinaus berühren sie sich in manchen Fällen. Sie werden beispielsweise mit DDL- und DML-Datenbanken sowie Benutzerfunktionen (Codename UDF) kombiniert.

Tatsächlich weisen gespeicherte SQL-Prozeduren zahlreiche Vorteile auf, die sie von ähnlichen Prozessen unterscheiden. Sicherheit, Programmierflexibilität, Produktivität – all das lockt immer mehr Anwender, die mit Datenbanken arbeiten. Den Höhepunkt der Beliebtheit der Verfahren erreichten sie in den Jahren 2005–2010, als ein Programm von Microsoft namens „SQL Server Management Studio“ veröffentlicht wurde. Mit seiner Hilfe ist die Arbeit mit Datenbanken viel einfacher, praktischer und komfortabler geworden. Von Jahr zu Jahr gewann dieses Programm bei Programmierern an Popularität. Heute ist es ein absolut bekanntes Programm, das für Anwender, die mit Datenbanken „kommunizieren“, auf Augenhöhe mit Excel steht.

Wenn eine Prozedur aufgerufen wird, wird sie sofort vom Server selbst verarbeitet, ohne dass unnötige Prozesse oder Benutzereingriffe erforderlich sind. Anschließend können Sie beliebige Löschungen, Ausführungen oder Änderungen durchführen. Für all dies ist der DDL-Operator verantwortlich, der allein die komplexesten Aktionen zur Verarbeitung von Objekten ausführt. Darüber hinaus geschieht dies alles sehr schnell und der Server wird nicht wirklich belastet. Diese Geschwindigkeit und Leistung ermöglichen Ihnen die sehr schnelle Übertragung großer Informationsmengen vom Benutzer zum Server und umgekehrt.

Um diese Technologie für die Arbeit mit Informationen umzusetzen, gibt es mehrere Programmiersprachen. Hierzu zählen beispielsweise PL/SQL von Oracle, PSQL in den Systemen InterBase und Firebird sowie das klassische Microsoft Transact-SQL. Sie alle sind für die Erstellung und Ausführung gespeicherter Prozeduren konzipiert, was es großen Datenbankprozessoren ermöglicht, ihre eigenen Algorithmen zu verwenden. Dies ist auch notwendig, damit diejenigen, die diese Informationen verwalten, alle Objekte vor unbefugtem Zugriff Dritter und damit vor der Erstellung, Änderung oder Löschung bestimmter Daten schützen können.

Produktivität

Diese Datenbankobjekte können auf verschiedene Arten programmiert werden. Dadurch können Benutzer die Art der verwendeten Methode auswählen, die am besten geeignet ist, was Aufwand und Zeit spart. Darüber hinaus wird der Vorgang selbst abgewickelt, wodurch der enorme Zeitaufwand für die Kommunikation zwischen Server und Benutzer vermieden wird. Außerdem kann das Modul jederzeit umprogrammiert und in die gewünschte Richtung verändert werden. Besonders hervorzuheben ist die Geschwindigkeit, mit der die gespeicherte SQL-Prozedur gestartet wird: Dieser Prozess erfolgt schneller als andere ähnliche Prozesse, was ihn bequem und universell macht.

Sicherheit

Diese Art der Informationsverarbeitung unterscheidet sich von ähnlichen Prozessen dadurch, dass sie gewährleistet Erhöhte Sicherheit. Dies wird dadurch gewährleistet, dass der Zugriff auf Vorgänge durch andere Benutzer vollständig ausgeschlossen werden kann. Dadurch kann der Administrator unabhängig voneinander Vorgänge mit ihnen durchführen, ohne befürchten zu müssen, dass Informationen abgefangen oder unbefugt auf die Datenbank zugegriffen wird.

Datentransfer

Die Beziehung zwischen der gespeicherten SQL-Prozedur und der Clientanwendung besteht in der Verwendung von Parametern und Rückgabewerten. Letztere muss die Daten nicht an die gespeicherte Prozedur übergeben, sondern diese Informationen werden (hauptsächlich auf Wunsch des Benutzers) für SQL verarbeitet. Nachdem die gespeicherte Prozedur ihre Arbeit abgeschlossen hat, sendet sie Datenpakete zurück (aber wiederum optional) an die Anwendung, die sie aufgerufen hat verschiedene Methoden, mit deren Hilfe sowohl ein Aufruf einer gespeicherten SQL-Prozedur als auch ein Return erfolgen kann, zum Beispiel:

Übertragen von Daten mithilfe eines Ausgabetypparameters;

Übergabe von Daten mit dem Return-Operator;

Übergeben von Daten mit dem Auswahloperator.

Lassen Sie uns nun herausfinden, wie dieser Prozess von innen aussieht.

1. Erstellen Sie eine gespeicherte EXEC-Prozedur in SQL

Sie können eine Prozedur in MS SQL (Managment Studio) erstellen. Nachdem die Prozedur erstellt wurde, wird sie im programmierbaren Knoten der Datenbank aufgeführt, in der die Erstellungsprozedur vom Bediener ausgeführt wird. Zur Ausführung verwenden gespeicherte SQL-Prozeduren einen EXEC-Prozess, der den Namen des Objekts selbst enthält.

Wenn Sie eine Prozedur erstellen, erscheint zuerst ihr Name, gefolgt von einem oder mehreren ihr zugewiesenen Parametern. Parameter können optional sein. Nachdem die Parameter, also der Hauptteil der Prozedur, geschrieben wurden, müssen einige notwendige Operationen ausgeführt werden.

Der Punkt ist, dass ein Körper lokale Variablen enthalten kann und diese Variablen auch in Bezug auf Prozeduren lokal sind. Mit anderen Worten: Sie können nur im Hauptteil einer Prozedur angezeigt werden Microsoft SQL Server. Gespeicherte Prozeduren gelten in diesem Fall als lokal.

Um eine Prozedur zu erstellen, benötigen wir also den Namen der Prozedur und mindestens einen Parameter als Hauptteil der Prozedur. beachten Sie, dass ausgezeichnete Option In diesem Fall geht es darum, eine Prozedur mit dem Namen des Schemas im Klassifikator zu erstellen und auszuführen.

Der Hauptteil der Prozedur kann beliebiger Art sein, z. B. das Erstellen einer Tabelle, das Einfügen einer oder mehrerer Zeilen einer Tabelle, das Festlegen des Typs und der Art der Datenbank usw. Der Prozedurhauptteil schränkt jedoch die Ausführung bestimmter Vorgänge darin ein. Nachfolgend sind einige wichtige Einschränkungen aufgeführt:

Der Körper sollte keine andere gespeicherte Prozedur erstellen;

Der Körper sollte keinen falschen Eindruck vom Objekt erwecken;

Der Körper sollte keine Auslöser erzeugen.

2. Festlegen einer Variablen im Hauptteil der Prozedur

Sie können Variablen lokal für den Hauptteil der Prozedur festlegen, sodass sie sich dann ausschließlich im Hauptteil der Prozedur befinden. Es empfiehlt sich, Variablen am Anfang des Hauptteils der gespeicherten Prozedur zu erstellen. Sie können Variablen aber auch an einer beliebigen Stelle im Körper eines bestimmten Objekts festlegen.

Manchmal stellen Sie möglicherweise fest, dass mehrere Variablen in einer Zeile festgelegt sind und die einzelnen Variablenparameter durch ein Komma getrennt sind. Beachten Sie außerdem, dass der Variablen das Präfix @ vorangestellt ist. Im Hauptteil der Prozedur können Sie die Variable an eine beliebige Stelle setzen. Beispielsweise kann die Variable @NAME1 am Ende des Prozedurhauptteils deklariert werden. Um einer deklarierten Variablen einen Wert zuzuweisen, wird ein Satz personenbezogener Daten verwendet. Anders als in der Situation, in der mehr als eine Variable in derselben Zeile deklariert wird, wird in dieser Situation nur ein Satz personenbezogener Daten verwendet.

Benutzer stellen häufig die Frage: „Wie weist man einer Anweisung im Hauptteil einer Prozedur mehrere Werte zu?“ Also. Das ist eine interessante Frage, aber viel einfacher zu lösen, als Sie denken. Antwort: Verwenden von Paaren wie „Select Var = value“. Sie können diese Paare verwenden, indem Sie sie durch ein Komma trennen.

Eine Vielzahl von Beispielen zeigt, wie Menschen eine einfache gespeicherte Prozedur erstellen und ausführen. Eine Prozedur kann jedoch Parameter akzeptieren, sodass der Prozess, der sie aufruft, Werte in der Nähe davon hat (aber nicht immer). Wenn sie zusammenfallen, beginnen entsprechende Prozesse im Körperinneren. Wenn Sie beispielsweise eine Prozedur erstellen, die eine Stadt und Region vom Aufrufer akzeptiert und Daten darüber zurückgibt, wie viele Autoren zu der entsprechenden Stadt und Region gehören. Die Prozedur fragt die Autorentabellen der Datenbank ab, z. B. „Pubs“, um diese Autorenzählung durchzuführen. Um diese Datenbanken zu erhalten, lädt Google beispielsweise das SQL-Skript von der SQL2005-Seite herunter.

Im vorherigen Beispiel benötigt die Prozedur zwei Parameter, die Englische Sprache bedingt werden @State und @City genannt. Der Datentyp entspricht dem in der Anwendung definierten Typ. Der Hauptteil der Prozedur verfügt über interne Variablen @TotalAuthors, und diese Variable wird verwendet, um die Anzahl der Autoren anzuzeigen. Als nächstes kommt der Abschnitt zur Abfrageauswahl, der alles berechnet. Abschließend wird der berechnete Wert mit der print-Anweisung im Ausgabefenster gedruckt.

So führen Sie eine gespeicherte Prozedur in SQL aus

Es gibt zwei Möglichkeiten, den Vorgang durchzuführen. Der erste Weg zeigt durch die Übergabe von Parametern, wie eine durch Kommas getrennte Liste nach dem Prozedurnamen ausgeführt wird. Nehmen wir an, wir haben zwei Werte (wie im vorherigen Beispiel). Diese Werte werden mit erfasst variable Parameter@State- und @City-Prozeduren. Bei dieser Methode der Parameterübergabe ist die Reihenfolge wichtig. Diese Methode wird als ordinale Argumentübergabe bezeichnet. Bei der zweiten Methode sind die Parameter bereits direkt zugewiesen, die Reihenfolge spielt in diesem Fall keine Rolle. Diese zweite Methode wird als Übergabe benannter Argumente bezeichnet.

Der Ablauf kann geringfügig vom typischen abweichen. Alles ist wie im vorherigen Beispiel, nur werden hier die Parameter verschoben. Das heißt, der Parameter @City wird zuerst gespeichert und @State wird neben dem Standardwert gespeichert. Der Standardparameter wird normalerweise separat hervorgehoben. Gespeicherte SQL-Prozeduren werden nur als Parameter übergeben. In diesem Fall ersetzt der Parameter „UT“ sofern vorhanden den Standardwert „CA“. Bei der zweiten Ausführung wird nur ein Argumentwert für den @City-Parameter übergeben und der @State-Parameter nimmt den Standardwert „CA“ an. Erfahrene Programmierer empfehlen, dass alle Variablen standardmäßig am Ende der Parameterliste stehen sollten. Andernfalls ist die Ausführung nicht möglich und Sie müssen mit der Übergabe benannter Argumente arbeiten, was länger und komplexer ist.

4. Gespeicherte SQL Server-Prozeduren: Rückgabemethoden

Es gibt drei wichtige Möglichkeiten, Daten in einer aufgerufenen gespeicherten Prozedur zu senden. Sie sind unten aufgeführt:

Gibt den Wert einer gespeicherten Prozedur zurück;

Parameterausgabe der gespeicherten Prozedur;

Auswahl einer der gespeicherten Prozeduren.

4.1 Rückgabe von Werten aus gespeicherten SQL-Prozeduren

Bei dieser Technik weist eine Prozedur einer lokalen Variablen einen Wert zu und gibt ihn zurück. Eine Prozedur kann auch direkt einen konstanten Wert zurückgeben. Im folgenden Beispiel haben wir eine Prozedur erstellt, die die Gesamtzahl der Autoren zurückgibt. Wenn Sie dieses Verfahren mit den vorherigen vergleichen, können Sie feststellen, dass der Druckwert umgekehrt ist.

Sehen wir uns nun an, wie man eine Prozedur ausführt und ihren Rückgabewert ausgibt. Die Ausführung des Verfahrens erfordert das Setzen einer Variablen und das Drucken, was nach diesem gesamten Vorgang ausgeführt wird. Beachten Sie, dass Sie anstelle einer print-Anweisung auch eine Select-Anweisung verwenden können, z. B. Select @RetValue sowie OutputValue.

4.2 SQL Stored Procedure-Parameterausgabe

Der Antwortwert kann verwendet werden, um eine einzelne Variable zurückzugeben, was wir im vorherigen Beispiel gesehen haben. Mithilfe des Output-Parameters kann eine Prozedur einen oder mehrere Variablenwerte an den Aufrufer senden. Der Ausgabeparameter wird beim Erstellen einer Prozedur genau mit diesem Schlüsselwort „Output“ bezeichnet. Wird ein Parameter als Ausgabeparameter angegeben, muss ihm das Prozedurobjekt einen Wert zuweisen. Gespeicherte SQL-Prozeduren, von denen Beispiele unten zu sehen sind, geben in diesem Fall zusammenfassende Informationen zurück.

In unserem Beispiel gibt es zwei Ausgabenamen: @TotalAuthors und @TotalNoContract. Sie sind in der Parameterliste angegeben. Diese Variablen weisen Werte innerhalb des Prozedurkörpers zu. Wenn wir Ausgabeparameter verwenden, kann der Aufrufer den im Hauptteil der Prozedur festgelegten Wert sehen.

Außerdem werden im vorherigen Szenario zwei Variablen deklariert, um die Werte anzuzeigen, die die gespeicherten Prozeduren von MS SQL Server im Ausgabeparameter festgelegt haben. Anschließend wird das Verfahren durchgeführt, indem der Normalwert des Parameters „CA“ bereitgestellt wird. Die folgenden Parameter sind Ausgabeparameter und daher werden die deklarierten Variablen in der angegebenen Reihenfolge übergeben. Beachten Sie, dass bei der Übergabe von Variablen auch das Schlüsselwort „output“ hier festgelegt wird. Nach erfolgreichem Abschluss des Vorgangs werden die von den Ausgabeparametern zurückgegebenen Werte im Meldungsfenster angezeigt.

4.3 Auswahl einer der gespeicherten SQL-Prozeduren

Diese Technik wird verwendet, um eine Reihe von Werten als Datentabelle (RecordSet) an die aufrufende gespeicherte Prozedur zurückzugeben. In diesem Beispiel fragt die gespeicherte SQL-Prozedur mit @AuthID-Parametern die Tabelle „Autoren“ ab, indem sie die mithilfe dieses @AuthId-Parameters zurückgegebenen Datensätze filtert. Die Select-Anweisung entscheidet, was an den Aufrufer der gespeicherten Prozedur zurückgegeben werden soll. Wenn die gespeicherte Prozedur ausgeführt wird, wird die AuthId zurückgegeben. Dieses Verfahren hier liefert immer nur einen Datensatz oder gar keinen. Eine gespeicherte Prozedur unterliegt jedoch keinen Einschränkungen hinsichtlich der Rückgabe von mehr als einem Datensatz. Es ist nicht ungewöhnlich, Beispiele zu sehen, bei denen Daten mithilfe ausgewählter Parameter zurückgegeben werden, die berechnete Variablen umfassen, indem mehrere Summen bereitgestellt werden.

Abschließend

Die gespeicherte Prozedur ist ziemlich ernst Softwaremodul, Zurücksenden oder Senden sowie das Setzen der notwendigen Variablen dank der Client-Anwendung. Da die gespeicherte Prozedur selbst auf dem Server ausgeführt wird, können große Mengen an Datenaustausch zwischen dem Server und der Clientanwendung (für einige Berechnungen) vermieden werden. Dadurch können Sie die Belastung der SQL-Server reduzieren, was natürlich deren Besitzern zugute kommt. Einer der Untertypen sind gespeicherte T-SQL-Prozeduren, deren Untersuchung jedoch für diejenigen erforderlich ist, die beeindruckende Datenbanken erstellen. Es gibt auch eine große, sogar riesige Anzahl von Nuancen, die beim Studium gespeicherter Prozeduren nützlich sein können, aber dies ist eher für diejenigen erforderlich, die planen, sich mit der Programmierung zu befassen, auch beruflich.

Gespeicherte Prozedur - ein Datenbankobjekt, bei dem es sich um eine Reihe von SQL-Anweisungen handelt, die einmal kompiliert und auf dem Server gespeichert werden. Gespeicherte Prozeduren sind gewöhnlichen Hochsprachenprozeduren sehr ähnlich, sie können Eingabe- und Ausgabeparameter und lokale Variablen haben, sie können numerische Berechnungen und Operationen an Zeichendaten durchführen, deren Ergebnisse Variablen und Parametern zugewiesen werden können. Gespeicherte Prozeduren können ausgeführt werden Standardoperationen mit Datenbanken (sowohl DDL als auch DML). Darüber hinaus ermöglichen gespeicherte Prozeduren Schleifen und Verzweigungen, d. h. sie können Anweisungen zur Steuerung des Ausführungsprozesses verwenden.

Gespeicherte Prozeduren ähneln benutzerdefinierten Funktionen (UDFs). Der Hauptunterschied besteht darin, dass benutzerdefinierte Funktionen wie jeder andere Ausdruck in verwendet werden können SQL-Abfrage, während gespeicherte Prozeduren mit der CALL-Funktion aufgerufen werden müssen:

CALL-Prozedur(…)

EXECUTE-Prozedur(…)

Gespeicherte Prozeduren können mehrere Ergebnisse zurückgeben, also die Ergebnisse einer SELECT-Abfrage. Solche Ergebnismengen können mithilfe von Cursorn, anderen gespeicherten Prozeduren, die einen Ergebnismengenzeiger zurückgeben, oder Anwendungen verarbeitet werden. Gespeicherte Prozeduren können auch deklarierte Variablen zum Verarbeiten von Daten und Cursor enthalten, die Ihnen eine Schleife über mehrere Zeilen in einer Tabelle ermöglichen. Der SQL-Standard bietet IF, LOOP, REPEAT, CASE und viele andere, mit denen man arbeiten kann. Gespeicherte Prozeduren können Variablen akzeptieren, Ergebnisse zurückgeben oder Variablen ändern und zurückgeben, je nachdem, wo die Variable deklariert ist.

Die Implementierung gespeicherter Prozeduren variiert von DBMS zu DBMS. Die meisten großen Datenbankanbieter unterstützen sie in der einen oder anderen Form. Abhängig vom DBMS können gespeicherte Prozeduren in verschiedenen Programmiersprachen implementiert werden, beispielsweise SQL, Java, C oder C++. Gespeicherte Prozeduren, die nicht in SQL geschrieben sind, können SQL-Abfragen selbstständig ausführen oder auch nicht.

Hinter

Logik mit anderen Anwendungen teilen. Gespeicherte Prozeduren kapseln die Funktionalität; Dies bietet Konnektivität für den Datenzugriff und die Datenverwaltung über verschiedene Anwendungen hinweg.

Isolieren von Benutzern von Datenbanktabellen. Dadurch können Sie Zugriff auf gespeicherte Prozeduren gewähren, nicht jedoch auf die Tabellendaten selbst.

Bietet einen Schutzmechanismus. Wenn Sie gemäß dem vorherigen Punkt nur über gespeicherte Prozeduren auf Daten zugreifen können, kann niemand sonst Ihre Daten mit dem SQL-Befehl DELETE löschen.

Verbesserte Ausführung durch reduzierten Netzwerkverkehr. Mithilfe gespeicherter Prozeduren können mehrere Abfragen kombiniert werden.

Gegen

Erhöhte Belastung des Datenbankservers, da die meiste Arbeit auf der Serverseite und weniger auf der Clientseite ausgeführt wird.

Du wirst viel lernen müssen. Sie müssen die MySQL-Ausdruckssyntax erlernen, um Ihre gespeicherten Prozeduren schreiben zu können.

Sie duplizieren Ihre Anwendungslogik an zwei Stellen: Servercode und Code für gespeicherte Prozeduren, wodurch der Prozess der Datenmanipulation verkompliziert wird.

Die Migration von einem DBMS zu einem anderen (DB2, SQL Server usw.) kann zu Problemen führen.

Zweck und Vorteile gespeicherter Prozeduren

Gespeicherte Prozeduren verbessern die Leistung, erweitern die Programmierfunktionen und unterstützen Datensicherheitsfunktionen.

Anstatt eine häufig verwendete Abfrage zu speichern, können Clients auf die entsprechende gespeicherte Prozedur verweisen. Wenn eine gespeicherte Prozedur aufgerufen wird, wird ihr Inhalt sofort vom Server verarbeitet.

Mit gespeicherten Prozeduren können Sie nicht nur die Abfrage tatsächlich ausführen, sondern auch Berechnungen durchführen und Daten bearbeiten – ändern, löschen, DDL-Anweisungen ausführen (nicht in allen DBMS!) und andere gespeicherte Prozeduren aufrufen sowie komplexe Transaktionslogik ausführen. Mit einer einzigen Anweisung können Sie ein komplexes Skript aufrufen, das in einer gespeicherten Prozedur enthalten ist, und vermeiden so das Versenden Hunderter Befehle über das Netzwerk und insbesondere die Notwendigkeit, große Datenmengen vom Client zum Server zu übertragen.

In den meisten DBMS wird eine gespeicherte Prozedur bei der ersten Ausführung kompiliert (analysiert und ein Datenzugriffsplan generiert). Zukünftig erfolgt die Verarbeitung schneller. Das Oracle DBMS interpretiert gespeicherten prozeduralen Code, der im Datenwörterbuch gespeichert ist. Ab Oracle 10g wird die sogenannte native Kompilierung von gespeichertem prozeduralem Code in C und dann in den Maschinencode der Zielmaschine unterstützt, wonach beim Aufruf einer gespeicherten Prozedur deren kompilierter Objektcode direkt ausgeführt wird.

Programmierfähigkeiten

Die erstellte gespeicherte Prozedur kann jederzeit aufgerufen werden, was für Modularität sorgt und die Wiederverwendung von Code fördert. Letzteres erleichtert die Wartung der Datenbank, da sie von sich ändernden Geschäftsregeln isoliert wird. Sie können eine gespeicherte Prozedur jederzeit gemäß den neuen Regeln ändern. Danach werden alle Anwendungen, die es verwenden, automatisch an die neuen Geschäftsregeln angepasst, ohne dass direkte Änderungen erforderlich sind.

Sicherheit

Durch die Verwendung gespeicherter Prozeduren können Sie den direkten Benutzerzugriff auf Datenbanktabellen einschränken oder vollständig eliminieren, sodass Benutzer nur noch Berechtigungen zum Ausführen gespeicherter Prozeduren haben, die einen indirekten und streng regulierten Zugriff auf Daten ermöglichen. Darüber hinaus unterstützen einige DBMS die Textverschlüsselung (Wrapping) einer gespeicherten Prozedur.

Diese Sicherheitsfunktionen ermöglichen die Isolierung der Datenbankstruktur vom Benutzer und gewährleisten so die Integrität und Zuverlässigkeit der Datenbank.

Die Wahrscheinlichkeit von Aktionen wie SQL-Injection wird verringert, da gut geschriebene gespeicherte Prozeduren zusätzlich Eingabeparameter überprüfen, bevor sie die Abfrage an das DBMS übergeben.

Gespeicherte Prozeduren implementieren

Gespeicherte Prozeduren werden normalerweise mithilfe der SQL-Sprache oder ihrer spezifischen Implementierung im ausgewählten DBMS erstellt. Für diese Zwecke gibt es beispielsweise im Microsoft SQL Server DBMS die Transact-SQL-Sprache, in Oracle – PL/SQL, in InterBase und Firebird – PSQL, in PostgreSQL – PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, in IBM DB2 – SQL/PL (Englisch), in Informix – SPL. MySQL folgt ziemlich genau dem SQL:2003-Standard, seine Sprache ähnelt SQL/PL.

Einige DBMS ermöglichen die Verwendung gespeicherter Prozeduren, die in einer beliebigen Programmiersprache geschrieben sind, die unabhängige ausführbare Dateien erstellen kann, beispielsweise C++ oder Delphi. In der Terminologie von Microsoft SQL Server werden solche Prozeduren als erweiterte gespeicherte Prozeduren bezeichnet und sind einfach Funktionen, die in einer Win32-DLL enthalten sind. Und beispielsweise haben in Interbase und Firebird von DLL/SO aufgerufene Funktionen einen anderen Namen – UDF (User Defined Function). Mit MS SQL 2005 wurde die Möglichkeit eingeführt, gespeicherte Prozeduren in jeder .NET-Sprache zu schreiben, und es ist geplant, in Zukunft auf erweiterte gespeicherte Prozeduren zu verzichten. Das Oracle DBMS wiederum ermöglicht das Schreiben gespeicherter Prozeduren in Java. In IBM DB2 ist das Schreiben gespeicherter Prozeduren und Funktionen in herkömmlichen Programmiersprachen eine traditionelle Methode, die von Anfang an unterstützt wird, und die prozedurale SQL-Erweiterung wurde diesem DBMS erst in relativ späten Versionen nach seiner Aufnahme in den ANSI-Standard hinzugefügt. Informix unterstützt auch Prozeduren in Java und C.

Im Oracle DBMS können gespeicherte Prozeduren zu sogenannten Paketen zusammengefasst werden. Ein Paket besteht aus zwei Teilen – einer Paketspezifikation, die die Definition einer gespeicherten Prozedur angibt, und einem Pakethauptteil, der ihre Implementierung enthält. Auf diese Weise ermöglicht Ihnen Oracle die Trennung der Schnittstelle Programmcode aus seiner Umsetzung.

Im IBM DB2 DBMS können gespeicherte Prozeduren zu Modulen zusammengefasst werden.

Syntax

PROZEDUR `p2`() ERSTELLEN

SQL-SICHERHEITSDEFINER

KOMMENTAR „Ein Verfahren“

SELECT „Hallo Welt!“;

Der erste Teil des Codes erstellt eine gespeicherte Prozedur. Der nächste enthält optionale Parameter. Dann kommt der Name und schließlich der Hauptteil des Verfahrens selbst.

4 Merkmale einer gespeicherten Prozedur:

Sprache: Aus Gründen der Portabilität ist die Standardeinstellung SQL.

Deterministisch: wenn die Prozedur immer das gleiche Ergebnis zurückgibt und die gleichen Eingabeparameter verwendet. Dies dient dem Replikations- und Registrierungsprozess. Der Standardwert ist NICHT DETERMINISTISCH.

SQL-Sicherheit: Benutzerrechte werden während des Aufrufs überprüft. INVOKER ist der Benutzer, der die gespeicherte Prozedur aufruft. DEFINER ist der „Ersteller“ des Verfahrens. Der Standardwert ist DEFINER.

Kommentar: Zu Dokumentationszwecken ist der Standardwert „“

Aufrufen einer gespeicherten Prozedur

CALL gespeicherter_Prozedurname (param1, param2, ....)

CALL procedure1(10 , "string parameter" , @parameter_var);

Ändern einer gespeicherten Prozedur

MySQL verfügt über eine ALTER PROCEDURE-Anweisung zum Ändern von Prozeduren, die jedoch nur zum Ändern bestimmter Merkmale geeignet ist. Wenn Sie die Parameter oder den Hauptteil einer Prozedur ändern müssen, sollten Sie diese löschen und neu erstellen.

EntfernunggelagertVerfahren

DROP-VERFAHREN, WENN EXISTIERT p2;

Dies ist ein einfacher Befehl. Die IF EXISTS-Anweisung fängt einen Fehler ab, wenn eine solche Prozedur nicht existiert.

Optionen

PROZEDUR ERSTELLEN proc1(): leere Liste Parameter

CREATE PROCEDURE proc1 (IN varname DATA-TYPE): ein Eingabeparameter. Das Wort IN ist optional, da die Standardparameter IN (in) sind.

CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): Ein Parameter wurde zurückgegeben.

CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): ein Parameter, sowohl Eingabe als auch Rückgabe.

Die Syntax der Variablendeklaration sieht folgendermaßen aus:

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

Verfahrenserklärung

VERFAHREN ERSTELLEN [({IN|OUT|INOUT} [,…])]
[DYNAMISCHER ERGEBNISSATZ ]
BEGINNEN [Atomar]

ENDE

Stichworte
. IN (Eingabe) – Eingabeparameter
. OUT (Ausgabe) – Ausgabeparameter
. INOUT – Ein- und Ausgabe sowie ein Feld (ohne Parameter)
. DYNAMIC RESULT SET gibt an, dass die Prozedur eine angegebene Anzahl von Cursorn öffnen kann, die nach der Rückkehr der Prozedur geöffnet bleiben

Anmerkungen
Aufgrund der Netzwerk- und Stapelüberlastung wird die Verwendung vieler Parameter in gespeicherten Prozeduren (hauptsächlich große Zahlen und Zeichenfolgen) nicht empfohlen. In der Praxis weisen bestehende Dialekte von Transact-SQL, PL/SQL und Informix erhebliche Unterschiede zum Standard auf, sowohl bei der Deklaration und Verwendung von Parametern, bei der Deklaration von Variablen als auch beim Aufruf von Unterprogrammen. Microsoft empfiehlt die Verwendung der folgenden Näherung, um die Cachegröße der gespeicherten Prozedur abzuschätzen:
=(maximale Anzahl gleichzeitiger Benutzer)*(Größe des größten Ausführungsplans)*1,25. Die Größe des Ausführungsplans in Seiten kann mit dem Befehl DBCC MEMUSAGE bestimmt werden.

Aufrufen einer Prozedur

In vielen vorhandenen DBMS werden gespeicherte Prozeduren mit dem folgenden Operator aufgerufen:

PROZEDUR AUSFÜHREN [(][)]

Notiz: Aufrufe gespeicherter Prozeduren können aus einer Anwendung, einer anderen gespeicherten Prozedur oder interaktiv erfolgen.

Beispiel einer Prozedurdeklaration

CREATE PROCEDURE Proc1 AS //prozedur deklarieren
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //Deklarieren Sie den Cursor
OPEN Cur1 //den Cursor öffnen
FETCH NEXT FROM Cur1 //Daten vom Cursor lesen
WHILE @@Fetch_Status=0
BEGINNEN
NÄCHSTES VON Cur1 FETCH
ENDE
CLOSE Cur1 //Cursor schließen
Freigabe von Cur1 freigeben
EXECUTE Proc1 //Führen Sie die Prozedur aus

Polymorphismus
Zwei Unterprogramme mit demselben Namen können im selben Schema erstellt werden, wenn sich die Parameter der beiden Unterprogramme so weit voneinander unterscheiden, dass sie unterschieden werden können. Um zwischen zwei Routinen mit demselben Namen in derselben Schaltung zu unterscheiden, erhält jede eine Alternative und einzigartiger Name(spezifischer Name). Ein solcher Name kann bei der Definition des Unterprogramms explizit angegeben werden. Beim Aufruf von Unterprogrammen mit mehreren gleichen Namen erfolgt die Ermittlung des benötigten Unterprogramms in mehreren Schritten:
. Zunächst werden alle Prozeduren mit dem angegebenen Namen definiert, und wenn keine vorhanden sind, dann alle Funktionen mit dem angegebenen Namen.
. Für die weitere Analyse werden nur die Unterprogramme berücksichtigt, in Bezug auf die dieser Benutzer hat das Ausführungsprivileg (EXECUTE).
. Für sie werden diejenigen ausgewählt, deren Anzahl an Parametern der Anzahl der Aufrufargumente entspricht. Die angegebenen Datentypen der Parameter und deren Positionen werden überprüft.
. Wenn mehr als ein Unterprogramm übrig ist, wird das Unterprogramm ausgewählt, dessen qualifizierender Name kürzer ist.
In der Praxis unterstützt Oracle Polymorphismus für Funktionen, die nur im Paket DB@ - in deklariert sind anderes Schema, und in Sybase und MS SQL Server ist eine Überladung verboten.

Verfahren löschen und ändern
Um eine Prozedur zu entfernen, verwenden Sie den Operator:

Um eine Prozedur zu ändern, verwenden Sie den Operator:

VERFAHREN ÄNDERN [([{IN|OUT|INOUT}])]
BEGINNEN [Atomar]

ENDE

Berechtigungen zur Durchführung von Verfahren

GRANT EXECUTE ON ZU |ÖFFENTLICH [MIT ZUSCHUSSOPTION]

Systemprozeduren
Viele DBMS (einschließlich SQL Server) verfügen über einen bestimmten Satz integrierter gespeicherter Systemprozeduren, die Sie für Ihre eigenen Zwecke verwenden können.

Wir betrachten eine Situation, in der gespeicherte Prozeduren die Abfrageleistung beeinträchtigen können.

Beim Kompilieren gespeicherter Prozeduren in MS SQL Server 2000 werden die gespeicherten Prozeduren im Prozedurcache abgelegt, was die Leistung bei der Ausführung verbessern kann, da die Notwendigkeit, gespeicherten Prozedurcode zu analysieren, zu optimieren und zu kompilieren, entfällt.
Andererseits gibt es beim Speichern des kompilierten Codes einer gespeicherten Prozedur Fallstricke, die den gegenteiligen Effekt haben können.
Tatsache ist, dass beim Kompilieren einer gespeicherten Prozedur der Ausführungsplan der Anweisungen, aus denen der Prozedurcode besteht, kompiliert wird. Wenn die kompilierte gespeicherte Prozedur zwischengespeichert wird, wird dementsprechend auch ihr Ausführungsplan zwischengespeichert und die gespeicherte Prozedur daher nicht optimiert für eine bestimmte Situation und Abfrageparameter.
Lassen Sie uns ein kleines Experiment durchführen, um dies zu demonstrieren.

SCHRITT 1. Erstellen einer Datenbank.
Für das Experiment erstellen wir eine separate Datenbank.

Datenbank test_sp_perf erstellen
ON (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
ANMELDEN (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)

SCHRITT 2. Eine Tabelle erstellen.
TABELLE ERSTELLEN sp_perf_test(Spalte1 int, Spalte2 char(5000))

SCHRITT 3. Füllen der Tabelle mit Testzeilen. Einer Tabelle werden absichtlich doppelte Zeilen hinzugefügt. 10.000 Zeilen mit Zahlen von 1 bis 10.000 und 10.000 Zeilen mit Zahlen von 50.000.

DECLARE @i int
SETZE @i=1
Während ich<10000)
BEGINNEN
INSERT INTO sp_perf_test(column1, Column2) VALUES(@i,"Test string #"+CAST(@i as char(8)))
INSERT INTO sp_perf_test(column1, Column2) VALUES(50000,"Test string #"+CAST(@i as char(8)))
SETZE @i= @i+1
ENDE

SELECT COUNT(*) FROM sp_perf_test
GEHEN

SCHRITT 4. Erstellen eines nicht gruppierten Indexes. Da der Ausführungsplan mit der Prozedur zwischengespeichert wird, wird der Index bei allen Aufrufen auf die gleiche Weise verwendet.

ERSTELLEN SIE KEINEN KLUSTERED INDEX CL_perf_test ON sp_perf_test(column1)
GEHEN

SCHRITT 5. Erstellen einer gespeicherten Prozedur. Die Prozedur führt einfach eine SELECT-Anweisung mit einer Bedingung aus.

ERSTELLE PROC proc1 (@param int)
ALS
SELECT Spalte1, Spalte2 FROM sp_perf_test WHERE Spalte1=@param
GEHEN

SCHRITT 6. Ausführen einer gespeicherten Prozedur. Beim Ausführen einer anfälligen Prozedur wird speziell ein selektiver Parameter verwendet. Als Ergebnis des Verfahrens erhalten wir 1 Zeile. Der Ausführungsplan weist auf die Verwendung eines nicht gruppierten Indexes hin, weil Die Abfrage ist selektiv und dies ist die beste Möglichkeit, die Zeile abzurufen. Eine zum Abrufen einer einzelnen Zeile optimierte Prozedur wird im prozeduralen Cache gespeichert.

EXEC proc1 1234
GEHEN

SCHRITT 7. Führen Sie eine gespeicherte Prozedur mit einem nicht selektiven Parameter aus. Der als Parameter verwendete Wert ist 50000. In der ersten Spalte befinden sich etwa 10000 Zeilen mit diesem Wert. Daher ist die Verwendung eines nicht gruppierten Index und der Lesezeichen-Suchoperation wirkungslos, da der kompilierte Code mit dem Ausführungsplan jedoch darin gespeichert ist Der prozedurale Cache wird verwendet. Der Ausführungsplan zeigt dies sowie die Tatsache, dass eine Lesezeichen-Suchoperation für 9999 Zeilen durchgeführt wurde.

EXEC proc1 50000
GEHEN

SCHRITT 8. Durchführen einer Zeilenauswahl mit dem ersten Feld gleich 50000. Beim Ausführen einer separaten Abfrage wird die Abfrage optimiert und mit einem bestimmten Wert für die erste Spalte kompiliert. Infolgedessen stellt der Abfrageoptimierer fest, dass das Feld viele Male dupliziert wird, und entscheidet sich für die Verwendung des Tabellenscanvorgangs, der in diesem Fall wesentlich effizienter ist als die Verwendung eines nicht gruppierten Index.

SELECT Spalte1, Spalte2 FROM sp_perf_test WHERE Spalte1=50000
GEHEN

Daraus können wir schließen, dass die Verwendung gespeicherter Prozeduren möglicherweise nicht immer die Abfrageleistung verbessert. Sie sollten bei gespeicherten Prozeduren, die Ergebnisse mit einer variablen Anzahl von Zeilen bearbeiten und unterschiedliche Ausführungspläne verwenden, sehr vorsichtig sein.
Mit dem Skript können Sie das Experiment auf Ihrem MS SQL-Server wiederholen.