Gespeicherte MySQL-Prozeduren (Erstellen, Auflisten, Ändern und Löschen)

Einführung

Gespeicherte MySQL-Prozeduren gruppieren mehrere Aufgaben in einen und speichern Sie die Aufgabe auf dem Server für die zukünftige Verwendung.

Gespeicherte Prozeduren vereinfachen die Datenbankverwaltung und reduzieren den Netzwerkverkehr. Wenn Sie beispielsweise eine Abfrage an den MySQL-Server senden, wird die Abfrage verarbeitet und die Ergebnisse zurückgegeben. Durch die Verwendung gespeicherter Prozeduren werden die Abfragen auf dem Server gespeichert, damit sie später ausgeführt werden können.

In diesem Lernprogramm lernen Sie, gespeicherte Prozeduren zu erstellen, aufzulisten, zu ändern und zu löschen.

Voraussetzungen

• MySQL Server und MySQL Workbench installiert
• Ein MySQL-Benutzerkonto mit Root-Rechten

Was sind gespeicherte Prozeduren in MySQL?

Gespeicherte MySQL-Prozeduren sind vorkompiliert SQL-Anweisungen in einer Datenbank gespeichert. Sie sind Subroutinen, die einen Namen, eine Parameterliste und SQL-Anweisungen enthalten.

Alle relationalen Datenbanksysteme unterstützen gespeicherte Prozeduren und benötigen keine zusätzlichen Laufzeitumgebungspakete.

Wie werden gespeicherte Prozeduren verwendet?

Um gespeicherte Prozeduren aufzurufen, können Sie CALL verwenden -Anweisung oder andere gespeicherte Prozeduren. Wenn eine gespeicherte Prozedur zum ersten Mal aufgerufen wird, sucht MySQL sie im Datenbankkatalog, kompiliert den Code und platziert ihn im Cache-Speicher , und führt es aus.

Nachfolgende Läufe in derselben Sitzung führen gespeicherte Prozeduren aus dem Cache-Speicher aus, was sie für sich wiederholende Aufgaben äußerst nützlich macht.

Gespeicherte Prozeduren verwenden Parameter um Werte zu übergeben und Ergebnisse anzupassen. Parameter werden verwendet, um die Spalten in einer Tabelle anzugeben, in der die Abfrage ausgeführt wird und Ergebnisse zurückgibt.

Gespeicherte Prozeduren können auch das IF enthalten , CASE und LOOP Ablaufsteuerungsanweisungen die den Code prozedural implementieren.

Gespeicherte Prozedur erstellen

Erstellen Sie eine gespeicherte Prozedur auf zwei Arten:

1. MySQL-Shell verwenden

Verwenden Sie die folgende Syntax, um eine gespeicherte Prozedur in MySQL zu erstellen:

DELIMITER //
CREATE PROCEDURE procedure_name ( IN | OUT | INOUT parameter_name parameter_datatype (length), … )
BEGIN    
    SQL statements
END //
DELIMITER ;

Standardmäßig ist die Syntax der verwendeten Datenbank zugeordnet, aber Sie können die Syntax auch für eine andere Datenbank verwenden, indem Sie den Datenbanknamen wie folgt angeben:database_name.procedure_name .

Hier das erste DELIMITER -Argument setzt das Standardtrennzeichen auf // , während das letzte DELIMITER Argument setzt es auf das Semikolon ; zurück . Um mehrere Anweisungen zu verwenden, geben Sie unterschiedliche Trennzeichen wie $$ an .

Der Prozedurname kommt nach CREATE PROCEDURE Streit. Verwenden Sie nach dem Prozedurnamen Klammern, um die in der Prozedur zu verwendenden Parameter, den Namen des Parameters, den Datentyp und die Datenlänge anzugeben. Trennen Sie jeden Parameter durch ein Komma.

Die Parametermodi sind:

• IN – Wird verwendet, um einen Parameter als Eingabe zu übergeben. Wenn sie definiert ist, übergibt die Abfrage ein Argument an die gespeicherte Prozedur. Der Wert des Parameters ist immer geschützt.
• OUT – Verwenden, um einen Parameter als Ausgabe zu übergeben. Sie können den Wert innerhalb der gespeicherten Prozedur ändern, und der neue Wert wird an das aufrufende Programm zurückgegeben.
• INOUT – Eine Kombination aus IN und OUT Parameter. Das aufrufende Programm übergibt das Argument, und die Prozedur kann INOUT ändern Parameter und übergibt den neuen Wert an das Programm zurück.

Zum Beispiel:

Führen Sie die gespeicherte Prozedur aus, indem Sie sie aufrufen:

CALL procedure_name;

Die Abfrage gibt Ergebnisse für die gespeicherte Prozedur zurück.

2. MySQL-Workbench verwenden

Eine andere Möglichkeit, eine gespeicherte Prozedur zu erstellen, ist die Verwendung des MySQL Workbench-Assistenten. Der Assistent ist intuitiv und vereinfacht den Vorgang, da Sie keine Trennzeichen setzen oder sich um das Format kümmern müssen.

