Programmierung in MySQL Teil 5 – Cursor

Im heutigen Teil meiner Artikelserie zur Programmierung in MySQL wird es um Cursor gehen. Cursor werden in MySQL verwendet, um mehrere Zeilen eines Ergebnisses abzuarbeiten. Sie sind von der Syntax her allerdings etwas gewöhnungsbedürftig. Daher werde ich den Aufbau heute kurz vorstellen und euch ein Template an die Hand geben, um Cursor-Konstrukte schnell erstellen zu können.

Überblick über die MySQL Artikelserie

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

Grundidee

Das Wort Cursor kann man mit „Zeiger“ übersetzen, was ihn schon ganz gut beschreibt. Ein Cursor zeigt immer auf einen bestimmten Datensatz in einer Ergebnismenge. Setzt man diesen Zeiger zunächst auf den ersten Datensatz und schiebt ihn dann Stück für Stück weiter, kann man alle Datensätze der Abfrage abarbeiten. Das ist immer dann nützlich, wenn die Abarbeitung zu komplex ist, um über ein einzelnes MySQL-Kommando zu erfolgen.

Cursor deklarieren

Ein Cursor muss genau wie eine Variable am Anfang der Routine deklariert werden. Die Syntax dafür lautet:

DECLARE Cursorname CURSOR AS SELECT Spaltenname FROM Tabellenname [WHERE...]

Hier legt man also schon die SQL-Abfrage fest, deren Ergebnismenge vom Cursor durchlaufen werden soll. Den Wert der hier ausgewählten Spalte kann man später in der Cursor-Schleife verwenden. In der Regel verwendet man dafür den Primärschlüssel der Tabelle.

Damit die Schleife später feststellen kann, wann die Ergebnismenge fertig durchlaufen ist, muss man zusätzlich einen sogenannten Continue Handler deklarieren. Dieser führt bei einem bestimmten Ereignis eine bestimmte Aktion durch. Den Continue Handler kann man im Prinzip frei wählen, aber üblich ist die folgende Variante:

DECLARE var_done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done = 1;

Wir deklarieren also eine Integer-Variable var_done mit dem Default-Wert 0 und einen Continue Handler, der die Variable auf 1 setzt, sobald ein Datensatz nicht gefunden wurde.

Cursor öffnen

Bevor der Cursor benutzt werden kann, muss man ihn mit dem folgenden Befehl öffnen:

OPEN Cursorname;

Der Cursor ist nun offen, muss aber noch auf den ersten Datensatz gesetzt werden, bevor man loslegen kann:

FETCH NEXT FROM Cursorname INTO Variablenname;

Mit diesem Befehl setzt man den Cursor auf den ersten Datensatz und speichert den Primärschlüssel des Datensatzes in einer Variable.

Cursor durchlaufen

Nun kommt der eigentliche Cursordurchlauf, der in der Regel über eine While-Schleife realisiert wird. Die Schleife soll so lange durchlaufen werden, bis alle Datensätze fertig abgearbeitet ist, also bis der oben deklarierte Continue Handler ausgelöst wurde:

WHILE NOT var_done DO

Ähnlich wie in PHP wird auch bei MySQL eine Variable als false betrachte, so lange sie den Wert 0 hat.

In der Schleife erfolgt nun die eigentliche Abarbeitung, die natürlich vom Anwendungsfall abhängt. Anschließend muss der Cursor auf den nächsten Datensatz gesetzt werden:

FETCH Cursorname INTO Variablenname;

Dies wird in der Regel in der letzten Zeile der While-Schleife gemacht. Scheitert dieser Fetch-Befehl, weil es keinen Datensatz mehr gibt, wird der Continue Handler aktiviert, die Variable var_done auf 1 gesetzt und die Schleife wird beendet.

Cursor schließen

Zu jedem Öffnen gehört auch ein Schließen und so sollte man der Ordnung halber den Cursor nach dem Durchlauf auch wieder schließen:

CLOSE Cursorname;

Kleiner Fallstrick

Ein Continue Handler lässt sich nur global deklarieren und nicht für den einzelnen Cursor. Man sollte daher daran denken, dass immer wenn ein Datensatz nicht gefunden wurde, der Continue Handler ausgelöst wird und var_done auf 1 gesetzt wird, was zum Abbruch der Schleife führt. Das betrifft auch SELECT-Befehle. Damit die Schleife also korrekt arbeitet, muss man var_done gegebenenfalls wieder auf 0 setzen.

