Grundlagen von MySQL gespeicherten Prozeduren

Einführung

In diesem Lab werden Sie die Grundlagen von MySQL-Speicherprozeduren (stored procedures) lernen. Das Ziel besteht darin, zu verstehen, wie man Speicherprozeduren erstellt, aufruft und modifiziert, um Daten in einer MySQL-Datenbank zu verwalten.

Sie beginnen damit, eine Datenbank und eine Tabelle namens employees zu erstellen. Dann werden Sie eine Speicherprozedur namens insert_employee schreiben, um Daten in die employees-Tabelle einzufügen. Sie werden lernen, wie Sie diese Prozedur mit der CALL-Anweisung aufrufen und wie Sie Eingabeparameter zur Prozedur hinzufügen. Schließlich werden Sie lernen, wie Sie die Prozedur mit der DROP PROCEDURE-Anweisung löschen.

Skills Graph

Schreiben einer Prozedur zum Einfügen von Daten

In diesem Schritt werden Sie lernen, wie Sie in MySQL eine gespeicherte Prozedur (stored procedure) erstellen, die Daten in eine Tabelle einfügt. Gespeicherte Prozeduren sind vorkompilierte SQL-Anweisungen, die in der Datenbank gespeichert sind. Sie können über ihren Namen ausgeführt werden, was die Leistung und Sicherheit verbessern kann.

Zunächst erstellen wir eine einfache Tabelle, mit der wir arbeiten können. Öffnen Sie Ihr Terminal und verbinden Sie sich mit dem MySQL-Server mit dem folgenden Befehl:

mysql -u root -p

Sie werden nach dem Root-Passwort gefragt. Geben Sie das Passwort ein und drücken Sie die Eingabetaste.

Nun erstellen wir eine Datenbank namens testdb:

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

Als Nächstes erstellen wir eine Tabelle namens employees mit der folgenden Struktur:

CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department VARCHAR(255)
);

Jetzt, da wir eine Tabelle haben, erstellen wir eine gespeicherte Prozedur, um Daten in diese einzufügen. Eine gespeicherte Prozedur wird mit der CREATE PROCEDURE-Anweisung erstellt. Wir definieren eine Prozedur namens insert_employee, die einen neuen Mitarbeiterdatensatz einfügt.

Hier ist der SQL-Code für die gespeicherte Prozedur:

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 analysieren:

• DELIMITER //: Dies ändert das Anweisungs-Trennzeichen von ; auf //. Dies ist notwendig, da die Prozedur selbst Semikolons enthält, und wir MySQL mitteilen müssen, dass die gesamte Prozedurdefinition als eine einzelne Anweisung behandelt werden soll.
• CREATE PROCEDURE insert_employee: Dies deklariert die Erstellung einer gespeicherten Prozedur 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 gibt 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 employees-Tabelle einfügt, wobei die als Eingabeparameter übergebenen Werte verwendet werden.
• DELIMITER ;: Dies setzt das Anweisungs-Trennzeichen wieder auf ; zurück.

Um diesen Code auszuführen, können Sie ihn direkt in Ihr MySQL-Terminal kopieren und einfügen.

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

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

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

Sie haben nun erfolgreich eine gespeicherte Prozedur erstellt, um Daten in die employees-Tabelle einzufügen. Im nächsten Schritt werden Sie lernen, wie Sie diese Prozedur aufrufen.

Aufrufen der Prozedur mit der CALL-Anweisung

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

Stellen Sie zunächst sicher, dass Sie mit dem MySQL-Server verbunden sind und die testdb-Datenbank verwenden. Wenn Sie noch nicht verbunden sind, verwenden Sie die folgenden Befehle:

mysql -u root -p

Geben Sie das Passwort ein, wenn Sie dazu aufgefordert werden.

USE testdb;

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

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

In unserem Fall lautet der Name der Prozedur insert_employee, und sie erwartet zwei Argumente: den Namen des Mitarbeiters und die Abteilung des Mitarbeiters.

Rufen wir die insert_employee-Prozedur 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 insert_employee-Prozedur mit den angegebenen Argumenten aus.

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

SELECT * FROM employees;

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

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

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

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

SELECT * FROM employees;

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

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

Hinzufügen eines Eingabeparameters zur Prozedur

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

Fügen wir der insert_employee-Prozedur einen employee_salary-Parameter hinzu. Dadurch können wir das Gehalt des Mitarbeiters angeben, wenn wir einen neuen Datensatz einfügen.

