Wie man gespeicherte Prozeduren in MySQL verwendet

Einführung

Bei der Arbeit mit relationalen Datenbanken werden üblicherweise SQL-Anweisungen (Structured Query Language) verwendet, um Daten direkt aus dem Anwendungscode abzurufen oder zu bearbeiten, beispielsweise SELECT, INSERT, UPDATE oder DELETE. Diese Anweisungen greifen direkt auf die zugrunde liegenden Datenbanktabellen zu und verändern diese. Werden dieselben Anweisungen oder Anweisungsgruppen in mehreren Anwendungen verwendet, die auf dieselbe Datenbank zugreifen, werden sie häufig in den einzelnen Anwendungen wiederholt.

MySQL unterstützt, wie viele andere relationale Datenbankmanagementsysteme, die Verwendung von gespeicherten Prozeduren. Gespeicherte Prozeduren helfen dabei, eine oder mehrere SQL-Anweisungen zur Wiederverwendung unter einem gemeinsamen Namen zu gruppieren und die allgemeine Geschäftslogik in der Datenbank selbst zu kapseln. Eine solche Prozedur kann von einer Anwendung aufgerufen werden, die auf die Datenbank zugreift, um Daten konsistent abzurufen oder zu bearbeiten.

Mithilfe von gespeicherten Prozeduren können Sie wiederverwendbare Routinen für häufige Aufgaben erstellen, die in mehreren Anwendungen eingesetzt werden können, Daten validieren oder eine zusätzliche Ebene der Datenzugriffssicherheit bereitstellen, indem Sie Datenbankbenutzer daran hindern, direkt auf zugrunde liegende Tabellen zuzugreifen und beliebige Abfragen auszuführen.

In diesem Tutorial lernen Sie, was gespeicherte Prozeduren sind und wie man einfache gespeicherte Prozeduren erstellt, die Daten zurückgeben und Eingabe- und Ausgabeparameter verwenden.

Voraussetzungen

Um dieser Anleitung folgen zu können, benötigen Sie einen Computer mit einem SQL-basierten relationalen Datenbankmanagementsystem (RDBMS). Die Anweisungen und Beispiele in dieser Anleitung wurden in der folgenden Umgebung validiert:

• Ein Server mit Ubuntu 20.04, einem Benutzer ohne Root-Rechte, aber mit Administratorrechten, und einer mit UFW konfigurierten Firewall.
• MySQL ist auf dem Server installiert und gesichert.
• Grundlegende Einführung in die Ausführung von SELECT-Abfragen zum Abrufen von Daten aus der Datenbank

Hinweis: Viele relationale Datenbankmanagementsysteme (RDBMS) verwenden ihre eigene SQL-Implementierung, und die Syntax für gespeicherte Prozeduren ist nicht Teil des offiziellen SQL-Standards. Obwohl die in diesem Tutorial erwähnten Befehle auch in anderen RDBMS funktionieren können, sind gespeicherte Prozeduren datenbankspezifisch. Daher können Syntax und Ausgabe abweichen, wenn Sie sie auf einem anderen System als MySQL testen.

Sie benötigen außerdem eine leere Datenbank, in der Sie mithilfe von gespeicherten Prozeduren Tabellen erstellen können. Wir empfehlen Ihnen, die folgenden Abschnitte zu lesen, um detaillierte Informationen zum Verbinden mit einem MySQL-Server und zum Erstellen der in den Beispielen dieses Leitfadens verwendeten Testdatenbank zu erhalten.

Verbindung zu MySQL herstellen und eine Beispieldatenbank einrichten

In diesem Abschnitt stellen Sie eine Verbindung zum MySQL-Server her und erstellen eine Beispieldatenbank, damit Sie die Beispiele in diesem Leitfaden verwenden können.

In dieser Anleitung verwenden Sie eine fiktive Autosammlung. Sie speichern Details zu den Autos, die Sie aktuell besitzen, einschließlich Marke, Modell, Baujahr und Wert.