Template

Da das Cursor-Konstrukt doch recht aufwändig ist und man schnell eines der Elemente vergisst, habe ich mir ein kleines Template gebastelt, was ich immer per Copy & Paste in meine Routine einfüge und dann anpasse. Dieses möchte ich euch nicht vorenthalten und lade euch herzlich ein, es ebenfalls als Vorlage zu benutzen:

DELIMITER $$

CREATE PROCEDURE `cursor_example`()
BEGIN

DECLARE var_col1, var_done INT DEFAULT 0;
DECLARE myCursor CURSOR FOR SELECT table1.col1 FROM table1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done = 1;

SET var_done = 0; #only required if there is a SELECT ... INTO ... before opening the cursor, which could return no results

OPEN myCursor;
FETCH NEXT FROM myCursor INTO var_col1;

WHILE NOT var_done DO

UPDATE table2 SET table2.col2 = 0 WHERE table2.col1 = var_col1; #or whatever the loop is supposed to do

SET var_done = 0; #only required if there is a SELECT ... INTO ... in the loop, which could return no results
FETCH myCursor INTO var_col1;

END WHILE;
CLOSE myCursor;

END$$

Fazit

Cursor sind vom Handling her etwas unpraktisch, aber sehr mächtig. Mit ihnen kann man komplexe Algorithmen komplett auf Datenbankebene durchführen. Solltest du Probleme mit dem Einsatz von Cursorn haben, helfe ich 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.


 


