Grundlagen von MySQL Stored Procedures

Einführung

In diesem Lab lernen Sie die Grundlagen von MySQL Stored Procedures. Ziel ist es, zu verstehen, wie man Stored Procedures erstellt, aufruft und modifiziert, um Daten innerhalb einer MySQL-Datenbank zu verwalten.

Sie beginnen mit der Erstellung einer Datenbank und einer Tabelle namens employees. Anschließend schreiben Sie eine Stored Procedure namens insert_employee, um Daten in die Tabelle employees einzufügen. Sie lernen, wie Sie diese Prozedur mit der CALL-Anweisung aufrufen und wie Sie Eingabeparameter zur Prozedur hinzufügen. Abschließend lernen Sie, wie Sie die Prozedur mit der DROP PROCEDURE-Anweisung löschen.

Hinweis: Für dieses Lab müssen Sie sich nur einmal zu Beginn in die MySQL-Shell einloggen und am Ende wieder austreten. Alle SQL-Befehle in den folgenden Schritten sollten innerhalb derselben MySQL-Sitzung ausgeführt werden. Es ist nicht notwendig, sich zwischen den Schritten wiederholt mit MySQL zu verbinden oder zu trennen.

Prozedur zum Einfügen von Daten erstellen

In diesem Schritt lernen Sie, wie Sie eine Stored Procedure in MySQL erstellen, die Daten in eine Tabelle einfügt. Stored Procedures sind vorkompilierte SQL-Anweisungen, die in der Datenbank gespeichert sind. Sie können anhand ihres Namens ausgeführt werden, was die Leistung und Sicherheit verbessern kann.

Öffnen Sie zunächst Ihr Terminal und verbinden Sie sich mit dem MySQL-Server, indem Sie den folgenden Befehl verwenden:

sudo mysql -u root

Dieser Befehl verbindet sich als Benutzer root mit dem MySQL-Server. Halten Sie diese MySQL-Sitzung für alle nachfolgenden Schritte offen.

Sobald Sie verbunden sind, befinden Sie sich in der MySQL-Shell. Wechseln Sie nun zur Datenbank testdb, die während der Einrichtung erstellt wurde:

USE testdb;

Da wir uns nun in der richtigen Datenbank befinden, erstellen wir eine Stored Procedure, um Daten in die Tabelle employees einzufügen. Eine Stored Procedure wird mit der Anweisung CREATE PROCEDURE erstellt. Wir definieren eine Prozedur namens insert_employee, die einen neuen Mitarbeiterdatensatz einfügt.

Hier ist der SQL-Code für die Stored Procedure:

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255))
BEGIN
    INSERT INTO employees (name, department) VALUES (employee_name, employee_department);
END //
DELIMITER ;

Lassen Sie uns diesen Code aufschlüsseln:

• DELIMITER //: Dies ändert den Anweisungsbegrenzer von ; zu //. Dies ist notwendig, da die Prozedur selbst Semikolons enthält und wir MySQL mitteilen müssen, die gesamte Prozedurdefinition als eine einzige Anweisung zu behandeln.
• CREATE PROCEDURE insert_employee: Dies deklariert die Erstellung einer Stored Procedure namens insert_employee.
• (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255)): Dies definiert die Eingabeparameter für die Prozedur. employee_name und employee_department sind die Namen der Parameter, und VARCHAR(255) ist ihr Datentyp. Das Schlüsselwort IN zeigt an, dass es sich um Eingabeparameter handelt.
• BEGIN ... END: Dieser Block enthält die SQL-Anweisungen, die ausgeführt werden, wenn die Prozedur aufgerufen wird.
• INSERT INTO employees (name, department) VALUES (employee_name, employee_department);: Dies ist die SQL-Anweisung, die eine neue Zeile in die Tabelle employees einfügt, wobei die als Eingabeparameter übergebenen Werte verwendet werden.
• DELIMITER ;: Dies setzt den Anweisungsbegrenzer zurück auf ;.

Um diesen Code auszuführen, kopieren Sie ihn und fügen Sie ihn direkt in Ihre MySQL-Shell ein.

Nachdem Sie den Code ausgeführt haben, können Sie überprüfen, ob die Prozedur erstellt wurde, indem Sie den folgenden Befehl in der MySQL-Shell ausführen:

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

