Programmierung in MySQL Teil 3 – Die erste Routine

Ursprünglich wollte ich in diesem Teil der Serie zur MySQL-Programmierung auf das ganze Brett an Variablendeklaration, Ablaufsteuerung etc. eingehen. Wie das so ist, merkt man dann beim Schreiben aber doch, dass es eine ganze Menge mehr zu erzählen gibt, als man zunächst dachte. Daher werde ich mich heute auf die grundsätzliche Erstellung von Routinen beschränken und die Grundkonstrukte im nächsten Artikel abhandeln. Ich werde kurz auf die beiden Arten von MySQL-Routinen eingehen und euch durch die Erstellung eurer ersten einfachen Routine führen.

Überblick über die Artikelserie

  1. Sinn und Unsinn
  2. Einrichtung und Vorbereitung
  3. Die erste Routine
  4. Grundkonstrukte
  5. Cursor
  6. Trigger
  7. Der Event Scheduler

Funktionen und Stored Procedures

MySQL unterscheidet zwischen Funktionen und Stored Procedures. Funktionen können einen Rückgabewert liefern, Stored Procedures hingegen nicht. Beide Typen können jedoch einen Ergebnissatz zurückliefern, so wie ein normales SELECT. Stored Procedures sind dafür etwas mächtiger und lassen sich z.B. auch rekursiv aufrufen, was bei Funktionen leider nicht möglich ist. In der Praxis sind die beiden Typen also oft austauschbar und werden daher unter dem Oberbegriff „Routinen“ zusammengefasst.

Die Sache mit dem Delimiter

In MySQL wird ja in der Standardeinstellunge das Semikolon (;) benutzt, um das Ende eines Kommandos zu markieren. Diese Markierung wird auch als Delimiter bezeichnet. Dabei ergibt sich für die Erstellung von Routinen ein Problem. Eine Routine wird mit einem CREATE-Kommando erzeugt. Die Routine besteht jedoch selbst wieder aus mehreren Kommandos, deren Ende markiert werden muss. Der Compiler ansich ist ja dumm und weiß bei einem Semikolon erst mal nicht, ob nur ein Kommando innerhalb der Routine beendet ist, oder die gesamte Routine. Daher müssen wir für die Erstellung der Routine den „äußeren“ Delimiter auf einen anderen Wert nutzen. Die MySQL-Workbench nutzt dafür $$, aber im Prinzip ist der Wert beliebig.

Hallo Welt

Wie es sich für ein ordentlichen Programmier-Tutorial gehört, starten wir auch hier mit einer Hello-World-Anwendung. Wer den letzten Artikel noch nicht gelesen hat, oder die MySQL-Workbench noch nicht installiert hat, sollte dies nun tun. Im Prinzip kann man auch mit PHPMyAdmin arbeiten, aber für die Entwicklung ist es auf Dauer doch sehr mühsam.

Fairerweise erstellt und die MySQL-Workbench direkt bei einem Klick auf „Create Routine“ ein Grundgerüst, das in etwas so aussieht:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE `test`.`new_routine` ()
BEGIN

END

In der ersten Zeile nach den Kommentaren wird der Delimiter auf einen anderen Wert gesetzt. Dann kommt der CREATE PROCEDURE Befehl, gefolgt von dem Routinennamen. Wie in anderen Programmiersprachen auch, können wir der Prozedur Argumente übergeben, die wie gewohnt in runden Klammern aufgelistet sind. Eine Routine ist genau wie eine Tabelle einer bestimmten Datenbank zugeordnet. Daher muss entweder die Datenbank vorver mit USE ausgewählt werden, oder der Routinenname muss wie hier zusammen mit der Datenbank notiert werden. Der eigentliche Inhalt der Routine wird dann durch die Schlüsselworte BEGIN und END eingeschlossen.

Was man hier sieht ist allerdings nicht die ganze Wahrheit. Klickt man an dieser Stelle einfach mal auf Apply, bekommt man den „echten“ SQL-Code angezeigt, den die Workbench erzeugt:


USE `test`;
DROP procedure IF EXISTS `new_routine`;

DELIMITER $$
CREATE PROCEDURE `test`.`new_routine` ()
BEGIN
END$$

DELIMITER ;

Zuerst wird die Datenbank ausgewählt. Dann wird eine evtl. vorhandene ältere Version der Routine gelöscht. Im Anschluss wird ein anderer Delimiter gesetzt und die Routine erstellt. Beachtet, dass das Ende des CREATE-Befehls nun mit einem $$ markiert wird. In der letzten Zeile wird der Delimiter wieder auf den Standardwert gesetzt, damit man bei weiteren Kommandos wieder wie gewohnt mit dem Semikolon arbeiten kann.

