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.