In diesem Thema wird beschrieben, wie Sie mithilfe der Transact-SQL CREATE PROCEDURE-Anweisung eine gespeicherte Transact-SQL-Prozedur in SQL Server Management Studio erstellen können.
Bevor Sie beginnen, führen Sie die folgenden Schritte aus. Berechtigungen
Erstellen Sie eine Prozedur mit: SQL Server Management Studio, Transact-SQL
Das Ausführen dieser Anweisung erfordert die CREATE PROCEDURE-Berechtigung für die Datenbank und die ALTER-Berechtigung für das Schema, in dem die Prozedur erstellt wird.
Sie können eine der folgenden Methoden verwenden:
SQL Server Management Studio
Verwenden von SQL Server Management Studio
Erstellen Sie eine Prozedur im Objekt-Explorer
Drück den Knopf OK.
IN Abfrageeditor Ersetzen Sie die SELECT-Anweisung durch die folgende Anweisung:
Um die Syntax zu überprüfen, wählen Sie Parsing auf der Speisekarte Anfrage. Wenn eine Fehlermeldung zurückgegeben wird, vergleichen Sie die Anweisungen mit den oben genannten und nehmen Sie gegebenenfalls Korrekturen vor.
Um eine Prozedur zu erstellen, im Menü Anfrage Menüpunkt wählen Ausführen. Die Prozedur wird als Objekt in der Datenbank angelegt.
Um den Vorgang im Objekt-Explorer anzuzeigen, klicken Sie mit der rechten Maustaste auf das Element Gespeicherte Prozeduren und auswählen Aktualisieren.
Um eine Prozedur auszuführen, klicken Sie im Objekt-Explorer mit der rechten Maustaste auf den Namen der gespeicherten Prozedur HumanResources.uspGetEmployeesTest und auswählen Ausführen einer gespeicherten Prozedur.
Im Fenster Ausführen des Verfahrens Geben Sie Margheim als Wert für den Parameter @LastName und Diane als Wert für den Parameter @FirstName ein.
IN Objekt-Explorer Stellen Sie eine Verbindung zu einer Instanz der Datenbank-Engine her und stellen Sie sie bereit.
Falten Sie den Knoten nacheinander auf Datenbank, Datenbank AdventureWorks2012 und Knoten Programmierung.
Klicken Sie mit der rechten Maustaste auf das Element Gespeicherte Prozeduren und auswählen Erstellen Sie eine gespeicherte Prozedur.
Auf der Speisekarte Anfrage Menüpunkt wählen Geben Sie Werte für Vorlagenparameter an.
Im Dialogfeld Festlegen von Werten für Vorlagenparameter Geben Sie die folgenden Werte für die angezeigten Parameter ein.
| Parameter | Bedeutung |
|---|---|
| Autor | Ihr Name |
| Erstelldatum | Heutiges Datum |
| Beschreibung | Gibt Daten über Mitarbeiter zurück. |
| Prozedurname | |
| @Param1 | @Familienname, Nachname |
| @Datatype_For_Param1 | nvarchar(50) |
| Default_Value_For_Param1 | NULL |
| @Param2 | @Vorname |
| @Datatype_For_Param2 | nvarchar(50) |
| Default_Value_For_Param2 | NULL |
Verwendung von Transact-SQL
Erstellen einer Prozedur im Abfrageeditor
IN Objekt-Explorer Stellen Sie eine Verbindung zu einer Instanz der Datenbank-Engine her.
Auf der Speisekarte Datei Menüpunkt wählen Erstellen Sie eine Anfrage.
Kopieren Sie das folgende Beispiel in das Abfragefenster und klicken Sie auf die Schaltfläche Ausführen. In diesem Beispiel wird dieselbe gespeicherte Prozedur wie zuvor erstellt, jedoch mit einem anderen Prozedurnamen.
VERWENDEN Sie AdventureWorks2012; GEHEN SIE ERSTELLEN VERFAHREN HumanResources .uspGetEmployeesTest2 @LastName nvarchar (50) , @FirstName nvarchar (50) AS SET NOCOUNT ON ; SELECT FirstName, LastName, Department FROM HumanResources.vEmployeeDepartmentHistory WHERE FirstName = @FirstName AND LastName = @LastName AND EndDate IS NULL; GEHEN
Um den Vorgang abzuschließen, kopieren Sie das folgende Beispiel in das Abfragefenster, das Sie erstellen, und klicken Sie auf die Schaltfläche Ausführen. Es werden verschiedene Methoden zum Einstellen von Parameterwerten gezeigt.
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_ und werden in gespeichert Systembasis Daten und kann 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.
MySQL 5 verfügt über viele neue Funktionen, eine der wichtigsten davon ist die Erstellung gespeicherter Prozeduren. In diesem Tutorial werde ich darüber sprechen, was sie sind und wie sie Ihnen das Leben erleichtern können.
Einführung
Eine gespeicherte Prozedur ist eine Möglichkeit, sich wiederholende Aktionen zu kapseln. Gespeicherte Prozeduren können Variablen deklarieren, den Datenfluss manipulieren und andere Programmiertechniken verwenden.
Der Grund für ihre Entstehung ist klar und wird durch häufige Verwendung bestätigt. Spricht man dagegen unregelmäßig mit denen, die mit ihnen zusammenarbeiten, spalten sich die Meinungen in zwei völlig gegensätzliche Seiten. Vergiss das 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.
Das Tool, mit dem ich arbeite, heißt MySQL Query Browser und ist ein Standardtool für die Interaktion mit Datenbanken. Werkzeug Befehlszeile MySQL ist eine weitere ausgezeichnete Wahl. Der Grund, warum ich Ihnen das erzähle, ist, dass jedermanns Lieblingsprogramm phpMyAdmin die Ausführung gespeicherter Prozeduren nicht unterstützt.
Übrigens verwende ich eine grundlegende Tabellenstruktur, um Ihnen das Verständnis dieses Themas zu erleichtern. Ich spreche von gespeicherten Prozeduren, und sie sind so komplex, dass man sich mit der umständlichen Tabellenstruktur befassen muss.
Schritt 1: Platzieren Sie einen Begrenzer
Ein Trennzeichen ist ein Zeichen oder eine Zeichenfolge, mit dem/der dem MySQL-Client angezeigt wird, dass Sie mit dem Schreiben des SQL-Ausdrucks fertig sind. Seit jeher dient das Semikolon als Trennzeichen. Es können jedoch Probleme auftreten, da eine gespeicherte Prozedur möglicherweise mehrere Ausdrücke enthält, die jeweils mit einem Semikolon enden müssen. In diesem Tutorial verwende ich die Zeichenfolge „//“ als Trennzeichen.
Schritt 2: So arbeiten Sie mit gespeicherten Prozeduren
Erstellen einer gespeicherten Prozedur
DELIMITER // PROZEDUR ERSTELLEN „p2“ () LANGUAGE SQL DETERMINISTISCHER SQL-SICHERHEITSDEFINER KOMMENTAR „Eine Prozedur“ BEGIN SELECT „Hallo Welt!“; ENDE//
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.
Bei Namen gespeicherter Prozeduren muss die Groß-/Kleinschreibung beachtet werden. Sie können auch nicht mehrere Prozeduren mit demselben Namen erstellen. Innerhalb einer gespeicherten Prozedur dürfen keine Ausdrücke vorhanden sein, die die Datenbank selbst ändern.
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
Um eine gespeicherte Prozedur aufzurufen, müssen Sie Folgendes eingeben: Stichwort CALL und dann der Name der Prozedur und in Klammern die Parameter (Variablen oder Werte). Klammern sind erforderlich.
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.
Entfernen einer gespeicherten Prozedur
DROP-VERFAHREN, WENN EXISTIERT p2;
Das einfacher Befehl. Die IF EXISTS-Anweisung fängt einen Fehler ab, wenn eine solche Prozedur nicht existiert.
Schritt 3: Optionen
Sehen wir uns an, wie wir Parameter an eine gespeicherte Prozedur übergeben können.
- CREATE PROCEDURE proc1(): leere Parameterliste
- 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.
Selbstverständlich können Sie mehrere Parameter unterschiedlichen Typs angeben.
Beispiel für IN-Parameter
DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; ENDE//
Beispiel-OUT-Parameter
DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = „Dies ist ein Test“; ENDE //
Beispiel für einen INOUT-Parameter
DELIMITER // PROZEDUR ERSTELLEN „proc_INOUT“ (OUT var1 INT) BEGIN SET var1 = var1 * 2; ENDE //
Schritt 4: Variablen
Jetzt zeige ich Ihnen, wie Sie Variablen erstellen und in Prozeduren speichern. Sie müssen sie zusammen mit ihren Datentypen explizit am Anfang des BEGIN/END-Blocks deklarieren. Sobald Sie eine Variable deklariert haben, können Sie sie auf die gleiche Weise wie Sitzungsvariablen, Literale oder Spaltennamen verwenden.
Die Syntax der Variablendeklaration sieht folgendermaßen aus:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;
Lassen Sie uns einige Variablen deklarieren:
DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE heute TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;
Arbeiten mit Variablen
Nachdem Sie eine Variable deklariert haben, können Sie ihren Wert mit den Befehlen SET oder SELECT festlegen:
DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE heute TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = „Ich bin ein String“; SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5; ENDE //
Schritt 5: Thread-Kontrollstrukturen
MySQL unterstützt IF-, CASE-, ITERATE-, LEAVE LOOP-, WHILE- und REPEAT-Konstrukte zur Steuerung von Threads innerhalb einer gespeicherten Prozedur. Wir werden uns die Verwendung von IF, CASE und WHILE ansehen, da diese am häufigsten verwendet werden.
IF-Design
Mit dem IF-Konstrukt können wir Aufgaben ausführen, die Bedingungen enthalten:
DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET Variable1 = Parameter1 + 1; WENN Variable1 = 0 DANN SELECT Variable1; ENDIF; WENN param1 = 0 DANN WÄHLEN Sie „Parameterwert = 0“; ELSE SELECT „Parameterwert<>0"; ENDE WENN; ENDE //
CASE-Design
CASE ist eine weitere Methode zum Testen von Bedingungen und zum Auswählen einer geeigneten Lösung. Dies ist eine großartige Möglichkeit, viele IF-Konstrukte zu ersetzen. Das Konstrukt kann auf zwei Arten beschrieben werden und bietet Flexibilität bei der Verwaltung mehrerer bedingter Ausdrücke.
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET Variable1 = Parameter1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); ENDGEHÄUSE; ENDE //
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET Variable1 = Parameter1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1); WHEN variable1 = 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); ENDGEHÄUSE; ENDE //
WHILE-Design
Technisch gesehen gibt es drei Arten von Schleifen: die WHILE-Schleife, die LOOP-Schleife und die REPEAT-Schleife. Sie können eine Schleife auch mithilfe der Programmiertechnik von Darth Vader durchführen: GOTO-Anweisungen. Hier ist eine Beispielschleife:
DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET-Variable1 = 0; WHILE-Variable1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //
Schritt 6: Cursor
Cursor werden verwendet, um die von einer Abfrage zurückgegebenen Zeilen zu durchlaufen und jede Zeile zu verarbeiten.
MySQL unterstützt Cursor in gespeicherten Prozeduren. Hier ist eine kurze Syntax zum Erstellen und Verwenden eines Cursors.
DECLARE Cursorname CURSOR FOR SELECT ...; /*Einen Cursor deklarieren und füllen */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Was tun, wenn keine Datensätze mehr vorhanden sind*/ OPEN Cursor-Name; /*Cursor öffnen*/ FETCH Cursorname INTO Variable [, Variable]; /*Weisen Sie einer Variablen einen Wert zu, der dem aktuellen Wert der Spalte entspricht*/ CLOSE Cursor-Name; /*Cursor schließen*/
In diesem Beispiel führen wir einige einfache Operationen mit einem Cursor aus:
DELIMITER // PROZEDUR ERSTELLEN „proc_CURSOR“ (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OFFEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; WENN b = 0, DANN SETZE c = c + a; ENDIF; ENDE WÄHREND; SCHLIEßEN cur1; SET param1 = c; ENDE //
Cursor haben drei Eigenschaften, die Sie verstehen müssen, um unerwartete Ergebnisse zu vermeiden:
- Nicht empfindlich: Ein einmal geöffneter Cursor spiegelt keine Änderungen in der Tabelle wider, die später auftreten. In Wirklichkeit garantiert MySQL nicht, dass der Cursor aktualisiert wird. Verlassen Sie sich also nicht darauf.
- Schreibgeschützt: Cursor können nicht geändert werden.
- Kein Zurückspulen: Der Cursor kann sich nur in eine Richtung bewegen – vorwärts. Sie können keine Zeilen überspringen, ohne sie auszuwählen.
Abschluss
In diesem Tutorial habe ich Ihnen die Grundlagen der Arbeit mit gespeicherten Prozeduren und einige der damit verbundenen spezifischen Eigenschaften vorgestellt. Natürlich müssen Sie Ihr Wissen in Bereichen wie Sicherheit, SQL-Ausdrücken und Optimierung vertiefen, bevor Sie ein echter MySQL-Prozedur-Guru werden.
Sie sollten die Vorteile der Verwendung gespeicherter Prozeduren in Ihrer spezifischen Anwendung berechnen und dann nur die erforderlichen Prozeduren erstellen. Im Allgemeinen verwende ich Prozeduren; Meiner Meinung nach lohnt es sich, sie aufgrund ihrer Sicherheit, Codepflege und des Gesamtdesigns in Projekte zu implementieren. Bedenken Sie außerdem, dass MySQL-Prozeduren noch in Arbeit sind. Erwarten Sie Verbesserungen hinsichtlich Funktionalität und Verbesserungen. Teilen Sie uns gerne Ihre Meinung mit.
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 zum Ausführen von Aktionen mit dem Systemverzeichnis oder zum Abrufen verwendet System Information. 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
[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 übernommen frühere Versionen SQL Server kann nicht als zuverlässiger Schutzalgorithmus angesehen werden – es gibt Dienstprogramme, mit denen Sie die Rückkonvertierung 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 0ū 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.
Letzte Aktualisierung: 14.08.2017
Eine Datenoperation stellt häufig eine Reihe von Anweisungen dar, die in einer bestimmten Reihenfolge ausgeführt werden müssen. Wenn Sie beispielsweise einen Produktkauf hinzufügen, müssen Sie Daten in die Bestelltabelle eingeben. Bevor Sie dies tun, müssen Sie jedoch prüfen, ob das von Ihnen gekaufte Produkt auf Lager ist. Möglicherweise müssen Sie eine Reihe zusätzlicher Bedingungen überprüfen. Das heißt, der Kaufprozess eines Produkts umfasst tatsächlich mehrere Aktionen, die in einer bestimmten Reihenfolge ausgeführt werden müssen. Und in diesem Fall wäre es optimaler, alle diese Aktionen in einem Objekt zu kapseln – gespeicherte Prozedur(gespeicherte Prozedur).
Das heißt, gespeicherte Prozeduren sind im Wesentlichen eine Reihe von Anweisungen, die als eine einzelne Einheit ausgeführt werden. Somit ermöglichen gespeicherte Prozeduren, komplexe Vorgänge zu vereinfachen und in einem einzigen Objekt unterzubringen. Der Kaufprozess eines Produkts wird sich ändern; dementsprechend reicht es aus, den Verfahrenscode zu ändern. Das heißt, das Verfahren vereinfacht auch die Codeverwaltung.
Mit gespeicherten Prozeduren können Sie außerdem den Zugriff auf Daten in Tabellen einschränken und so die Wahrscheinlichkeit absichtlicher oder unbeabsichtigter unerwünschter Aktionen in Bezug auf diese Daten verringern.
Und ein weiterer wichtiger Aspekt ist die Leistung. Gespeicherte Prozeduren werden normalerweise schneller ausgeführt als normale SQL-Anweisungen. Dies liegt daran, dass der Prozedurcode beim ersten Start einmal kompiliert und dann in kompilierter Form gespeichert wird.
Um eine gespeicherte Prozedur zu erstellen, verwenden Sie den Befehl CREATE PROCEDURE oder CREATE PROC.
Die gespeicherte Prozedur hat also drei Hauptmerkmale: Codevereinfachung, Sicherheit und Leistung.
Nehmen wir beispielsweise an, dass es in der Datenbank eine Tabelle gibt, in der Daten zu Produkten gespeichert sind:
CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);
Erstellen wir eine gespeicherte Prozedur, um Daten aus dieser Tabelle abzurufen:
USE productsdb; GEHEN SIE PROZEDUR ERSTELLEN Produktzusammenfassung WÄHLEN SIE PRODUKTNAME ALS Produkt, Hersteller, Preis AUS Produkte
Da der Befehl CREATE PROCEDURE in einem separaten Paket aufgerufen werden muss, folgt auf den Befehl USE, der die aktuelle Datenbank festlegt, ein Befehl GO, um ein neues Paket zu definieren.
Dem Prozedurnamen muss das Schlüsselwort AS folgen.
Um den Hauptteil einer Prozedur vom Rest des Skripts zu trennen, wird der Prozedurcode häufig in einem BEGIN...END-Block platziert:
USE productsdb; LOS PROZEDUR ERSTELLEN Produktzusammenfassung ANFANG SELECT Produktname ALS Produkt, Hersteller, Preis VON PRODUKTE ENDE;
Nachdem wir die Prozedur hinzugefügt haben, können wir sie im Datenbankknoten in SQL Server Management Studio im Unterknoten sehen Programmierbarkeit -> Gespeicherte Prozeduren:
Und wir werden den Vorgang auch über eine visuelle Schnittstelle steuern können.
Ausführen des Verfahrens
Um eine gespeicherte Prozedur auszuführen, rufen Sie den Befehl EXEC oder EXECUTE auf:
EXEC-Produktübersicht
Entfernen einer Prozedur
Um eine Prozedur zu entfernen, verwenden Sie den Befehl DROP PROCEDURE:
DROP-VERFAHREN Produktzusammenfassung