Befolgen Sie diese Schritte:

Schritt 1: Klicken Sie mit der rechten Maustaste auf Gespeicherte Prozeduren im Navigator-Fenster von MySQL Workbench und wählen Sie Create Stored Procedure… um den Assistenten zu starten.

Schritt 2: Geben Sie den Prozedurnamen an und geben Sie den Code innerhalb des BEGIN ein … ENDE blockieren.

Schritt 3: Überprüfen Sie den Code und klicken Sie auf Anwenden .

Schritt 4: Bestätigen Sie die Ausführung, indem Sie auf Übernehmen klicken und erstellen Sie die Prozedur, indem Sie auf Fertig stellen klicken .

Schritt 5: Führen Sie das Verfahren aus, um zu sehen, ob es funktioniert. Erstellen Sie eine neue SQL-Registerkarte zum Ausführen von Abfragen.

Schritt 6: CALL die Prozedur auf der Registerkarte „SQL“ und klicken Sie auf Ausführen .

Wenn keine Fehler zurückgegeben werden, führt MySQL die gespeicherte Prozedur aus und zeigt die Ergebnisse an.

Gespeicherte Prozeduren auflisten

Es gibt drei Möglichkeiten, eine Liste aller gespeicherten Prozeduren anzuzeigen:

1. MySQL-Shell verwenden

Verwenden Sie die folgende Syntax, um eine Liste aller gespeicherten Prozeduren zu erhalten, auf die Sie Zugriff haben, einschließlich ihrer Eigenschaften:

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]

Der SHOW PROCEDURE STATUS -Anweisung gibt eine lange Ausgabe zurück. Die Anweisung zeigt die Namen und Merkmale gespeicherter Prozeduren an, auf die Sie auf dem Server Zugriff haben.

Scrollen Sie durch die Ausgabe, um die Prozeduren zu finden, die sich derzeit auf dem Server befinden.

Das LIKE Argument findet gespeicherte Prozeduren, die ein bestimmtes Wort in ihrem Namen enthalten. Verwenden Sie % um eine beliebige Anzahl von Zeichen zu ersetzen, einschließlich Null.

Zum Beispiel:

Das WHERE Mit dem Argument können Sie gespeicherte Prozeduren nur in einer bestimmten Datenbank auflisten.

Zum Beispiel:

In diesem Beispiel gibt die Anweisung nur die gespeicherten Prozeduren für die ’Kundenliste zurück ’ Datenbank.

2. Datenwörterbuch verwenden

Das information_schema Die Datenbank enthält eine Tabelle namens Routinen , das Informationen zu gespeicherten Prozeduren und Funktionen enthält, die sich auf alle Datenbanken auf dem aktuellen MySQL-Server beziehen.

Verwenden Sie die folgende Syntax, um alle gespeicherten Prozeduren für eine Datenbank anzuzeigen:

SELECT 
    routine_name
FROM
    information_schema.routines
WHERE
    routine_type = 'PROCEDURE'
        AND routine_schema = 'database_name';

3. MySQL-Workbench verwenden

Verwenden Sie für einen GUI-Ansatz zum Anzeigen gespeicherter Prozeduren MySQL Workbench. Befolgen Sie diese Schritte, um gespeicherte Prozeduren anzuzeigen:

Schritt 1 :Doppelklicken Sie im Navigator auf die Datenbank, die Sie verwenden möchten Abschnitt.

Schritt 2 :Erweitern Sie die gespeicherten Prozeduren Dropdown-Element.

Dieses Element zeigt alle gespeicherten Prozeduren für die aktuelle Datenbank.

Gespeicherte Prozedur ändern

Eine gespeicherte Prozedur zu ändern bedeutet, die Eigenschaften zu ändern eines Verfahrens. Es gibt keine Aussage in MySQL zum Ändern der Parameter oder des Bodys einer gespeicherten Prozedur. Um Parameter oder den Körper zu ändern, löschen Sie die gespeicherte Prozedur und erstellen Sie eine neue.

Ändern Sie eine gespeicherte Prozedur auf zwei Arten:

1. MySQL-Shell verwenden

Ändern Sie eine Prozedureigenschaft mithilfe von ALTER PROCEDURE Erklärung. Beispielsweise können wir einer Prozedur, die wir zuvor erstellt haben, einen Kommentar hinzufügen. Die Syntax lautet:

ALTER PROCEDURE procedure_name
COMMENT 'Insert comment here';

2. MySQL-Workbench verwenden

Die Benutzeroberfläche von MySQL Workbench ermöglicht es Benutzern, eine gespeicherte Prozedur zu ändern, bei der Benutzer Parameter hinzufügen oder den Code ändern können. MySQL Workbench wird gelöscht die vorhandene gespeicherte Prozedur und erstellt eine neue, nachdem die Änderungen vorgenommen wurden.

Befolgen Sie diese Schritte:

