Cursor in Mysql
Ein Cursor in Mysql hilft euch über ein Result Set zu iterieren. Dabei muss er vorher definiert und geöffnet werden. Beim Deklarieren geben wir ihm das Select Statement mit, über das er iterieren soll. Der Cursor geht durch jede einzelne Zeile des Result Sets, bis er zum Ende angekommen ist. Dort schließen wir den Cursor wieder. Das Verhalten werde ich später im Beispiel genauer erklären.
Eigenschaften eines Cursors
Mysql unterstützt Cursors in z.B. stored Procedures. Folgende Eigenschaften sind zu beachten:
- Read only: es sind soweit keine Updates/Inserts direkt mit dem Cursor möglich
- Ein Cursor arbeitet sich von oben nach unten durch und kann keine Zeile überspringen
- Er muss vorher deklariert werden
Cursor Beispiel
Das Beispiel, welches ich hier mit einbeziehe, ist ein echtes Beispiel, welches von mir benutzt wird. Für das Windparc-Dashboard benötige ich am Anfang eines jeden Jahres die Plan-Daten, die ich per PDF Datei zur Verfügung gestellt bekomme. Ich hatte versucht diesen Prozess zu automatisieren, scheitere allerdings daran, dass die Tabellen in der PDF nicht korrekt ausgelesen werden können. Ich muss also 24 Windmühlen x 12 Monate = 288 Zeilen in der Tabelle anlegen, die außer der Windmühlen-ID, Jahr, Monat, Plan- und Istwerte die gleichen Werte haben. Diesen Schritt wollte ich mir also vereinfachen und habe mir ein stored Procedure geschrieben. Das Jahr wird dabei als IN Parameter übergeben.
Zur besseren Beschreibung des Prozesses, habe ich die Schritte visuell dargestellt. Kurz beschrieben hole ich mir die Windmühlen ID. Pro ID wird jeder Monat als ein Datensatz in der Tabelle erzeugt.
Prozess Beschreibung
Erklärung des Codes
Kommen wir also zum ersten Schritt. Ich deklariere die Variablen im Code, die notwendig sind. Darunter sind “finished”, “mill”, “month” und natürlich der Cursor selbst. Zu den 3 ersten Variablen folgt die Definition:
- “finished” ist ein Boolean und hilft uns später aus der Schleife zu springen.
- “mill” speichert die ID der Windmühle
- “month” ist die Anzahl der Monate, für die while Schleife
/*Wird für den Loop benötigt*/ DECLARE finished INTEGER DEFAULT FALSE; /*Variable für die Mühlen*/ DECLARE mill int DEFAULT 0; /*Um von Monat 12 herunter zu zählen*/ DECLARE month int DEFAULT 12; /*Cursor deklarieren */ DECLARE curwindmill CURSOR FOR select ID from DB.WIND_MILL where ID <> 1; /*Wenn der Loop am Ende angekommen ist setze finished = TRUE um aus dem Loop zu springen*/ DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;
Wir deklarieren den Cursor und geben ein Select Statement mit, über das er iterieren soll. Der Continue Handler hilft uns die Variable finished auf TRUE zu setzen, sobald die letzte Zeile vom Dataset verarbeitet wurde. Damit ihr euch besser vorstellen könnt worüber wir iterieren, bilde ich die WIND_MILL Tabelle teils ab.
| ID | WINDMILL | LOCATION | TYPE | WINDPARC | CREATED_AT | UPDATED_AT |
| 2 | H-WEA01 | 2 | 2 | 1 | 08.06.2020 20:56 | 08.06.2020 20:56 |
| 3 | H-WEA02 | 2 | 2 | 1 | 08.06.2020 20:56 | 08.06.2020 20:56 |
| 4 | H-WEA03 | 2 | 2 | 1 | 08.06.2020 20:56 | 08.06.2020 20:56 |
| 6 | H-WEA05 | 2 | 2 | 1 | 08.06.2020 20:56 | 08.06.2020 20:56 |
| … | … | … | … | … | … | … |
| 24 | G-WEA05 | 4 | 4 | 1 | 08.06.2020 20:56 | 08.06.2020 20:56 |
Durch die Deklarierung können wir nun den Cursor öffnen und ein Loop erzeugen. get_loop ist dabei nur der Loop Name.
/* Cursor öffnen*/ OPEN curwindmill; /*Über jeden Eintrag loopen*/ get_loop: LOOP
Mit Fetch holen wir uns die Daten vom Cursor und schreiben den Wert in mill. Sollte der Cursor am Ende angekommen sein, so wird der Loop anhand finished = True verlassen.
/*Das Ergebnis aus dem Cursor in mill schreiben*/
FETCH curwindmill INTO mill;
/*Sollte finished True werden, was der Fall ist, wenn über jeden Eintrag iteriert wurde, dann verlasse den Loop*/
IF finished THEN
/*Loop verlassen*/
LEAVE get_loop;
END IF;
Da ich hier noch die Monate beachten muss, habe ich eine while Schleife mit eingebaut. Diese sorgt dafür, dass von 12 heruntergezählt wird. Pro Monat wird für die ID ein neuer Datensatz erzeugt. Durch das IGNORE werden eventuell auftretende Fehler im Insert ignoriert und der Cursor springt zum nächsten Datensatz ohne dass ein neuer Datensatz angelegt wurde.
/*Solange month > 0 is schreibe die Daten in die Dabelle. Sollte der Eintrag schon vorhanden sein, ignoriere und gehe zum nächsten*/
WHILE month > 0 DO
/*Datensatz einfügen*/
INSERT IGNORE DB.WIND_ENERGY_PERFORMANCE VALUES(mill,month, YEAR, NULL, 0 , 1, 0, now(), now() );
/*Monat subtrahieren*/
SET month = month - 1;
END WHILE;
/*month resetten und für die nächste Mill von vorne anfangen*/
SET month = 12;
Zum Schluss wird die Variable month noch resetted und die while Schleife beendet, beziehungsweise erneut aufgerufen. Sollte das Ende erreicht sein, wird der Cursor geschlossen
SET month = 12; END LOOP get_loop; /*Cursor schließen*/ CLOSE curwindmill; END
Stored Procedure ausführen
Mit einem CALL, gefolgt vom Procedure Name und dem Jahr können wir nun neue Zeilen generieren.
call Metabase.P_WIND_ENERGY_PERFORMANCE_ADD_YEAR(2021)
| WINDMILL | MONTH | YEAR | ISPER | PLANPERF | UNIT | ISPERF(PCT) | CREATED_AT | UPDATED_AT |
| 3 | 1 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 3 | 2 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 3 | 3 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 3 | 4 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 3 | 5 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 3 | 6 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 3 | 7 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 3 | 8 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 3 | 9 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 3 | 10 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 3 | 11 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 3 | 12 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 4 | 1 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 4 | 2 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 4 | 3 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| 4 | 4 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
| … | … | … | … | … | … | … | … | |
| 24 | 12 | 2021 | NULL | 0 | 1 | 0 | 16.03.2021 20:01:20 | 16.03.2021 20:01:20 |
Kompletter Code
CREATE DEFINER=`user`@`%` PROCEDURE `Name`(
IN YEAR int
)
BEGIN
/*Wird für den Loop benötigt*/
DECLARE finished INTEGER DEFAULT FALSE;
/*Variable für die Mühlen*/
DECLARE mill int DEFAULT 0;
/*Um von Monat 12 herunter zu zählen*/
DECLARE month int DEFAULT 12;
/*Cursor deklarieren*/
DECLARE curwindmill CURSOR
FOR
select ID from DB.WIND_MILL where ID <> 1;
/*Wenn der Loop am Ende angekommen ist setze finished = TRUE um aus dem Loop zu springen*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;
/* Cursor öffnen*/
OPEN curwindmill;
/*Über jeden Eintrag loopen*/
get_loop: LOOP
/*Das Ergebnis aus dem Cursor in mill schreiben*/
FETCH curwindmill INTO mill;
/*Sollte finished True werden, was der Fall ist, wenn über jeden Eintrag iteriert wurde, dann verlasse den Loop*/
IF finished THEN
/*Loop verlassen*/
LEAVE get_loop;
END IF;
/*Solange month > 0 is schreibe die Daten in die Dabelle. Sollte der Eintrag schon vorhanden sein, ignoriere und gehe zum nächsten*/
WHILE month > 0 DO
/*Datensatz einfügen*/
INSERT IGNORE DB.WIND_ENERGY_PERFORMANCE VALUES(mill,month, YEAR, NULL, 0 , 1, 0, now(), now() );
/*Monat subtrahieren*/
SET month = month - 1;
END WHILE;
/*month resetten und für die nächste Mill von vorne anfangen*/
SET month = 12;
END LOOP get_loop;
/*Cursor schließen*/
CLOSE curwindmill;
END