Zunächst müssen Sie die vorhandene Prozedur löschen. Wenn Sie sie nicht löschen, erhalten Sie einen Fehler, wenn Sie versuchen, eine Prozedur mit demselben Namen zu erstellen. Verbinden Sie sich mit MySQL, falls Sie noch nicht verbunden sind:

mysql -u root -p

Geben Sie das Passwort ein, wenn Sie dazu aufgefordert werden.

USE testdb;

Löschen Sie die vorhandene Prozedur:

DROP PROCEDURE IF EXISTS insert_employee;

Nun erstellen wir die geänderte gespeicherte Prozedur 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) VALUES (employee_name, employee_department);
    -- Add salary update after insert
    UPDATE employees SET salary = employee_salary WHERE name = employee_name AND department = employee_department;
END //
DELIMITER ;

Lassen Sie uns die Änderungen analysieren:

• 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 der employees-Tabelle eine neue Spalte salary hinzugefügt. Sie müssen diese Spalte manuell mit der folgenden SQL-Anweisung hinzufügen:

ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

• Wir haben eine UPDATE-Anweisung hinzugefügt, um das Gehalt des neu eingefügten Mitarbeiters zu aktualisieren. Da die INSERT-Anweisung nicht direkt das Setzen des Gehalts unterstützt, fügen wir zunächst den Mitarbeiter ein und aktualisieren dann das Gehalt basierend auf dem Namen und der Abteilung.

Nun rufen wir die geänderte insert_employee-Prozedur 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 employees-Tabelle abfragen:

SELECT * FROM employees;

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

Sie haben nun erfolgreich einen Eingabeparameter zur gespeicherten Prozedur insert_employee hinzugefügt und überprüft, dass die Daten korrekt eingefügt wurden. Dies zeigt, wie gespeicherte Prozeduren modifiziert werden können, um neue Anforderungen zu erfüllen.

Löschen der Prozedur

In diesem letzten Schritt werden Sie lernen, wie Sie eine gespeicherte Prozedur aus der Datenbank löschen. Wenn Sie eine Prozedur löschen, wird sie aus der Datenbank entfernt und kann nicht mehr ausgeführt werden.

Stellen Sie zunächst sicher, dass Sie mit dem MySQL-Server verbunden sind und die testdb-Datenbank verwenden. Wenn Sie noch nicht verbunden sind, verwenden Sie die folgenden Befehle:

mysql -u root -p

Geben Sie das Passwort ein, wenn Sie dazu aufgefordert werden.

USE testdb;

Die DROP PROCEDURE-Anweisung wird verwendet, um eine gespeicherte Prozedur zu entfernen. Die Syntax lautet wie folgt:

DROP PROCEDURE [IF EXISTS] procedure_name;

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

In unserem Fall lautet der Name der Prozedur insert_employee. Löschen wir die Prozedur:

DROP PROCEDURE IF EXISTS insert_employee;

Diese Anweisung wird die insert_employee-Prozedur aus der testdb-Datenbank entfernen.

Um zu überprüfen, ob die Prozedur gelöscht wurde, können Sie erneut den Status der Prozedur anzeigen lassen:

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 der folgenden: ERROR 1305 (42000): PROCEDURE testdb.insert_employee does not exist.

Sie haben nun erfolgreich die gespeicherte Prozedur insert_employee gelöscht. Damit ist die Lab-Übung (LabEx) zum Erstellen, Aufrufen, Modifizieren und Löschen von gespeicherten Prozeduren in MySQL abgeschlossen.

Zusammenfassung

In diesem Lab (LabEx) haben Sie die Grundlagen von MySQL gespeicherten Prozeduren gelernt, beginnend mit der Erstellung einer Datenbank und einer Tabelle namens employees. Anschließend haben Sie eine gespeicherte Prozedur namens insert_employee mit der CREATE PROCEDURE-Anweisung definiert, die Daten in die employees-Tabelle einfügt. Der DELIMITER-Befehl wurde verwendet, um Semikolons innerhalb der Prozedurdefinition zu verarbeiten.

Das Lab hat auch behandelt, wie Sie Eingabeparameter für die gespeicherte Prozedur definieren und deren Namen und Datentypen angeben. Dies ermöglicht es Ihnen, Werte an die Prozedur zu übergeben, wenn sie aufgerufen wird, was die Prozedur flexibler und wiederverwendbarer macht.