Gespeicherte Abläufe in MySQL
Einführung
Wenn Sie mit einer relationalen Datenbank arbeiten, geben Sie einzelne SQL (Structured Query Language)-Abfragen direkt im Anwendungscode ein, um Daten abzurufen oder zu manipulieren. Diese lauten z.B. SELECT, INSERT, UPDATE oder DELET. Diese Anweisungen greifen direkt auf die zugrunde liegenden Datenbanktabellen zu und bearbeiten sie. Wenn dieselben Anweisungen oder Anweisungsgruppen in mehreren Anwendungen verwendet werden, die auf dieselbe Datenbank zugreifen, werden sie oft in einzelnen Anwendungen dupliziert.
MySQL, wie viele andere relationale Datenbankverwaltungssysteme, unterstützt die Verwendung von gespeicherten Abläufe in MySQL. Sie helfen dabei, eine oder mehrere SQL-Anweisungen zur Wiederverwendung unter einem gemeinsamen Namen zu gruppieren und die häufig verwendete Geschäftslogik direkt in der Datenbank zu kapseln. Solche Abläufe können von der Anwendung, die auf die Datenbank zugreift, aufgerufen werden, um Daten auf konsistente Weise abzurufen oder zu manipulieren.
Mit gespeicherten Abläufe in MySQL können Sie widerverwendbare Routinen für gängige Aufgaben erstellen, die in mehreren Anwendungen verwendet werden können. Sie können Datenvalidierung durchführen oder eine zusätzliche Sicherheitsebene hinzufügen, indem Sie verhindern, dass Datenbankbenutzer direkt auf die zugrunde liegenden Tabellen zugreifen und beliebige Abfragen ausführen.
In diesem Tutorial erfahren Sie, was gespeicherte Abläufe in MySQL sind und wie Sie diese erstellen, die Daten zurückgeben und sowohl Eingabe- als auch Ausgabeparameter verwenden.
Voraussetzungen
Um diesem Leitfaden zu folgen, benötigen Sie einen Computer, der ein SQL-basiertes relationales Datenbankverwaltungssystem (RDBMS) ausführt. Die Anweisungen und Beispiele in diesem Leitfaden wurden in der folgenden Umgebung getestet:
- Ein Server, der Ubuntu 20.04 mit einem Nicht-Root-Benutzer mit administrativen Rechten und einer Firewall konfiguriert mit UFW ausführt, wie in unserem Initial-Server-Setup-Leitfaden für Ubuntu 20.04 beschrieben.
- MySQL, installiert und gesichert auf dem Server, wie in „How To Install MySQL on Ubuntu 20.04“ beschrieben.
- Grundlegende Kenntnisse zur Ausführung von SELECT-Abfragen, um Daten aus der Datenbank abzurufen, wie im Leitfaden „How To SELECT Rows FROM Tables in SQL“ beschrieben.
Hinweis: Beachten Sie, dass viele RDBMS ihre eigenen, einzigartigen Implementierungen von SQL verwenden und die Syntax von Stored Procedures nicht zum offiziellen SQL-Standard gehört. Die in diesem Tutorial beschriebenen Befehle funktionieren möglicherweise auch in anderen RDBMS. Allerdings sind gespeicherte Abläufe datenbankspezifisch, sodass sich Syntax oder Ausgabe auf einem anderen System als MySQL unterscheiden können.
Sie benötigen auch eine leere Datenbank, in der Sie Tabellen erstellen können, um die Verwendung von gespeicherten Abläufen in MySQL zu demonstrieren. Wir empfehlen Ihnen, den folgenden Abschnitt „Verbindung zu MySQL und Einrichten einer Beispieldatenbank“ durchzugehen, um Details zur Verbindung mit einem MySQL-Server und zur Erstellung der Testdatenbank zu erhalten, die in den Beispielen in diesem Leitfaden verwendet wird.
Verbindung zu MySQL und Einrichten einer Beispieldatenbank
In diesem Abschnitt stellen Sie eine Verbindung zu einem MySQL-Server. Des Weiteren erstellen Sie eine Beispieldatenbank, damit Sie die Beispiele in diesem Leitfaden nachvollziehen können.
Für diesen Leitfaden verwenden Sie eine imaginäre Autosammlung. Sie speichern Details über derzeit besessene Autos, einschließlich Marke, Modell, Baujahr und Wert.
Arbeitet Ihr SQL-Datenbanksystem auf einem entfernten Server, melden Sie sich von Ihrem lokalen Rechner aus per SSH an.
ssh sammy@your_server_ip
Öffnen Sie dann die MySQL-Server-Eingabeaufforderung und ersetzen Sie „sammy“ durch den Namen Ihres MySQL-Benutzerkontos:
mysql -u sammy -p
Erstellen Sie eine Datenbank mit dem Namen „procedures“:
CREATE DATABASE procedures;
Wenn die Datenbank erfolgreich erstellt wurde, erhalten Sie eine Ausgabe wie diese:
Ausgabe
Query OK, 1 row affected (0.01 sec)
Um die Datenbank „procedures“ auszuwählen, führen Sie die folgende USE-Anweisung aus:
USE procedures;
Sie erhalten die folgende Ausgabe:
Ausgabe
Database changed
Nach der Auswahl der Datenbank können Sie darin Beispieltabellen erstellen. Die Tabelle „cars“ enthält vereinfachte Daten über Autos in der Datenbank und umfasst folgende Spalten:
- make: Diese Spalte speichert die Marke jedes besessenen Autos, ausgedrückt mit dem Datentyp varchar und einer maximalen Länge von 100 Zeichen.
- model: Diese Spalte speichert den Modellnamen des Autos, ebenfalls ausgedrückt mit varchar und maximal 100 Zeichen.
- year: Diese Spalte speichert das Baujahr des Autos mit dem Datentyp int zur Speicherung numerischer Werte.
- value: Diese Spalte speichert den Wert des Autos als Dezimalwert mit maximal 10 Stellen und 2 Stellen nach dem Dezimalpunkt.
Erstellen Sie die Beispieltabelle mit dem folgenden Befehl:
CREATE TABLE cars (
make varchar(100),
model varchar(100),
year int,
value decimal(10, 2)
);
Erscheint die folgende Ausgabe, hat das System die Tabelle erfolgreich erstellt:
Ausgabe
Query OK, 0 rows affected (0.00 sec)
Laden Sie anschließend einige Beispieldaten in die Tabelle „cars“ ein, indem Sie die folgende INSERT INTO-Operation ausführen:
INSERT INTO cars
VALUES
('Porsche', '911 GT3', 2020, 169700),
('Porsche', 'Cayman GT4', 2018, 118000),
('Porsche', 'Panamera', 2022, 113200),
('Porsche', 'Macan', 2019, 27400),
('Porsche', '718 Boxster', 2017, 48880),
('Ferrari', '488 GTB', 2015, 254750),
('Ferrari', 'F8 Tributo', 2019, 375000),
('Ferrari', 'SF90 Stradale', 2020, 627000),
('Ferrari', '812 Superfast', 2017, 335300),
('Ferrari', 'GTC4Lusso', 2016, 268000);
Die INSERT INTO-Operation fügt zehn Sportwagen als Beispiel zur Tabelle hinzu, mit fünf Porsche- und fünf Ferrari-Modellen. Die folgende Ausgabe zeigt an, dass alle zehn Zeilen hinzugefügt wurden:
Ausgabe
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
Damit sind Sie bereit, den Rest des Leitfadens zu befolgen und Stored Procedures in SQL zu verwenden.
Einführung in Stored Procedures
Gespeicherte Abläufe in MySQL sind benannte Objekte, die eine oder mehrere Anweisungen enthalten und bei Aufruf von der Datenbank in einer bestimmten Reihenfolge ausgeführt werden. Im einfachsten Beispiel kann ein gespeicherter Ablauf in MySQL eine häufig verwendete Anweisung unter einer widerverwendbaren Routine speichern, z. B. das Abrufen von Daten aus der Datenbank mit häufig verwendeten Filtern. In den komplexesten Fällen können gespeicherte Abläufe in MySQL umfangreiche Programme darstellen, die komplexe Geschäftslogiken für robuste Anwendungen beschreiben.
Die Anweisungen können gängige SQL-Anweisungen wie SELECT- oder INSERT-Abfragen enthalten, die Daten zurückgeben oder bearbeiten. Zusätzlich können gespeicherte Abläufe Folgendes nutzen:
- Parameter, die an die gespeicherte Abläufe übergeben oder durch diese zurückgegeben werden.
- Deklarierte Variablen zur Verarbeitung abgerufener Daten direkt im Code der Prozedur.
- Bedingte Anweisungen, die die Ausführung bestimmter Teile des Codes je nach Bedingung erlauben, z. B. IF- oder CASE-Anweisungen.
- Schleifen wie WHILE, LOOP und REPEAT, die den Code mehrfach ausführen, z. B. für jede Zeile in einem abgerufenen Datensatz.
- Fehlerbehandlungsanweisungen, wie das Zurückgeben von Fehlermeldungen an die Datenbankbenutzer, die auf die Prozedur zugreifen.
- Aufrufe anderer Stored Procedures in der Datenbank.
Hinweis: Die umfangreiche Syntax, die MySQL unterstützt, ermöglicht das Schreiben robuster Programme und das Lösen komplexer Probleme. Dieser Leitfaden behandelt nur die grundlegende Verwendung von gespeicherten Abläufen in MySQL mit SQL-Anweisungen, die im Prozedurkörper eingeschlossen sind, sowie Eingabe- und Ausgabeparameter. Die Ausführung bedingten Codes, die Verwendung von Variablen, Schleifen und angepasster Fehlerbehandlung liegen außerhalb des Umfangs dieses Leitfadens.
Wenn die Prozedur durch ihren Namen aufgerufen wird, führt die Datenbank-Engine sie wie definiert Schritt für Schritt aus.
Der Datenbankbenutzer muss über die entsprechenden Berechtigungen zur Ausführung der gegebenen Prozedur verfügen. Diese Anforderung an Berechtigungen stellt eine Sicherheitsebene dar, indem direkter Datenbankzugriff verhindert wird, während Benutzern Zugang zu sicheren, ausführbaren Prozeduren gewährt wird.
Gespeicherte Abläufe in MySQL werden direkt auf dem Datenbankserver ausgeführt. Alle Berechnungen werden lokal durchgeführt und die Ergebnisse erst nach Abschluss an den aufrufenden Benutzer zurückgegeben.
Wenn Sie das Verhalten der Prozedur ändern möchten, können Sie die Prozedur in der Datenbank aktualisieren, und die Anwendungen, die sie verwenden, übernehmen automatisch die neue Version. Alle Benutzer werden sofort den neuen Code der Prozedur verwenden, ohne ihre Anwendungen anpassen zu müssen.
Hier ist die allgemeine Struktur des SQL-Codes zum Erstellen einer Stored Procedure:
DELIMITER //
CREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n)
BEGIN
instruction_1;
instruction_2;
. . .
instruction_n;
END //
DELIMITER ;
Die ersten und letzten Anweisungen in diesem Codeausschnitt sind DELIMITER // und DELIMITER ;. Normalerweise verwendet MySQL das Semikolonzeichen (;) zum Trennen von Anweisungen und zur Angabe des Start- und Endzeitpunkts. Wenn Sie mehrere Anweisungen in der MySQL-Konsole eingeben, die mit Semikolons getrennt sind, werden diese als separate Befehle behandelt und nacheinander unabhängig ausgeführt. Ein gespeicherter Ablauf kann jedoch mehrere Befehle einschließen, die bei ihrem Aufruf nacheinander ausgeführt werden sollen. Dies stellt eine Schwierigkeit dar, wenn Sie MySQL mitteilen möchten, einen neuen Ablauf zu erstellen. Die Datenbank-Engine würde das Semikolonzeichen sehen und denken, dass sie die Anweisung stoppen soll. In diesem Fall ist die beabsichtigte Anweisung jedoch der gesamte Erstellungscode und nicht eine einzelne Anweisung, sodass MySQL Ihre Absicht missverstehen würde.
Um dieses Problem zu umgehen, verwenden Sie den Befehl DELIMITER, um den Delimiter vorübergehend von ; auf // für die Dauer des CREATE PROCEDURE-Befehls zu ändern. Dann werden alle Semikolons unverändert an den Server übermittelt. Nachdem dies vollständig ist, ändern Sie den Delimiter mit dem letzten DELIMITER ; wieder auf ; .
Das Herzstück des Codes zur Erstellung einer neuen Prozedur ist der CREATE PROCEDURE-Befehl gefolgt vom Namen der Prozedur: „procedure_name“ im Beispiel. Der Prozedurname wird gefolgt von einer optionalen Liste von Parametern, die die Abläufe akzeptieren soll. Der letzte Teil ist der Ablaufkörper, eingeschlossen in BEGIN- und END-Anweisungen. Darin befindet sich der Code, der eine einzelne SQL-Anweisung wie eine SELECT-Abfrage oder komplexeren Code enthalten kann.
Die END-Anweisung endet mit //, einem temporären Delimiter, anstelle eines typischen Semikolons.
Im nächsten Abschnitt erstellen Sie einen grundlegenden gespeicherten Ablauf in MySQL ohne Parameter, die eine einzelne Abfrage umschließt.
Erstellen eines gespeicherten Ablaufs in MySQL ohne Parameter
In diesem Abschnitt erstellen Sie Ihre erste gespeicherten Abläufe, die eine einzelne SQL-SELECT-Anweisung umschließen, um die Liste der besessenen Autos nach ihrer Marke und ihrem Wert in absteigender Reihenfolge zurückzugeben.
Beginnen Sie mit der Ausführung der SELECT-Anweisung, die Sie verwenden werden:
SELECT * FROM cars ORDER BY make, value DESC;
Die Datenbank gibt die Liste der Autos aus der Tabelle „cars“ zurück, zuerst sortiert nach Marke und dann, innerhalb einer Marke, nach Wert in absteigender Reihenfolge:
Ausgabe
+---------+---------------+------+-----------+
| make | model | year | value |
+---------+---------------+------+-----------+
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso | 2016 | 268000.00 |
| Ferrari | 488 GTB | 2015 | 254750.00 |
| Porsche | 911 GT3 | 2020 | 169700.00 |
| Porsche | Cayman GT4 | 2018 | 118000.00 |
| Porsche | Panamera | 2022 | 113200.00 |
| Porsche | 718 Boxster | 2017 | 48880.00 |
| Porsche | Macan | 2019 | 27400.00 |
+---------+---------------+------+-----------+
10 rows in set (0.00 sec)
Der wertvollste Ferrari steht am Anfang der Liste, und der am wenigsten wertvolle Porsche erscheint unten.
Angenommen, diese Abfrage wird häufig in mehreren Anwendungen oder von mehreren Benutzern verwendet, und Sie möchten sicherstellen, dass alle dieselbe Methode zur Sortierung der Ergebnisse verwenden. Erstellen Sie daher einen gespeicherten Ablauf in MySQL, der diese Anweisung unter einer widerverwendbaren, benannten Prozedur speichert.
Um diesen Ablauf zu erstellen, führen Sie das folgende Codefragment aus:
DELIMITER //
CREATE PROCEDURE get_all_cars()
BEGIN
SELECT * FROM cars ORDER BY make, value DESC;
END //
DELIMITER ;
Wie im vorherigen Abschnitt beschrieben, weisen die ersten und letzten Befehle (DELIMITER // und DELIMITER 😉 MySQL an, das Semikolonzeichen für die Dauer der Prozedurerstellung nicht als Anweisungsende zu behandeln.
Der Namen des Ablaufs „get_all_cars“, folgt dem SQL-Befehl CREATE PROCEDURE, um den Ablauf sinnvoll zu beschreiben. Nach dem Namen befindet sich ein Paar runder Klammern (), in dem Sie Parameter hinzufügen können. In diesem Beispiel verwendet der Ablauf keine Parameter, daher sind die Klammern leer. Dann, zwischen den Anweisungen BEGIN und END, die den Anfang und das Ende des Blocks definieren, wird die zuvor verwendete SELECT-Anweisung unverändert eingefügt.
Hinweis: Abhängig von den Berechtigungen Ihres MySQL-Benutzers erhalten Sie möglicherweise einen Fehler, wenn Sie den Befehl CREATE PROCEDURE ausführen: ERROR 1044 (42000): Access denied for user ’sammy’@’localhost‘ to database ‚procedures‘. Um Berechtigungen zum Erstellen und Ausführen von gespeicherten Abläufen für Ihren Benutzer zu erteilen, melden Sie sich als root bei MySQL an und führen Sie die folgenden Befehle aus, wobei Sie den MySQL-Benutzernamen und den Host nach Bedarf anpassen:
GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;
Nachdem Sie die Benutzerberechtigungen aktualisiert haben, melden Sie sich als root ab, melden Sie sich als Benutzer erneut an und führen Sie die CREATE PROCEDURE-Anweisung erneut aus.
Die Datenbank bestätigt die erfolgreiche Erstellung mit einer Erfolgsmeldung:
Ausgabe
Query OK, 0 rows affected (0.02 sec)
Der Ablauf „get_all_cars“ ist jetzt in der Datenbank gespeichert und wird beim Aufruf die gespeicherte Anweisung unverändert ausführen.
Um gespeicherte Abläufe in MySQL auszuführen, verwenden Sie den SQL-Befehl CALL gefolgt vom Namen des Ablaufs. Versuchen Sie, den neu erstellten Ablauf wie folgt auszuführen:
CALL get_all_cars;
Der Name „get_all_cars“ reicht aus, um den Ablauf zu verwenden. Sie müssen keinen Teil der zuvor verwendeten SELECT-Anweisung manuell eingeben. Die Datenbank zeigt die Ergebnisse wie die Ausgabe der zuvor ausgeführten SELECT-Anweisung an:
Ausgabe
+---------+---------------+------+-----------+
| make | model | year | value |
+---------+---------------+------+-----------+
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso | 2016 | 268000.00 |
| Ferrari | 488 GTB | 2015 | 254750.00 |
| Porsche | 911 GT3 | 2020 | 169700.00 |
| Porsche | Cayman GT4 | 2018 | 118000.00 |
| Porsche | Panamera | 2022 | 113200.00 |
| Porsche | 718 Boxster | 2017 | 48880.00 |
| Porsche | Macan | 2019 | 27400.00 |
+---------+---------------+------+-----------+
10 rows in set (0.00 sec)
Sie haben jetzt erfolgreich eine gespeicherten Ablauf ohne Parameter erstellt, die alle Autos aus der Tabelle „cars“ in einer bestimmten Reihenfolge zurückgibt.
Erstellen eines gespeicherten Ablaufs in MySQL mit einem Eingabeparameter
In diesem Abschnitt fügen Sie Eingabeparameter zur Definition der Abläufe hinzu, damit Benutzer beim Ausführen Daten übergeben können. Beispielsweise könnten Benutzer Abfragefilter bereitstellen.
Der zuvor erstellte Ablauf „get_all_cars“ rief alle Autos aus der Tabelle „cars“ zu jeder Zeit ab. Erstellen wir einen weiteren Ablauf, um Autos aus einem bestimmten Produktionsjahr zu finden. Dazu definieren Sie einen benannten Parameter in der Ablaufdefinition.
Führen Sie den folgenden Code aus:
DELIMITER //
CREATE PROCEDURE get_cars_by_year(
IN year_filter int
)
BEGIN
SELECT * FROM cars WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;
Es gibt mehrere Änderungen am Prozedur-Erstellungscode aus dem vorherigen Abschnitt.
Der Name ist „get_cars_by_year“, der den Ablauf beschreibt: Autos basierend auf ihrem Produktionsjahr abrufen.
Die zuvor leeren Klammern enthalten jetzt eine einzelne Parameterdefinition: IN year_filter int. Das Schlüsselwort IN teilt der Datenbank mit, dass der Parameter vom aufrufenden Benutzer in den Ablauf übergeben wird. „year_filter“ ist ein beliebiger Name für den Parameter, auf den Sie sich im Ablaufcode beziehen werden. Schließlich ist int der Datentyp. In diesem Fall wird das Produktionsjahr als numerischer Wert ausgedrückt.
Der „year_filter“-Parameter, der nach dem Ablaufnamen definiert ist, erscheint in der SELECT-Anweisung in der WHERE-Klausel year = year_filter, um die Tabelle „cars“ anhand ihres Produktionsjahres zu filtern.
Die Datenbank gibt erneut eine Erfolgsmeldung aus:
Ausgabe
Query OK, 0 rows affected (0.02 sec)
Versuchen Sie, die Prozedur ohne Übergabe von Parametern auszuführen, wie Sie es zuvor getan haben:
CALL get_cars_by_year;
Die MySQL-Datenbank gibt eine Fehlermeldung aus:
Fehlermeldung
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE procedures.get_cars_by_year; expected 1, got 0
Diesmal erwartet der gespeicherte Ablauf einen Parameter, aber niemand hat einen übergeben. Um eine gespeicherten Ablauf mit Parametern aufzurufen, können Sie die Parameterwerte in Klammern in der Reihenfolge angeben, in der sie vom Ablauf erwartet werden. Um Autos aus 2017abzurufen, führen Sie Folgendes aus:
CALL get_cars_by_year(2017);
Nun führt der Ablauf die Anweisungen korrekt aus und gibt die Liste der Autos aus diesem Jahr zurück:
Ausgabe
+---------+---------------+------+-----------+
| make | model | year | value |
+---------+---------------+------+-----------+
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Porsche | 718 Boxster | 2017 | 48880.00 |
+---------+---------------+------+-----------+
2 rows in set (0.00 sec)
In diesem Beispiel haben Sie gelernt, wie Sie Eingabeparameter an gespeicherte Abläufe in MySQL übergeben und sie in Abfragen verwenden, um Filteroptionen bereitzustellen.
Im nächsten Abschnitt werden Sie Ausgabeparameter verwenden, um Abläufe zu erstellen, die mehrere verschiedene Werte in einem Durchlauf zurückgeben.
Erstellen eines gespeicherten Ablaufs in MySQL mit Eingabe- und Ausgabeparametern
In den beiden vorherigen Beispielen riefen die von Ihnen erstellten Abläufe eine SELECT-Anweisung auf, um einen Ergebnissatz zu erhalten. In einigen Fällen benötigen Sie jedoch möglicherweise einen Ablauf, der mehrere unterschiedliche Werte zusammen zurückgibt, anstatt eines einzelnen Ergebnisses für eine individuelle Abfrage.
Angenommen, Sie möchten einen Ablauf erstellen, der Zusammenfassungsinformationen über Autos aus einem bestimmten Jahr bereitstellt, einschließlich der Anzahl der Autos in der Sammlung und deren Marktwert (Minimalwert, Maximalwert und Durchschnittswert).
Um dies zu erreichen, können Sie OUT-Parameter verwenden, wenn Sie einen neue Ablauf erstellen. Ähnlich wie IN-Parameter haben OUT-Parameter Namen und zugehörige Datentypen. Anstatt Daten an den Ablauf zu übergeben, füllt der Ablauf sie jedoch, um Werte an den aufrufenden Benutzer zurückzugeben.
Erstellen Sie eine get_car_stats_by_year-Prozedur, die Zusammenfassungsdaten über die Autos eines bestimmten Produktionsjahres mithilfe von Ausgabeparametern zurückgibt:
DELIMITER //
CREATE PROCEDURE get_car_stats_by_year(
IN year_filter int,
OUT cars_number int,
OUT min_value decimal(10, 2),
OUT avg_value decimal(10, 2),
OUT max_value decimal(10, 2)
)
BEGIN
SELECT COUNT(*), MIN(value), AVG(value), MAX(value)
INTO cars_number, min_value, avg_value, max_value
FROM cars
WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;
Diesmal sind neben dem Eingabeparameter
year_filter, der verwendet wird, um Autos nach dem Produktionsjahr zu filtern, vier OUT-Parameter innerhalb des Klammerblocks definiert. Der Parameter cars_number wird mit dem Datentyp int dargestellt und wird verwendet, um die Anzahl der Autos in der Sammlung zurückzugeben. Die Parameter min_value, avg_value und max_value repräsentieren den Marktwert und sind mit dem Typ decimal(10, 2) definiert (ähnlich wie die Spalte value in der Tabelle cars). Diese Parameter geben Informationen über das günstigste und das teuerste Auto in der Sammlung sowie den Durchschnittspreis aller passenden Autos zurück.
Die SELECT-Anweisung ruft vier Werte aus der Tabelle cars ab, indem sie SQL-Mathematikfunktionen verwendet: COUNT, um die Gesamtanzahl der Autos zu ermitteln, sowie MIN, AVG und MAX, um den Minimal-, Durchschnitts- und Maximalwert aus der Spalte value abzurufen.
Hinweis: Weitere Informationen zur Verwendung von mathematischen Funktionen in SQL finden Sie im Leitfaden „How To Use Mathematical Expressions and Aggregate Functions in SQL“.
Um der Datenbank mitzuteilen, dass die Ergebnisse dieser Abfrage in den Ausgabeparametern der Stored Procedure gespeichert werden sollen, wird ein neues Schlüsselwort INTO eingeführt. Nach dem Schlüsselwort INTO werden die Namen der vier Prozedurparameter aufgeführt, die den abgerufenen Daten entsprechen. Auf diese Weise speichert MySQL den Wert COUNT(*) im Parameter cars_number, das Ergebnis MIN(value) im Parameter min_value und so weiter.
Die Datenbank bestätigt die erfolgreiche Ablauferstellung:
Ausgabe
Query OK, 0 rows affected (0.02 sec)
Führen Sie nun den neuen Ablauf aus, indem Sie Folgendes ausführen:
CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);
Die vier neuen Parameter beginnen mit dem Zeichen
@. Dies sind lokale Variablennamen in der MySQL-Konsole, die Sie verwenden können, um Daten vorübergehend zu speichern. Wenn Sie diese an den gespeicherten Ablauf übergeben, den Sie gerade erstellt haben, fügt der Ablauf die Werte in diese Variablen ein.
Die Datenbank gibt folgende Ausgabe zurück:
Ausgabe
Query OK, 1 row affected (0.00 sec)
Dies unterscheidet sich vom vorherigen Verhalten, bei dem der Bildschirm die Ergebnisse sofort anzeigte. Dies liegt daran, dass die Ergebnisse des Ablaufs in Ausgabeparametern gespeichert und nicht als Abfrageergebnis zurückgegeben wurden. Um auf die Ergebnisse zuzugreifen, können Sie diese direkt in der MySQL-Shell wie folgt abrufen:
SELECT @number, @min, @avg, @max;
Mit dieser Abfrage wählen Sie Werte aus den lokalen Variablen und rufen nicht den Ablauf erneut auf. Der gespeicherte Ablauf hat die Ergebnisse in diesen Variablen gespeichert, und die Daten bleiben verfügbar, bis Sie sich von der Shell abmelden.
Hinweis: Weitere Informationen zur Verwendung benutzerdefinierter Variablen in MySQL finden Sie im Abschnitt „User-Defined Variables“ in der Dokumentation. Bei der Anwendungsentwicklung unterscheiden sich die Methoden zum Zugriff auf Daten, je nach Programmiersprache und Framework. Im Zweifelsfall konsultieren Sie die Dokumentation Ihrer bevorzugten Sprache und Ihres bevorzugten Frameworks.
Die Ausgabe zeigt die Werte für die abgefragten Variablen:
Ausgabe
+---------+----------+-----------+-----------+
| @number | @min | @avg | @max |
+---------+----------+-----------+-----------+
| 2 | 48880.00 | 192090.00 | 335300.00 |
+---------+----------+-----------+-----------+
1 row in set (0.00 sec)
Die Werte entsprechen der Anzahl der Autos aus 2017, sowie dem minimalen, durchschnittlichen und maximalen Marktwert der Autos aus diesem Produktionsjahr.
In diesem Beispiel haben Sie gelernt, wie Sie Ausgabeparameter verwenden, um mehrere verschiedene Werte innerhalb des Ablaufs zur späteren Verwendung zurückzugeben. Im nächsten Abschnitt erfahren Sie, wie Sie erstellten Abläufe entfernen.
Entfernen von Stored Procedures
In diesem Abschnitt erfahren Sie, wie Sie die in der Datenbank vorhandenen Abläufe entfernen.
Manchmal benötigt man den gespeicherte Ablauf möglicherweise nicht mehr. In anderen Fällen möchten Sie möglicherweise die Funktionsweise ändern. MySQL erlaubt es nicht, die Definition des Ablaufs nach der Erstellung zu ändern. Daher besteht die einzige Möglichkeit darin, den Ablauf zuerst zu entfernen und dann mit den gewünschten Änderungen neu zu erstellen.
Entfernen wir den letzten Ablauf, „get_car_stats_by_year“. Verwenden Sie dazu die DROP PROCEDURE-Anweisung:
DROP PROCEDURE get_car_stats_by_year;
Die Datenbank bestätigt die erfolgreiche Löschung mit einer Erfolgsmeldung:
Ausgabe
Query OK, 0 rows affected (0.02 sec)
CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);
Diesmal zeigt es eine Fehlermeldung an, die besagt, dass der Ablauf in der Datenbank nicht vorhanden ist:
Fehlermeldung
ERROR 1305 (42000): PROCEDURE procedures.get_car_stats_by_year does not exist
In diesem Abschnitt haben Sie gelernt, wie Sie vorhandene, gespeicherte Abläufe in MySQL in der Datenbank löschen.
Fazit
Durch die Befolgung dieses Leitfadens haben Sie gelernt, was gespeicherte Abläufe sind und wie Sie diese in MySQL verwenden. Sie haben Abläufe ohne Parameter sowie Abläufe erstellt, die Eingabe- und Ausgabeparameter verwenden, um sie flexibler zu gestalten.
Sie können gespeicherte Abläufe in MySQL verwenden, um widerverwendbare Routinen zu erstellen und Methoden für den Datenzugriff zu vereinheitlichen. Des Weiteren können Sie komplexe Verhaltensweisen implementieren, die über die Möglichkeiten einzelner SQL-Abfragen hinausgehen. Dieses Tutorial behandelte nur die Grundlagen der Verwendung.