Schritt 1: Klicken Sie im Abschnitt Navigator mit der rechten Maustaste auf die gespeicherte Prozedur, die Sie ändern möchten. Wählen Sie Gespeicherte Prozedur ändern… aus Artikel.

Schritt 2: Wenn die Registerkarte geöffnet wird, nehmen Sie die gewünschten Änderungen an der vorhandenen gespeicherten Prozedur vor und klicken Sie auf Übernehmen .

Schritt 3: Ein SQL-Skript-Überprüfungsfenster erscheint und zeigt den Vorgang – Löschen der vorhandenen gespeicherten Prozedur und Erstellen einer neuen, die die Änderungen enthält.

Klicken Sie auf Anwenden und dann Fertig stellen im nächsten Fenster, um das Skript auszuführen.

Gespeicherte Prozedur löschen

So löschen (löschen) Sie eine Prozedur:

1. MySQL-Shell verwenden

Löschen Sie eine gespeicherte Prozedur mithilfe des DROP PROCEDURE vom Server Aussage.

Die grundlegende Syntax ist:

DROP PROCEDURE [IF EXISTS] stored_procedure_name;

Der IF EXISTS -Parameter löscht die gespeicherte Prozedur nur, wenn sie auf dem Server vorhanden ist. Geben Sie anstelle von stored_procedure_name den Namen der gespeicherten Prozedur ein Syntax.

Zum Beispiel:

Da es keine Prozedur mit dem Namen „test“ gibt ’ auf dem Server besagt die Ausgabe, dass 0 Zeilen betroffen waren und dass die angegebene Prozedur nicht existiert.

Löschen einer nicht existierenden Prozedur ohne IF EXISTS Parameter gibt einen Fehler zurück.

2. MySQL-Workbench verwenden

Gehen Sie folgendermaßen vor, um eine gespeicherte Prozedur mit MySQL Workbench zu löschen:

Schritt 1: Erweitern Sie das Element Gespeicherte Prozeduren im Abschnitt Navigator. Klicken Sie mit der rechten Maustaste auf die gespeicherte Prozedur, die Sie löschen möchten, und wählen Sie Gespeicherte Prozedur löschen… im Kontextmenü.

Schritt 2: Klicken Sie im Bestätigungsfenster auf Jetzt löschen um die gespeicherte Prozedur zu löschen.

Diese Aktion wird dauerhaft gelöscht das Verfahren.

Vor- und Nachteile gespeicherter MySQL-Prozeduren

Gespeicherte Prozeduren haben mehrere Vor- und Nachteile, da sie auf bestimmte Anforderungen zugeschnitten sind. Nachfolgend sind einige der Vor- und Nachteile aufgeführt.

Vorteile der Verwendung gespeicherter Prozeduren

Die Vorteile gespeicherter Prozeduren sind:

Reduzierung des Netzwerkverkehrs

Gespeicherte Prozeduren tragen dazu bei, den Netzwerkverkehr zwischen Anwendungen und MySQL Server zu reduzieren, indem die gesamte Programmierlogik auf dem Server bleibt. Anstatt mehrere Abfrageergebnisse über das Netzwerk zu senden, senden Apps nur den Prozedurnamen und die Parametereingabe.

Verbesserte Sicherheit

Der Datenbankadministrator gewährt Apps Berechtigungen, um nur bestimmte gespeicherte Prozeduren aufzurufen und darauf zuzugreifen, ohne ihnen direkten Zugriff auf Tabellen zu gewähren. Gespeicherte Prozeduren helfen, Angriffe durch Skripteinschleusung zu verhindern, da Eingabeparameter als Werte und nicht als ausführbarer Code behandelt werden.

Zentralisierte Geschäftslogik

Gespeicherte Prozeduren kapseln die Geschäftslogik, die von mehreren Anwendungen wiederverwendet werden kann. Dadurch wird die Duplizierung derselben Logik in vielen verschiedenen Anwendungen reduziert und die Datenbank konsistenter.

Nachteile der Verwendung gespeicherter Prozeduren

Die Nachteile gespeicherter Prozeduren sind:

Ressourcennutzung

Die Verwendung vieler gespeicherter Prozeduren und logischer Operationen führt dazu, dass die Speicher- und CPU-Auslastung für jede Verbindung erheblich ansteigt.

Keine Portabilität

Es ist nicht einfach, gespeicherte Prozeduren, die in einer bestimmten Sprache geschrieben sind, von einer Installation auf eine andere zu portieren. Das Verlassen auf eine gespeicherte Prozedur bindet den Benutzer auch an eine bestimmte Datenbank.

Fehlerbehebung und Tests

MySQL bietet keine Dienstprogramme zum Testen und Debuggen von gespeicherten Prozeduren, daher kann es schwierig sein, sie zu debuggen. Das Entwickeln und Pflegen gespeicherter Prozeduren erfordert umfassende Kenntnisse. Dies ist eine Herausforderung für neue Entwickler und führt zu zusätzlichen Wartungskosten.