11 Gedanken zu “Programmierung in MySQL Teil 5 – Cursor
  1. Hallo Jonas,
    ich freue mich über die tollen Erklärungen auf dieser Seite. Leider ist es mir nicht gelungen ein einfaches Beispiel anhand der Tabelle test_table mittels Cursor umzusetzen.

    Wie wäre deine Vorlage anzupassen, damit folgendes passiert:
    In meiner Tabelle test_table haben die Felder test_id,test_name,test_zeichenvorschrift den Index col1,col2,col3.
    Die Tabelle soll durchlaufen werden und der erste Datensatz im Feld ‚test_zeichenvorschrift‘ den Wert ‚zv_1‘ erhalten, der zweite Datensatz den Wert ‚zv_2, der drittte wiederum den Wert ‚zv_1‘ der vierte ‚zv_2‘ und dies in einer Schleife über die gesamte Tabelle.
    In der produktiven Umgebung sind es ca. 140 Zeichenvorschriften, die über die Schleife auf ein paar Hundert Datensätze zu verteilen sind.

  2. Hallo Frank,

    das sollte möglich sein. Für das zv_1 und zv_2 würde ich eine Variable deklarieren. Bei jedem Durchlauf wird dann der Wert der Variable überprüft und auf den jeweils anderen Wert gesetzt.

    Was hast du denn bisher an Code und woran hapert es?

    Viele Grüße und schönes Wochenende,
    Jonas

  3. Hallo Jonas,
    vielen Dank für die schnelle Rückmeldung! Sorry, das ich mich jetzt erst melde.

    Letztlich hat es damit funktioniert (ist nicht schön, aber geht irgendwie. Da ist ganz deutlich noch Optimierungspotenzial):

    DELIMITER $$
    USE `fixed`$$ DROP procedure IF EXISTS `zv_einzugsgebiete`$$ CREATE PROCEDURE `zv_einzugsgebiete`()

    BEGIN

    DECLARE var_col1, var_done, var_zv, var_zv_0 INT DEFAULT 0;
    DECLARE myCursor CURSOR FOR SELECT gid FROM wasser_einzugsgebiete;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done = 1;
    SET var_done = 0;
    SET var_zv = 0;
    SET var_zv_0 = 0;
    OPEN myCursor;
    FETCH NEXT FROM myCursor INTO var_col1;

    WHILE NOT var_done DO

    WHILE var_zv <= 140 and var_zv_0 <= 10 DO # 141 Farben
    SET var_zv = var_zv + 1;
    UPDATE wasser_einzugsgebiete SET zeichenvorschrift = concat('3_einzugsgebiete_',var_zv) WHERE gid = var_col1;
    FETCH myCursor INTO var_col1;
    END WHILE;
    SET var_zv_0 = var_zv_0 + 1;
    SET var_zv = 0; # 10 Schleifen für derzeit 973 Datensätze

    END WHILE;
    CLOSE myCursor;
    END; $$
    DELIMITER ;

  4. Hallo Jonas,
    ja, dank deiner tollen Anleitung auf der Website ist es gelungen!
    Danke nochmals.
    Viele Grüße,
    Frank

  5. Hallo,

    vom Prinzip ist mir die Funktionsweise von Cursorn (ORACLE bis Version 11) bekannt, aber bei MySQL-Datenbanken scheitere ich momentan daran, dass sich selbst das im MySQL Handbuch abgedruckte curdemo() nicht realisieren lässt, weil es immer zu folgenden Fehlermeldungen kommt
    :

    Fehler

    SQL-Befehl:

    CREATE PROCEDURE curdemo( ) BEGIN DECLARE done INT DEFAULT 0;

    MySQL meldet: Dokumentation
    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near “ at line 3

  6. Nein, das war nur der Fehlermeldungscode, der Cursor war zunächst der von mitr verfasste Code:

    CREATE PROCEDURE curdemo()
    BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE ID_Suchort int;
    DECLARE ID_Suchwort int;
    DECLARE Suchort varchar(100);
    DECLARE Suchwort varchar(100);
    DECLARE Suchort_Zaehler bigint;
    DECLARE Suchwort_Zaehler bigint;
    DECLARE Suchkombi_Zaehler bigint;
    DECLARE cur1 CURSOR FOR SELECT ID_Suchort, ID_Suchwort, SO_Normiert, SW_Normiert, SO_Zaehler, SW_Zaehler, SK_Zaehler FROM tmp_Auswertungen;
    DECLARE CONTINUE HANDLER FOR SQLSTATE ‚02000‘ SET done = 1;

    OPEN cur1;

    REPEAT
    FETCH cur1 INTO ID_Suchort, ID_Suchwort, Suchort, Suchwort, Suchort_Zaehler, Suchwort_Zaehler, Suchkombi_Zaehler;
    INSERT INTO tmp_Statistik()
    SELECT br.ID_Branche, br.Aufrufanzahl, br.Nummer, br.Bezeichnung, cur1.ID_Suchwort, cur1.SW_Normiert, cur1.SW_Zaehler,
    br.ID_Orte, br.Aufrufe, br.Ort, cur1.ID_Suchort, cur1.SO_Normiert, cur1.SO_Zaehler,
    cur1.SK_Zaehler
    FROM Orte o, Branchen br
    WHERE o.Ort_Norm LIKE ‚%’||cur1.SO_Normiert||’%‘
    AND br.Bezeichnung_Normiert LIKE ‚%’||cur1.SW_Normiert||’%‘
    UNTIL done END REPEAT;

    CLOSE cur1;
    END

    und später der Original-Code aus dem MySQL Handbuch zum Cursor-Beispiel.

    Aber die Fehlermeldung blieb immer die gleiche.

  7. Ah okay. Also mir fallen 3 Dinge auf.

    1. INSERT INTO tmp_Statistik() …
    Du scheinst hier einen INSERT in eine Funktion/Prozedur zu machen. Das ist meines Wissens nicht möglich.

    2. … cur1.ID_Suchwort, cur1.SW_Normiert, cur1.SW_Zaehler, …
    Du benutzt den Cursor wie eine Tabelle. Die Werte sind aber in normalen Variablen:
    … ID_Suchwort, Suchort, Suchwort_Zaehler …
    Gut möglich, dass sich MySQL hier von Oracle unterscheidet. Guck dir zum Vergleich nochmal mein Template aus dem Artikel an.

    3. Vielleicht hast du es nur nicht mit kopiert, aber vor der Prozedur musst du den Delimiter ändern und diesen dann auch hinter dem END verwenden. Guck dir auch dazu nochmal mein Template an.

    Viel Erfolg,
    Jonas

  8. Guten Tag Jonas,
    Frage: Kann man mit einem Cursor auch die Feldnamen aus z.B. einer MySql-View so auslesen, daß ein neues SELECT-Statement damit erzeugt werden kann?
    Hintergrund: Viele strukturgleiche Tabellen sollen in eine normalisierte Zielstruktur per UNION-Statement INSERTed werden.

Kommentieren

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