So verwenden Sie gespeicherte Prozeduren in MySQL

Wenn Sie mit einer relationalen Datenbank arbeiten, stellen Sie normalerweise einzelne SQL-Abfragen (Structured Query Language) ein, um Daten abzurufen oder zu bearbeiten, z. B. SELECT, INSERT, UPDATE oder DELETE. direkt aus Ihrem Anwendungscode heraus. Diese Aussagen funktionieren und die zugrunde liegenden Datenbanktabellen direkt bearbeiten. Wenn das Gleiche Anweisungen oder Gruppen von Anweisungen werden in mehreren Anwendungen verwendet Da sie auf dieselbe Datenbank zugreifen, werden sie häufig einzeln dupliziert Anwendungen.

MySQL, ähnlich wie viele andere relationale Datenbankverwaltungssysteme, unterstützt die Verwendung gespeicherter Prozeduren. Gespeicherte Prozeduren helfen Gruppe eins oder mehrere SQL-Anweisungen zur Wiederverwendung unter einem gemeinsamen Namen, Kapselung gemeinsame Geschäftslogik innerhalb der Datenbank selbst. Ein solches Verfahren kann von der Anwendung aufgerufen werden, die auf die Datenbank zugreift, um abzurufen oder Bearbeiten Sie Daten auf konsistente Weise.

Mithilfe gespeicherter Prozeduren können Sie wiederverwendbare Routinen für gemeinsame Anwendungen erstellen Aufgaben zur Verwendung in mehreren Anwendungen, Bereitstellung von Datenvalidierung, oder stellen Sie durch Einschränkung eine zusätzliche Ebene der Datenzugriffssicherheit bereit Datenbankbenutzer können nicht direkt auf die zugrunde liegenden Tabellen zugreifen und diese ausgeben beliebige Abfragen.

In diesem Tutorial erfahren Sie, was gespeicherte Prozeduren sind und wie man das macht Erstellen Sie grundlegende gespeicherte Prozeduren, die Daten zurückgeben und sowohl Eingabe als auch verwenden Ausgabeparameter.

Herstellen einer Verbindung zu MySQL und Einrichten einer Beispieldatenbank

In diesem Abschnitt stellen Sie eine Verbindung zu einem MySQL-Server her und erstellen einen Beispieldatenbank, damit Sie den Beispielen in diesem Leitfaden folgen können.

Für diesen Leitfaden verwenden Sie eine imaginäre Autosammlung. Sie werden speichern Details zu aktuell im Besitz befindlichen Autos, mit Marke, Modell, Baujahr, und Wert.

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

ssh sammy@your_server_ip

Öffnen Sie dann den MySQL-Server Eingabeaufforderung und ersetzen Siesammydurch den Namen Ihres MySQL-Benutzers Konto:

mysql -u sammy-p

Erstellen Sie eine Datenbank mit dem NamenProzeduren:

CREATE DATABASEprocedures;

Wenn die Datenbank erfolgreich erstellt wurde , erhalten Sie eine Ausgabe wie diese:

OutputQuery OK, 1 row affected (0.01 sec)

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

USEprocedures;

Sie erhalten die folgende Ausgabe:

OutputDatabase changed

Nach Auswahl der Datenbank können Sie darin Beispieltabellen erstellen. Die Tabelle „Autos“ enthält vereinfachte Daten zu Autos in der Datenbank. Es enthält die folgenden Spalten:

• Marke: Diese Spalte enthält die Marke für jedes eigene Auto, ausgedrückt mit dem Varchar-Datentyp mit maximal 100 Zeichen.
• Modell: Dies Die Spalte enthält den Namen des Automodells, ausgedrückt durch den Datentyp varchar mit maximal 100 Zeichen.
• Jahr: In dieser Spalte wird das Baujahr des Autos mit int gespeichert Datentyp zum Speichern numerischer Werte.
• Wert: Diese Spalte speichert den Wert des Autos unter Verwendung des Datentyps „Dezimal“ mit maximal 10 Ziffern und 2 Ziffern nach dem Komma.

Erstellen die Beispieltabelle mit dem folgenden Befehl:

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

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

OutputQuery OK, 0 rows affected (0.00 sec)

Laden Sie anschließend die Tabelle „cars“ mit einigen Beispieldaten, indem Sie die folgende INSERT INTO-Operation ausführen:

INSERT INTO carsVALUES('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 HINEIN Der Betrieb wird der Tabelle zehn Mustersportwagen hinzufügen, mit fünf Porsche und fünf Ferrari-Modelle. Die folgende Ausgabe zeigt, dass alle Es wurden fünf Zeilen hinzugefügt:

OutputQuery 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 gespeicherter Prozeduren in SQL zu beginnen.

Einführung in gespeicherte Prozeduren

Gespeicherte Prozeduren in MySQL und in vielen anderen relationalen Datenbanken Systeme sind benannte Objekte, die eine oder mehrere Anweisungen enthalten und dann bei Aufruf von der Datenbank in einer Reihenfolge ausgeführt. Im Im einfachsten Beispiel kann eine gespeicherte Prozedur eine allgemeine Anweisung unter speichern eine wiederverwendbare Routine, z. B. das Abrufen von Daten aus der Datenbank mit häufig verwendete Filter. Sie könnten beispielsweise eine gespeicherte Prozedur erstellen, um Abrufen von Online-Shop-Kunden, die innerhalb der letzten angegebenen Frist Bestellungen aufgegeben haben Anzahl der Monate. In den komplexesten Szenarien können gespeicherte Prozeduren dies tun stellen umfangreiche Programme dar, die komplexe Geschäftslogiken beschreiben Robuste Anwendungen.

Der Befehlssatz in einer gespeicherten Prozedur kann gängige SQL-Anweisungen wie SELECT- oder INSERT-Abfragen umfassen, die Daten zurückgeben oder manipulieren. Darüber hinaus können gespeicherte Prozeduren Folgendes nutzen:

• Parameter, die an die gespeicherte Prozedur übergeben oder über sie zurückgegeben werden.
• Deklarierte Variablen, um abgerufene Daten direkt im Prozedurcode zu verarbeiten.
• Bedingte Anweisungen, die die Ausführung von Teilen des ermöglichen Gespeicherter Prozedurcode abhängig von bestimmten Bedingungen, wie z. B. IF- oder CASE-Anweisungen.
• Schleifen wie WHILE, LOOP und REPEAT ermöglichen die mehrfache Ausführung von Teilen des Codes, z. B. für jede Zeile in abgerufenen Daten gesetzt.
• Anweisungen zur Fehlerbehandlung, z. B. die Rückgabe von Fehlermeldungen an die Datenbankbenutzer, die auf die Prozedur zugreifen.
• Aufrufe an andere gespeicherte Prozeduren in der Datenbank.

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

Der Datenbankbenutzer muss über die entsprechenden Berechtigungen zur Ausführung verfügen das vorgegebene Verfahren. Diese Berechtigungsanforderung bietet eine Ebene von Sicherheit, indem der direkte Zugriff auf die Datenbank verweigert und Benutzern gleichzeitig Zugriff gewährt wird zu einzelnen Prozeduren, deren Ausführung garantiert sicher ist.

Gespeicherte Prozeduren werden direkt auf dem Datenbankserver ausgeführt, Durchführung aller Berechnungen lokal und Rückgabe der Ergebnisse an den Aufrufer Benutzer erst nach Abschluss.

Wenn Sie das Verhalten des Verfahrens ändern möchten, können Sie das aktualisieren Prozedur in der Datenbank, und die Anwendungen, die sie verwenden, werden dies tun automatisch die neue Version abrufen. Alle Benutzer starten sofort den neuen Verfahrenscode verwenden, ohne dass eine Anpassung erforderlich ist Anwendungen.

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 ersten und letzten Anweisungen in diesem Codefragment sind DELIMITER // und DELIMITER ;. Normalerweise verwendet MySQL das Semikolon-Symbol (;) um Anweisungen abzugrenzen und anzugeben, wann sie beginnen und enden. Wenn du Führen Sie mehrere Anweisungen in der MySQL-Konsole aus, getrennt durch Semikolons werden als separate Befehle behandelt und ausgeführt unabhängig voneinander, einer nach dem anderen. Die gespeicherte Prozedur kann jedoch Schließen Sie mehrere Befehle ein, die nacheinander ausgeführt werden, wenn sie ausgeführt werden wird angerufen. Dies stellt eine Schwierigkeit dar, wenn versucht wird, MySQL anzuweisen, etwas zu erstellen ein neues Verfahren. Die Datenbank-Engine würde auf das Semikolonzeichen stoßen im Hauptteil der gespeicherten Prozedur und denken, dass die Ausführung beendet werden sollte Stellungnahme. In dieser Situation ist die beabsichtigte Aussage das Ganze Prozedurerstellungscode, keine einzige Anweisung innerhalb der Prozedur selbst, sodass MySQL Ihre Absichten falsch interpretieren würde.

Um diese Einschränkung zu umgehen, verwenden Sie den Befehl DELIMITER, um das Trennzeichen vorübergehend von ; zu ändern. bis // für die Dauer der CREATE PROCEDURE Anruf. Dann werden alle Semikolons im Hauptteil der gespeicherten Prozedur angezeigt unverändert an den Server übergeben. Nachdem der gesamte Vorgang abgeschlossen ist, wird die Trennzeichen wird wieder geändert in ; mit dem letzten DELIMITER ;.

Das Herzstück des Codes zum Erstellen einer neuen Prozedur ist der CREATE PROCEDURE-Aufruf, gefolgt vom Namen der Prozedur: procedure_name im Beispiel. Auf den Prozedurnamen folgt eine optionale Liste von Parameter, die die Prozedur akzeptiert. Der letzte Teil ist das Verfahren Körper, eingeschlossen in BEGIN- und END-Anweisungen. Darin befindet sich der Prozedurcode, der eine einzelne SQL-Anweisung wie eine SELECT-Abfrage oder komplexeren Code enthalten kann.

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

Im nächsten Abschnitt erstellen Sie eine grundlegende gespeicherte Prozedur ohne Parameter, die eine einzelne Abfrage einschließt.

Erstellen einer gespeicherten Prozedur ohne Parameter

In diesem Abschnitt erstellen Sie Ihre erste gespeicherte Prozedur, die eine einzelne SQL-SELECT-Anweisung kapselt, um die Liste der eigenen Autos zurückzugeben, sortiert nach Marke und Wert in absteigender Reihenfolge.

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

SELECT * FROM cars ORDER BY make, value DESC;

Die Datenbank gibt die Liste der Autos zurück aus der Tabelle „Autos“, zuerst sortiert nach Marke und dann innerhalb einer einzelnen 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 ganz oben auf der Liste, und der am wenigsten wertvolle Porsche erscheint unten.

Angenommen, diese Abfrage wird häufig in mehreren Anwendungen verwendet oder von mehreren Benutzern und gehen davon aus, dass Sie sicherstellen möchten, dass jeder das verwendet Genau die gleiche Art, die Ergebnisse zu ordnen. Dazu möchten Sie eine erstellen gespeicherte Prozedur, die diese Anweisung unter einem wiederverwendbaren Namen speichert Prozedur.

Um diese gespeicherte Prozedur zu erstellen, führen Sie das folgende Codefragment aus:

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

Wie im vorherigen Abschnitt beschrieben, sind der erste und der letzte Befehl (DELIMITER / / und DELIMITER ;) weisen MySQL an, das Semikolon-Zeichen für die Dauer der Prozedurerstellung nicht mehr als Anweisungstrennzeichen zu verwenden.

Das CREATE Auf den SQL-Befehl PROCEDURE folgt der Prozedurnameget_all_cars, den Sie definieren können, um die Funktionsweise der Prozedur am besten zu beschreiben. Nach dem Prozedurnamen steht ein Klammerpaar () Hier können Sie Parameter hinzufügen. In diesem Beispiel ist dies bei der Prozedur nicht der Fall Verwenden Sie Parameter, daher sind die Klammern leer. Anschließend wird zwischen den Befehlen BEGIN und END, die den Anfang und das Ende des Prozedurcodeblocks definieren, die zuvor verwendete SELECT-Anweisung wörtlich geschrieben.

Die Datenbank antwortet mit einer Erfolgsmeldung:

OutputQuery OK, 0 rows affected (0.02 sec)

Dieget_all_carsProzedur ist jetzt in der Datenbank gespeichert und führt beim Aufruf die gespeicherte Anweisung als aus ist.

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

CALLget_all_cars;

Der Prozedurname,get_all_cars, ist alles, was Sie zur Verwendung der Prozedur benötigen. Sie müssen keinen Teil der zuvor verwendeten SELECT-Anweisung mehr manuell eingeben. Die Datenbank zeigt die Ergebnisse genau wie die Ausgabe der zuvor ausgeführten SELECT-Anweisung an:

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 jetzt erfolgreich eine gespeicherte Prozedur ohne Parameter erstellt, die alle Autos aus der Autostabelle in einer bestimmten Reihenfolge zurückgibt . Sie können das Verfahren über mehrere Anwendungen hinweg verwenden.

Im nächsten Abschnitt erstellen Sie ein Verfahren, das akzeptiert Parameter, um das Verhalten des Verfahrens abhängig von der Benutzereingabe zu ändern.

Das obige ist der detaillierte Inhalt vonSo verwenden Sie gespeicherte Prozeduren in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!