Wenn Ihr SQL-Datenbanksystem auf einem Remote-Server läuft, stellen Sie eine SSH-Verbindung von Ihrem lokalen Rechner zu Ihrem Server her:

ssh sammy@your_server_ip

Öffnen Sie anschließend die MySQL-Server-Eingabeaufforderung und ersetzen Sie „Sami“ durch Ihren MySQL-Benutzernamen:

mysql -u sammy -p

Erstellen Sie eine Datenbank namens Prozeduren:

CREATE DATABASE procedures;

Wenn die Datenbank erfolgreich erstellt wurde, erhalten Sie folgende Ausgabe:

Output
Query OK, 1 row affected (0.01 sec)

Um die Prozedurendatenbank auszuwählen, führen Sie die folgende USE-Anweisung aus:

USE procedures;

Sie erhalten folgende Ausgabe:

Output
Database changed

Sobald Sie eine Datenbank ausgewählt haben, können Sie darin Instanztabellen erstellen. Die Tabelle „Maschinen“ enthält vereinfachte Daten über die Maschinen in der Datenbank. Sie enthält die folgenden Spalten:

• Marke: Diese Spalte repräsentiert jedes Auto, das dem Unternehmen gehört, ausgedrückt durch den Datentyp varchar mit maximal 100 Zeichen.
• Modell: Diese Spalte enthält den Namen des Automodells, ausgedrückt im Datentyp varchar mit maximal 100 Zeichen.
• Jahr: Diese Spalte speichert das Herstellungsjahr des Fahrzeugs mit einem int-Datentyp zur Aufnahme numerischer Werte.
• Wert: In dieser Spalte wird der Wert des Autos im Dezimaldatentyp mit maximal 10 Ziffern und 2 Nachkommastellen gespeichert.

Erstellen Sie die Beispieltabelle mit folgendem Befehl:

CREATE TABLE cars (
make varchar(100),
model varchar(100),
year int,
value decimal(10, 2)
);

Wenn die folgende Ausgabe gedruckt wird, wurde die Tabelle erstellt:

Output
Query OK, 0 rows affected (0.00 sec)

Laden Sie anschließend die Tabelle „Cars“ mit Beispieldaten, 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 Operation INSERT INTO fügt der Tabelle zehn Beispiel-Sportwagen hinzu, fünf Porsche-Modelle und fünf Ferrari-Modelle. Die folgende Ausgabe zeigt, dass alle fünf Zeilen hinzugefügt wurden:

Output
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0

Damit sind Sie bereit, dem Rest der Anleitung zu folgen und mit der Verwendung von gespeicherten Prozeduren in SQL zu beginnen.

Einführung in gespeicherte Prozeduren

Gespeicherte Prozeduren in MySQL und vielen anderen relationalen Datenbanksystemen werden als Objekte bezeichnet. Sie enthalten eine oder mehrere Anweisungen, die beim Aufruf durch die Datenbank nacheinander ausgeführt werden. Im einfachsten Fall kann eine gespeicherte Prozedur einen häufig verwendeten Ausdruck in einer wiederverwendbaren Routine speichern, beispielsweise zum Abrufen von Daten aus einer Datenbank mithilfe häufig verwendeter Filter. So lässt sich beispielsweise eine gespeicherte Prozedur erstellen, um die Kunden eines Online-Shops abzurufen, die in den letzten Monaten bestellt haben. In komplexen Szenarien können gespeicherte Prozeduren umfangreiche Programme repräsentieren, die komplexe Geschäftslogik für leistungsstarke Anwendungen beschreiben.

Die Anweisungen in einer gespeicherten Prozedur können gängige SQL-Anweisungen wie SELECT- oder INSERT-Abfragen enthalten, die Daten zurückgeben oder bearbeiten. Darüber hinaus können gespeicherte Prozeduren Folgendes verwenden:

• Parameter werden an die gespeicherte Prozedur übergeben oder von dieser zurückgegeben.
• Variablen, die zur direkten Verarbeitung abgerufener Daten im Prozedurcode deklariert werden.
• Bedingte Anweisungen ermöglichen die Ausführung von Abschnitten des gespeicherten Prozedurcodes in Abhängigkeit von bestimmten Bedingungen, wie z. B. IF- oder CASE-Anweisungen.
• Schleifen wie WHILE, LOOP und REPEAT ermöglichen es, Codeabschnitte mehrfach auszuführen, beispielsweise für jede Zeile in einem abgerufenen Datensatz.
• Anweisungen zur Fehlerbehandlung, wie z. B. die Rückgabe von Fehlermeldungen an Datenbankbenutzer, die Zugriff auf die Prozedur haben.
• Aufruf anderer gespeicherter Prozeduren in der Datenbank.

Hinweis: Die umfangreiche Syntax von MySQL ermöglicht das Schreiben leistungsstarker Programme und die Lösung komplexer Probleme mithilfe von gespeicherten Prozeduren. Diese Anleitung behandelt lediglich die grundlegende Verwendung gespeicherter Prozeduren mit SQL-Anweisungen im Prozedurrumpf sowie Eingabe- und Ausgabeparametern. Bedingte Codeausführung, die Verwendung von Variablen, Schleifen und benutzerdefinierte Fehlerbehandlung werden in dieser Anleitung nicht behandelt. Wir empfehlen Ihnen, sich in der offiziellen MySQL-Dokumentation ausführlicher über gespeicherte Prozeduren zu informieren.

Wird die Prozedur über ihren Namen aufgerufen, führt die Datenbank-Engine sie wie definiert, Anweisung für Anweisung aus.

Der Datenbankbenutzer muss über die entsprechenden Berechtigungen verfügen, um die jeweilige Prozedur auszuführen. Diese Berechtigungsanforderung stellt eine zusätzliche Sicherheitsebene dar, die den direkten Zugriff auf die Datenbank verhindert, gleichzeitig aber den Benutzern den Zugriff auf einzelne, sicher ausführbare Prozeduren ermöglicht.

Gespeicherte Prozeduren werden direkt auf dem Datenbankserver ausgeführt, führen alle Berechnungen lokal durch und geben die Ergebnisse erst nach Abschluss an den aufrufenden Benutzer zurück.

Wenn Sie das Verhalten einer Prozedur ändern möchten, können Sie die Prozedur in der Datenbank aktualisieren. Anwendungen, die diese Prozedur verwenden, erhalten dann automatisch die neue Version. Alle Benutzer können den neuen Prozedurcode sofort nutzen, ohne ihre Anwendungen anpassen zu müssen.

Hier ist die allgemeine Struktur des SQL-Codes, der zum Erstellen einer gespeicherten Prozedur verwendet wird:

DELIMITER //
CREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n)
BEGIN
instruction_1;
instruction_2;
. . .
instruction_n;
END //
DELIMITER ;

Die erste und letzte Anweisung in diesem Code-Snippet lauten `DELIMITER //` und `DELIMITER ;`. MySQL verwendet üblicherweise das Semikolon (;), um Anweisungen zu trennen und deren Anfang und Ende zu kennzeichnen. Werden mehrere Befehle in der MySQL-Konsole durch Semikolons getrennt ausgeführt, werden sie als separate Befehle behandelt und nacheinander unabhängig voneinander ausgeführt. Eine gespeicherte Prozedur kann jedoch mehrere Befehle enthalten, die beim Aufruf sequenziell ausgeführt werden. Dies führt zu einem Problem, wenn Sie MySQL anweisen möchten, eine neue Prozedur zu erstellen. Die Datenbank-Engine erkennt das Semikolon im Rumpf der gespeicherten Prozedur und geht fälschlicherweise davon aus, dass die Ausführung des Befehls beendet werden soll. In diesem Fall handelt es sich bei dem betreffenden Befehl um den gesamten Code zur Prozedurerstellung und nicht um eine einzelne Anweisung innerhalb der Prozedur selbst. Daher interpretiert MySQL Ihre Absicht falsch.

Um diese Einschränkung zu umgehen, verwenden Sie die DELIMITER-Anweisung, um das Trennzeichen für die Dauer des CREATE PROCEDURE-Aufrufs vorübergehend von ; auf // zu ändern. Anschließend werden alle Semikolons im Rumpf der gespeicherten Prozedur unverändert an den Server gesendet. Nach Abschluss der gesamten Prozedur wird das Trennzeichen mit dem letzten DELIMITER ; wieder auf ; geändert.

Der Kern des Codes zum Erstellen einer neuen Prozedur ist der Aufruf `CREATE PROCEDURE`, gefolgt vom Prozedurnamen: `procedure_name` im Beispiel. Auf den Prozedurnamen folgt optional eine Liste der Parameter, die die Prozedur akzeptiert. Der letzte Teil ist der Prozedurrumpf, der von `BEGIN`- und `END`-Anweisungen umschlossen wird. Innerhalb des Prozedurcodes befindet sich der Code, der eine SQL-Anweisung, wie beispielsweise eine `SELECT`-Abfrage, oder komplexeren Code enthalten kann.

Der END-Befehl endet mit //, einem temporären Trennzeichen, anstelle eines regulären Semikolons.

Im nächsten Abschnitt erstellen Sie eine einfache parameterlose gespeicherte Prozedur, die eine Abfrage kapselt.

Erstellen einer gespeicherten Prozedur ohne Parameter

In diesem Abschnitt erstellen Sie Ihre erste gespeicherte Prozedur, die eine SQL-SELECT-Anweisung enthält, um eine Liste der Fahrzeuge, die dem Benutzer gehören, nach Marke und Wert in absteigender Reihenfolge zurückzugeben.

Beginnen Sie mit der Ausführung der SELECT-Anweisung, die Sie verwenden möchten:

SELECT * FROM cars ORDER BY make, value DESC;

Die Datenbank gibt eine Liste von Autos aus der Tabelle „Cars“ zurück, zuerst nach Marke und dann innerhalb einer Marke nach Wert in absteigender Reihenfolge:

Output
+---------+---------------+------+-----------+
| 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 an der Spitze der Liste, der wertloseste Porsche am Ende.

Angenommen, diese Abfrage wird wiederholt in mehreren Anwendungen oder von mehreren Benutzern verwendet, und Sie möchten sicherstellen, dass alle dieselbe Methode zum Sortieren der Ergebnisse verwenden. Dazu möchten Sie eine gespeicherte Prozedur erstellen, die diesen Ausdruck unter einer wiederverwendbaren benannten Prozedur speichert.

Um diese gespeicherte Prozedur zu erstellen, führen Sie den folgenden Codeausschnitt aus:

DELIMITER //
CREATE PROCEDURE get_all_cars()
BEGIN
SELECT * FROM cars ORDER BY make, value DESC;
END //
DELIMITER ;

Wie im vorherigen Abschnitt erläutert, weisen die erste und letzte Anweisung (DELIMITER // und DELIMITER 😉) MySQL an, das Semikolon als Anweisungstrennzeichen während der gesamten Prozedurerstellung zu ignorieren.

Auf die SQL-Anweisung CREATE PROCEDURE folgt der Prozedurname get_all_cars, den Sie so definieren können, dass er die Funktion der Prozedur bestmöglich beschreibt. Nach dem Prozedurnamen steht ein Klammerpaar (), in dem Sie Parameter hinzufügen können. In diesem Beispiel verwendet die Prozedur keine Parameter, daher sind die Klammern leer. Zwischen den Anweisungen BEGIN und END, die den Anfang und das Ende des Prozedurcodeblocks definieren, wird die zuvor verwendete SELECT-Anweisung unverändert wiedergegeben.

Die Datenbank wird mit einer Erfolgsmeldung antworten:

Output
Query OK, 0 rows affected (0.02 sec)

Die Prozedur get_all_cars ist nun in der Datenbank gespeichert und führt beim Aufruf die gespeicherte Anweisung unverändert aus.

Um eine gespeicherte Prozedur auszuführen, verwenden Sie den SQL-Befehl CALL gefolgt vom Prozedurnamen. Versuchen Sie, die neu erstellte Prozedur wie folgt auszuführen:

CALL get_all_cars;

Der Prozedurname „get_all_cars“ genügt, um die Prozedur zu verwenden. Sie müssen keinen Teil der zuvor verwendeten SELECT-Anweisung mehr manuell eingeben. Die Datenbank verarbeitet die Ergebnisse genau so, wie sie die SELECT-Anweisung ausgibt.

Output
+---------+---------------+------+-----------+
| 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)
Query OK, 0 rows affected (0.00 sec)

Sie haben nun erfolgreich eine gespeicherte Prozedur ohne Parameter erstellt, die alle Autos aus der Tabelle „Cars“ in einer bestimmten Reihenfolge zurückgibt. Sie können diese Methode in verschiedenen Anwendungen verwenden.

Im nächsten Abschnitt erstellen Sie eine Prozedur, die Parameter akzeptiert, um das Verhalten der Prozedur in Abhängigkeit von der Benutzereingabe zu ändern.

Erstellen einer gespeicherten Prozedur mit Eingabeparametern

In diesem Abschnitt fügen Sie der Definition der gespeicherten Prozedur Eingabeparameter hinzu, damit Benutzer, die die Prozedur ausführen, Daten an sie übergeben können. Beispielsweise können Benutzer Abfragefilter angeben.

Die bisherige gespeicherte Prozedur `get_all_cars` ruft immer alle Autos aus der Tabelle `cars` ab. Wir erstellen nun eine weitere Prozedur, die Autos eines bestimmten Baujahrs findet. Dazu definieren wir in der Prozedurdefinition einen Parameter namens `get_all_cars`.

Führen Sie 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 ;

Im Vergleich zum vorherigen Abschnitt gibt es einige Änderungen am Code zur Prozedurerstellung.

Zunächst einmal lautet der Name get_cars_by_year, was die Methode beschreibt: Autos anhand ihres Herstellungsjahres abrufen.

Die vorherigen leeren Klammern enthalten nun eine Parameterdefinition: `IN year_filter int`. Das Schlüsselwort `IN` teilt der Datenbank mit, dass der Parameter vom aufrufenden Benutzer an die Prozedur übergeben wird. `year_filter` ist ein beliebiger Name für den Parameter. Sie verwenden ihn, um im Prozedurcode auf den Parameter zu verweisen. `int` ist der Datentyp. In diesem Fall wird das Herstellungsjahr als numerischer Wert angegeben.

Der Parameter year_filter, der nach dem Prozedurnamen definiert ist, erscheint in der SELECT-Anweisung in der WHERE year = year_filter-Klausel und filtert die Tabelle der Autos nach ihrem Herstellungsjahr.

Die Datenbank wird erneut mit einer Erfolgsmeldung antworten:

Output
Query OK, 0 rows affected (0.02 sec)

Versuchen Sie, die Prozedur wie zuvor ohne Parameter auszuführen:

CALL get_cars_by_year;

Die MySQL-Datenbank gibt eine Fehlermeldung zurück:

Error message
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE procedures.get_cars_by_year; expected 1, got 0

Dieses Mal erwartet die gespeicherte Prozedur einen Parameter, es wurde jedoch keiner angegeben. Um eine gespeicherte Prozedur mit Parametern aufzurufen, können Sie die Parameterwerte in Klammern in der von der Prozedur erwarteten Reihenfolge angeben. Um im Jahr 2017 hergestellte Fahrzeuge abzurufen, führen Sie Folgendes aus:

CALL get_cars_by_year(2017);

Die aufgerufene Prozedur wird nun korrekt ausgeführt und gibt die Liste der Autos für dieses Jahr zurück:

Output
+---------+---------------+------+-----------+
| make | model | year | value |
+---------+---------------+------+-----------+
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Porsche | 718 Boxster | 2017 | 48880.00 |
+---------+---------------+------+-----------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

In diesem Beispiel haben Sie gelernt, wie man Eingabeparameter an gespeicherte Prozeduren übergibt und diese in Abfragen innerhalb einer Prozedur verwendet, um Filteroptionen bereitzustellen.

Im nächsten Abschnitt verwenden Sie Ausgabeparameter, um Prozeduren zu erstellen, die bei einer einzigen Ausführung mehrere unterschiedliche Werte zurückgeben.

Erstellen einer gespeicherten Prozedur mit Eingabe- und Ausgabeparametern

In den beiden vorherigen Beispielen riefen die erstellten gespeicherten Prozeduren die SELECT-Anweisung auf, um ein Ergebnis-Set zurückzugeben. In manchen Fällen benötigen Sie jedoch möglicherweise eine gespeicherte Prozedur, die mehrere verschiedene Werte zusammen anstelle eines einzelnen Ergebnis-Sets für eine Abfrage zurückgibt.

Angenommen, Sie möchten eine Prozedur erstellen, die zusammenfassende Informationen über Autos eines bestimmten Jahres liefert, einschließlich der Anzahl der Autos in der Sammlung und ihres Marktwerts (Minimum, Maximum und Durchschnitt).

Hierfür können Sie beim Erstellen einer neuen gespeicherten Prozedur OUT-Parameter verwenden. Ähnlich wie IN-Parameter besitzen auch OUT-Parameter einen Namen und einen Datentyp. Anstatt jedoch Daten an die gespeicherte Prozedur zu übergeben, können sie mit Daten aus der gespeicherten Prozedur befüllt werden, um Werte an den aufrufenden Benutzer zurückzugeben.

Erstellen Sie eine Prozedur get_car_stats_by_year, die zusammenfassende Daten für Autos aus einem bestimmten Produktionsjahr unter Verwendung 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 ;

Dieses Mal gibt es neben dem IN-Parameter `year_filter`, der zum Filtern der Fahrzeuge nach Baujahr dient, vier OUT-Parameter im Klammerblock. Der Parameter `cars_number` ist vom Datentyp `int` und gibt die Anzahl der Fahrzeuge in der Menge zurück. Die Parameter `min_value`, `avg_value` und `max_value` repräsentieren den Marktwert und sind vom Datentyp `decimal(10, 2)` (ähnlich der Spalte `value` in der Tabelle `cars`). Sie liefern Informationen über das günstigste und das teuerste Fahrzeug in der Menge sowie den Durchschnittspreis aller übereinstimmenden Fahrzeuge.

Die SELECT-Anweisung sucht vier Werte aus der Tabelle cars mithilfe von SQL-Funktionen: COUNT, um die Gesamtzahl der Autos zu erhalten, und MIN, AVG und MAX, um den Minimal-, Durchschnitts- und Maximalwert aus der Spalte value zu erhalten.

Um der Datenbank mitzuteilen, dass die Ergebnisse dieser Abfrage in den Ausgabeparametern der gespeicherten Prozedur gespeichert werden sollen, wurde das neue Schlüsselwort INTO eingeführt. Nach dem Schlüsselwort INTO werden die Namen der vier Prozedurparameter aufgelistet, die sich auf die abgerufenen Daten beziehen. Dadurch speichert MySQL den Wert von COUNT(*) im Parameter cars_number, das Ergebnis von MIN(value) im Parameter min_value usw.

Die Datenbank bestätigt die erfolgreiche Erstellung der Prozedur:

Output
Query OK, 0 rows affected (0.02 sec)

Führen Sie nun die neue Routine aus, indem Sie Folgendes tun:

CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);

Die vier neuen Parameter beginnen mit dem @-Symbol. Es handelt sich dabei um die Namen lokaler Variablen in der MySQL-Konsole, mit denen Sie Daten temporär speichern können. Wenn Sie diese an die erstellte gespeicherte Prozedur übergeben, fügt die Prozedur Werte in diese Variablen ein.

Die Datenbank wird mit Folgendem antworten:

Output
Query OK, 1 row affected (0.00 sec)

Dies unterscheidet sich vom vorherigen Verhalten, bei dem die Ergebnisse sofort auf dem Bildschirm angezeigt wurden. Der Grund dafür ist, dass die Ergebnisse der gespeicherten Prozedur in Ausgabeparametern gespeichert und nicht als Abfrageergebnisse zurückgegeben werden. Um auf die Ergebnisse zuzugreifen, können Sie sie direkt in der MySQL-Shell wie folgt auswählen:

SELECT @number, @min, @avg, @max;

Mit dieser Abfrage wählen Sie Werte aus lokalen Variablen aus, anstatt die Prozedur erneut aufzurufen. Die gespeicherte Prozedur speichert ihre Ergebnisse in diesen Variablen, und die Daten stehen bis zum Beenden der Shell zur Verfügung.

Hinweis: Weitere Informationen zur Verwendung benutzerdefinierter Variablen in MySQL finden Sie im Abschnitt „Benutzerdefinierte Variablen“ der Dokumentation. Bei der Anwendungsentwicklung variiert der Zugriff auf Daten, die von gespeicherten Prozeduren zurückgegeben werden, je nach Programmiersprache und Framework. Im Zweifelsfall konsultieren Sie die Dokumentation Ihrer gewählten Sprache und Ihres Frameworks.

Die Ausgabe zeigt die Werte der Abfragevariablen an:

Output
+---------+----------+-----------+-----------+
| @number | @min | @avg | @max |
+---------+----------+-----------+-----------+
| 2 | 48880.00 | 192090.00 | 335300.00 |
+---------+----------+-----------+-----------+
1 row in set (0.00 sec)

Die Werte entsprechen der Anzahl der im Jahr 2017 produzierten Autos sowie dem minimalen, durchschnittlichen und maximalen Marktwert der Autos aus diesem Produktionsjahr.

In diesem Beispiel haben Sie gelernt, wie Sie Ausgabeparameter verwenden, um innerhalb einer gespeicherten Prozedur mehrere unterschiedliche Werte zur späteren Verwendung zurückzugeben. Im nächsten Abschnitt erfahren Sie, wie Sie erstellte Prozeduren löschen.

Gespeicherte Prozeduren löschen

In diesem Abschnitt löschen Sie die gespeicherten Prozeduren in der Datenbank.

Manchmal wird eine erstellte Prozedur nicht mehr benötigt. In anderen Fällen möchten Sie möglicherweise die Funktionsweise der Prozedur ändern. MySQL erlaubt es nicht, die Definition einer Prozedur nach ihrer Erstellung zu ändern. Daher bleibt Ihnen nur die Möglichkeit, die Prozedur zunächst zu löschen und sie anschließend mit den gewünschten Änderungen neu zu erstellen.

Lassen wir die letzte Prozedur, get_car_stats_by_year, weg. Dazu können Sie die Anweisung DROP PROCEDURE verwenden:

DROP PROCEDURE get_car_stats_by_year;

Die Datenbank bestätigt das erfolgreiche Löschen des Vorgangs mit einer Erfolgsmeldung:

Output
Query OK, 0 rows affected (0.02 sec)

Sie können überprüfen, ob die Prozedur gelöscht wurde, indem Sie versuchen, sie aufzurufen. Führen Sie Folgendes aus:

CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);

Dieses Mal erhalten Sie eine Fehlermeldung, dass die Prozedur in der Datenbank nicht existiert:

Error message
ERROR 1305 (42000): PROCEDURE procedures.get_car_stats_by_year does not exist

In diesem Abschnitt haben Sie gelernt, wie man gespeicherte Prozeduren aus einer Datenbank löscht.

Ergebnis

Mit dieser Anleitung haben Sie gelernt, was gespeicherte Prozeduren sind und wie Sie diese in MySQL verwenden, um wiederverwendbare Anweisungen in benannten Prozeduren zu speichern und später auszuführen. Sie haben gespeicherte Prozeduren ohne Parameter sowie Prozeduren mit Eingabe- und Ausgabeparametern erstellt, um diese flexibler zu gestalten.