Da eine leere Routine noch nicht viel Sinn macht, klicken wir an dieser Stelle nochmal auf „Cancel“ und fügen in den Routinenkörper die folgende Zeile ein:
SELECT 'Hello World!';
Wer den SELECT-Befehl bislang nur benutzt hat, um Daten aus Tabellen zu ziehen, mag dies etwas befremdlich finden, aber mit SELECT kann man alle möglichen Daten auswählen. Die Routine nennen wir noch stilecht in „hello_world“ um.

DELIMITER $$

CREATE PROCEDURE `test`.`hello_world` ()
BEGIN
SELECT 'Hello World!';
END

Das Ergebnis schicken wir mit zwei Klicks auf „Apply“ auf die Datenbank. Wenn alles geklappt hat, teilt uns die Workbench mit, dass der Befehl erfolgreich ausgeführt wurde.
MySQL Workbench meldet Erfolg-

Die fertige Routine können wir nun mit dem Befehl CALL aufrufen und das Ergebnis ‚Hello World!‘ sollte erscheinen:
Aufruf von Hello World

Diesen CALL Befehl kann man nun ganz normal in der Query seiner Webanwendung benutzen und das Ergebnis wie ein ganz normales Ergebnis eines SELECT behandeln.

Parameter übergeben

Um heute nicht beim absoluten Trivialbeispiel stehen zu bleiben, wollen wir der Prozedur noch als Parameter unseren Namen übergeben und eine perönliche Begrüßung zu erhalten. Dafür öffnen wir den Routinen-Ordner. Mit einem Rechtklick auf unsere Routine und „Alter Routine…“ können wir die Routine bearbeiten. Sollte der Routinen-Ordner leer sein, hilft ein Rechtsklick darauf und „Refresh All“.

In die runden Klammern gehört nun unser Parameter. Der Name ist frei wählbar, er darf nur kein MySQL-Schlüsselwort enthalten. Benennt man einen Parameter oder eine Variable wie eine Tabelle, kann es zu Verwechslungen kommen. Ich stelle Parametern gerne ein par_ vor den eigentlichen Namen, um solche Probleme zu vermeiden und die Übersicht zu behalten. Hinter dem Namen muss man noch den Datentyp festlegen. Wir wollen hier unseren Namen übergeben, nutzen also ein VARCHAR(32), um auch bei längeren Namen keine Probleme zu bekommen.

CREATE PROCEDURE `hello_world`(par_name VARCHAR(32))
...

Den so festgelegten Parameter können wir nun verwenden. Dafür ändern wir unser SELECT wie folgt ab:
SELECT CONCAT('Hello ', par_name);

CONCAT setzt einen String aus mehreren Teilstrings zusammen, arbeiten also ähnlich wie der .-Operator in PHP.

Die geänderte Prozedur schicken wir wieder auf die Datenbank und übergeben nun beim Aufruf unseren Namen. Wenn alles geklappt hat, werden wir nun namentlich begrüßt.

Namentliche Begrüßung der MySQL-Routine

Fazit und Ausblick

Zugegeben: Für gestandene Programmierer ist heute noch nicht allzu viel Spannendes passiert und mit Hello World kann man natürlich noch nichts Produktives machen. Der Grundstein ist aber gelegt und mit den Grundkonstrukten im nächsten Teil der Serie kann man dann richtig loslegen. Ähnlich wie bei der objektorientierten Programmierung muss die Aufgabenstellung eine gewisse Komplexität haben, damit man die Vorteile der MySQL-Programmierung erkennt.

Solltest du bis hierhin Fragen haben oder ein Beispiel bei dir nicht klappen, melde dich gerne in den Kommentaren.

Merkblatt

Damit du immer schnell nachgucken kannst wie das mit der MySQL Programmierung nochmal ging, habe ich ein zweiseitiges Merkblatt erstellt. Dort stehen alle Beispiele dieser Serie nochmal drauf. Du kannst das Merkblatt ausdrucken und mit einem Blick dein Gedächtnis auffrischen.

Jetzt herunterladen

Außerdem kannst du meine regelmäßigen Entwickler Tipps bestellen. Etwa ein Mal pro Monat verschicke ich Tipps zur Arbeit als Entwickler, sowohl zur technischen Seite als auch zur Zusammenarbeit mit Kunden.