Dieser Befehl zeigt Informationen über die Prozedur insert_employee an, einschließlich ihres Namens, ihrer Datenbank und ihres Erstellungsdatums.

Sie haben nun erfolgreich eine Stored Procedure zum Einfügen von Daten in die Tabelle employees erstellt. Im nächsten Schritt lernen Sie, wie Sie diese Prozedur aufrufen.

Prozedur mit CALL-Anweisung aufrufen

Im vorherigen Schritt haben Sie eine Stored Procedure namens insert_employee erstellt. In diesem Schritt lernen Sie, wie Sie diese Prozedur mit der CALL-Anweisung aufrufen.

Erinnerung: Sie sollten sich immer noch in der MySQL-Shell befinden und die Datenbank testdb verwenden. Wenn nicht, wechseln Sie mit folgendem Befehl dorthin:

USE testdb;

Die CALL-Anweisung wird verwendet, um eine Stored Procedure auszuführen. Die Syntax lautet wie folgt:

CALL prozedurname(argument1, argument2, ...);

In unserem Fall ist der Prozedurname insert_employee, und sie nimmt zwei Argumente entgegen: den Namen des Mitarbeiters und die Abteilung des Mitarbeiters.

Rufen wir die Prozedur insert_employee auf, um einen neuen Mitarbeiter namens "Alice Smith" in der Abteilung "Engineering" einzufügen:

CALL insert_employee('Alice Smith', 'Engineering');

Diese Anweisung führt die Prozedur insert_employee mit den angegebenen Argumenten aus.

Um zu überprüfen, ob die Daten korrekt eingefügt wurden, können Sie die Tabelle employees in der MySQL-Shell abfragen:

SELECT * FROM employees;

Sie sollten eine neue Zeile in der Tabelle mit dem Namen "Alice Smith" und der Abteilung "Engineering" sehen. Die id wird automatisch zugewiesen.

Fügen wir einen weiteren Mitarbeiter ein, "Bob Johnson" in der Abteilung "Marketing":

CALL insert_employee('Bob Johnson', 'Marketing');

Überprüfen Sie die Einfügung erneut, indem Sie die Tabelle employees abfragen:

SELECT * FROM employees;

Sie sollten nun zwei Zeilen in der Tabelle sehen, eine für "Alice Smith" und eine für "Bob Johnson".

Sie haben nun erfolgreich die Stored Procedure insert_employee mit der CALL-Anweisung aufgerufen und überprüft, ob die Daten korrekt eingefügt wurden. Dies zeigt, wie Stored Procedures verwendet werden können, um SQL-Logik zu kapseln und wiederzuverwenden.

Eingabeparameter zur Prozedur hinzufügen

In den vorherigen Schritten haben Sie eine Stored Procedure namens insert_employee erstellt und aufgerufen, die zwei Eingabeparameter akzeptiert: employee_name und employee_department. In diesem Schritt lernen Sie, wie Sie einen weiteren Eingabeparameter zur Prozedur hinzufügen.

Fügen wir der Prozedur insert_employee einen Parameter employee_salary hinzu. Dies ermöglicht es uns, das Gehalt des Mitarbeiters beim Einfügen eines neuen Datensatzes anzugeben.

Zuerst müssen Sie die vorhandene Prozedur löschen (drop). Wenn Sie dies nicht tun, erhalten Sie einen Fehler, wenn Sie versuchen, eine Prozedur mit demselben Namen zu erstellen. Führen Sie in Ihrer MySQL-Shell Folgendes aus:

DROP PROCEDURE IF EXISTS insert_employee;

Nun erstellen wir die modifizierte Stored Procedure mit dem neuen Eingabeparameter.

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255), IN employee_salary DECIMAL(10, 2))
BEGIN
    INSERT INTO employees (name, department, salary) VALUES (employee_name, employee_department, employee_salary);
END //
DELIMITER ;

Lassen Sie uns die Änderungen aufschlüsseln:

• Wir haben der Prozedurdefinition einen neuen Eingabeparameter IN employee_salary DECIMAL(10, 2) hinzugefügt. DECIMAL(10, 2) ist der Datentyp für das Gehalt, der bis zu 10 Ziffern mit 2 Dezimalstellen zulässt.
• Wir haben die INSERT-Anweisung geändert, um die Spalte salary und den Parameter employee_salary einzuschließen.

Rufen wir nun die modifizierte Prozedur insert_employee auf, um einen neuen Mitarbeiter namens "Charlie Brown" in der Abteilung "Finance" mit einem Gehalt von 60000.00 einzufügen:

CALL insert_employee('Charlie Brown', 'Finance', 60000.00);

Um zu überprüfen, ob die Daten korrekt eingefügt wurden, können Sie die Tabelle employees in der MySQL-Shell abfragen:

SELECT * FROM employees;

Sie sollten eine neue Zeile in der Tabelle mit dem Namen "Charlie Brown", der Abteilung "Finance" und dem Gehalt 60000.00 sehen.

Sie haben nun erfolgreich einen Eingabeparameter zur Stored Procedure insert_employee hinzugefügt und überprüft, ob die Daten korrekt eingefügt wurden. Dies zeigt, wie Stored Procedures modifiziert werden können, um neuen Anforderungen gerecht zu werden.

Prozedur löschen

In diesem letzten Schritt lernen Sie, wie Sie eine Stored Procedure aus der Datenbank löschen (drop). Das Löschen einer Prozedur entfernt sie aus der Datenbank, sodass sie nicht mehr ausgeführt werden kann.

Erinnerung: Sie sollten sich immer noch in der MySQL-Shell befinden und die Datenbank testdb verwenden.

Die Anweisung DROP PROCEDURE wird verwendet, um eine Stored Procedure zu entfernen. Die Syntax lautet wie folgt:

DROP PROCEDURE [IF EXISTS] prozedurname;

Die Klausel IF EXISTS ist optional, aber empfehlenswert. Sie verhindert, dass ein Fehler auftritt, wenn die Prozedur nicht existiert.

In unserem Fall ist der Prozedurname insert_employee. Lassen Sie uns die Prozedur löschen:

DROP PROCEDURE IF EXISTS insert_employee;

Diese Anweisung entfernt die Prozedur insert_employee aus der Datenbank testdb.

Um zu überprüfen, ob die Prozedur gelöscht wurde, können Sie versuchen, den Prozedurstatus erneut in der MySQL-Shell anzuzeigen:

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

Dieser Befehl sollte ein leeres Ergebnis zurückgeben, was darauf hinweist, dass die Prozedur nicht mehr existiert.

Alternativ erhalten Sie einen Fehler, wenn Sie versuchen, die Prozedur aufzurufen:

CALL insert_employee('Test', 'Test', 1000);

Dies führt zu einer Fehlermeldung ähnlich wie: ERROR 1305 (42000): PROCEDURE testdb.insert_employee does not exist.

Sie haben nun erfolgreich die Stored Procedure insert_employee gelöscht.

Sie können die MySQL-Shell nun verlassen, indem Sie Folgendes eingeben:

exit

Damit ist die Übung zum Erstellen, Aufrufen, Modifizieren und Löschen von Stored Procedures in MySQL abgeschlossen.

Zusammenfassung

In diesem Lab haben Sie die Grundlagen von MySQL Stored Procedures gelernt, beginnend mit der Erstellung einer Datenbank und einer Tabelle namens employees. Anschließend haben Sie eine Stored Procedure namens insert_employee mit der Anweisung CREATE PROCEDURE definiert, die Daten in die Tabelle employees einfügt. Der Befehl DELIMITER wurde verwendet, um Semikolons innerhalb der Prozedurdefinition zu handhaben.

Das Lab deckte auch ab, wie Eingabeparameter für die Stored Procedure definiert werden, einschließlich ihrer Namen und Datentypen. Dies ermöglicht es Ihnen, Werte an die Prozedur zu übergeben, wenn sie aufgerufen wird, was sie flexibler und wiederverwendbarer macht. Sie haben das Aufrufen der Stored Procedure mit der Anweisung CALL geübt und die Dateneinfügung verifiziert. Schließlich haben Sie gelernt, wie man eine Stored Procedure mit der Anweisung DROP PROCEDURE löscht.