Ich bin damit einverstanden, die Tipps per E-Mail zu erhalten, kann sie aber jederzeit abbestellen. Für den Versand wird der Anbieter CleverReach benutzt. Weitere Infos in der Datenschutz-Erklärung.


 

5 thoughts on “Programmierung in MySQL Teil 3 – Die erste Routine
  1. Hallo.
    Da sich meine Probleme noch nicht gelöst haben, wende ich mich nocheinmal an Sie. Zur Erinnerung nochmals die grundlegenden Informationen. Ich habe eine Datenbank die ich mithilfe von phpmyadmin verwalte. Außerdem erstelle ich alle Triggers,Routinen,Events direkt in phpmyadmin.
    Dazu habe ich nun folgende Problemstellung: Ich habe einen funktionierenden Trigger, der Daten in eine Tabelle speichert. Dazu habe ich eine Routine und ein Event erstellt. Und hier mein Problem. Das Event und die Routine funktionieren aber nicht und kann mir nicht erklären warum. Die Häufigste Meldung die ich erhalte ist folgende: # MySQL lieferte ein leeres Resultat zurück (d.h. null Zeilen). Das Event soll jede Sekunde die Routine aufrufen.
    Event:
    DROP EVENT `Timerprobe`//
    CREATE EVENT `Timerprobe` ON SCHEDULE EVERY 1 SECOND STARTS ‚2013-02-14 16:12:02‘ ON COMPLETION NOT PRESERVE ENABLE DO CALL check_timer()
    Routine:
    DROP PROCEDURE `check_timer`//# MySQL lieferte ein leeres Resultat zurück (d.h. null Zeilen).

    CREATE DEFINER=`###########`#`#` PROCEDURE `check_timer`()
    BEGIN
    DECLARE var_timer_id INT;
    SELECT timer_id INTO var_timer_id FROM timer;
    IF timer.endtime < now() THEN
    UPDATE ##########.timer SET timer.finished = 1 WHERE timer.timer_id = var_timer_id LIMIT 1;
    END IF;
    END
    # MySQL lieferte ein leeres Resultat zurück (d.h. null Zeilen).

  2. Hi Chris,

    also das mit dem leeren Result liefert PHPMyAdmin soweit ich weiß immer aus, wenn es kein Ergebnis gab und ist meiner Meinung nach nicht das Problem.

    Ich sehe hier zwei potentielle Probleme in der Prozedur:

    1. SELECT … INTO darf nur eine Zeile zurück liefern, da MySQL sonst nicht weiß, welches Ergebnis in die Variable gespeichert werden soll. Du solltest das Ergebnis mit WHERE einschränken und im Zweifel einfach ein LIMIT 1 an die Query anhängen. Das werde ich bei Gelegenheit mal im Artikel über die Grundkonstrukte nachtragen.

    2. Du darfst in einer IF-Abfrage nicht einfach auf die Spalte einer Tabelle zugreifen. MySQL weiß gar nicht, um welche Zeile es sich handelt. Stattdessen würde ich dir den Ansatz empfehlen, die Bedingung bereits im SELECT abzufragen und dann zu überprüfen, ob in der Variablen etwas drin steht etwa so:

    CREATE PROCEDURE `check_timer`()
    BEGIN
    DECLARE var_timer_id INT;
    SELECT timer_id INTO var_timer_id FROM timer WHERE endtime < now() LIMIT 1; IF var_timer_id IS NOT NULL THEN UPDATE timer SET finished = 1 WHERE timer_id = var_timer_id LIMIT 1; END IF; ENDIch habe auch noch deinen Kommentar bei den Triggern beantwortet, den hatte ich irgendwie übersehen. Dort gab es ein ähnliches Problem.

  3. Hi Jonas,
    ich freue mich, dass ich „endlich“ dieses Tutorial gefunden habe.
    Bisher habe ich komplizierte Abfragen in mit php programmiert, wobei mir immer klar war, dass das nicht gut sein kann.
    Dank dieses Tutorials kann ich die Dinge zukünftig direkt in mysql programmieren was sich „gut anfühlt“.
    Vielen Dank für die Artikelserie.
    Gruß Mario

  4. Hallo Jonas,
    ist es von MySQL so vorgesehen, dass ich innerhalb einer Prozedur keine Ereignisse oder Trigger erstellen darf? Ich erhalte immer eine Fehlermeldung wenn ich das versuche.

Kommentieren

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert