Alle Zeilen einer MySQL-Tabelle durchlaufen

1. Alle Zeilen einer MySQL-Tabelle durchlaufen
2. Verwenden Sie eine WHILE-Schleife in einer gespeicherten Prozedur, um alle Zeilen einer MySQL-Tabelle zu durchlaufen
3. Verwenden Sie CURSOR in einer gespeicherten Prozedur, um alle Zeilen der MySQL-Tabelle zu durchlaufen
4. Vergleich der Verwendung von WHILE und CURSOR

Heute lernen wir die Verwendung von WHILE und CURSOR in einer gespeicherten Prozedur kennen, um alle Zeilen einer MySQL-Tabelle zu durchlaufen. Wir werden auch die Vor- und Nachteile jeder Technik untersuchen, um zu unterscheiden, welche in welcher Situation geeignet ist.

Alle Zeilen einer MySQL-Tabelle durchlaufen

Wir werden verschiedene Ansätze kennenlernen, mit denen wir alle Zeilen einer MySQL-Tabelle durchlaufen können, um Daten aus einer bestimmten oder mehreren Tabellen zu lesen/einzufügen. Lassen Sie uns jeden von ihnen anhand eines Codebeispiels lernen.

Zu diesem Zweck haben wir zwei Tabellen: Mitarbeiter und die andere ist emp_performance. Die Tabelle Mitarbeiter hat als Attribute (auch Spaltennamen genannt) EMP_ID, FIRSTNAME, LASTNAME, GENDER und AGE.

Die Tabelle emp_performance hat die Felder PERFORM_ID, FIRSTNAME, LASTNAME und PERFORMANCE, wobei FIRSTNAME und LASTNAME dieselben sind wie die Tabelle employees.

Stellen Sie sich vor, wir müssten VORNAME und NACHNAME aus der Tabelle Mitarbeiter kopieren und in die Tabelle Arbeitnehmer_Leistung einfügen, um jeden Monat die LEISTUNG für jeden Mitarbeiter zu berechnen.

Es muss eine Möglichkeit geben, die notwendigen Werte aus der Tabelle Mitarbeiter zu AUSWÄHLEN, in die Tabelle Arbeitnehmer_Leistung EINFÜGEN und später mit der LEISTUNG-Berechnung fortfahren. Sie können auch Mitarbeiter und Emp_Leistungen erstellen, um mit uns fortzufahren; der Code ist unten angegeben.

Beispielcode:

#create an `employees` table
CREATE TABLE employees (
  EMP_ID INT NOT NULL AUTO_INCREMENT,
  FIRSTNAME VARCHAR(45) NOT NULL,
  LASTNAME VARCHAR(45) NOT NULL,
  GENDER VARCHAR(45) NOT NULL,
  AGE INT NOT NULL,
  PRIMARY KEY (EMP_ID));

#insert data
INSERT INTO employees (FIRSTNAME, LASTNAME, GENDER, AGE) VALUES
('Mehvish','Ashiq', 'Female', 30),
('Thomas', 'Christopher', 'Male', 22),
('John', 'Daniel', 'Male', 34),
('Saira', 'James', 'Female', 27);

#create a `emp_performance` table
CREATE TABLE emp_performance (
  PERFORM_ID INT NOT NULL AUTO_INCREMENT,
  FIRSTNAME VARCHAR(45) NOT NULL,
  LASTNAME VARCHAR(45) NOT NULL,
  PERFORMANCE  VARCHAR(45) NULL,
  PRIMARY KEY (PERFORM_ID));

Wir können WHILE und CURSOR in einer gespeicherten Prozedur verwenden, um alle Zeilen der employees-Tabelle und INSERT in die emp_performance-Tabelle zu durchlaufen.

Verwenden Sie eine WHILE-Schleife in einer gespeicherten Prozedur, um alle Zeilen einer MySQL-Tabelle zu durchlaufen

Jetzt haben wir unsere Tische bereit. Wir können also die folgende Prozedur schreiben und ausführen, um den VORNAMEN und den NACHNAMEN aus der Tabelle Mitarbeiter zu AUSWÄHLEN und in die Tabelle Arbeitnehmer_Leistung zu EINFÜGEN.

Beispielcode:

DROP PROCEDURE IF EXISTS CALCPERFORMANCE;
DELIMITER ;;

CREATE PROCEDURE CALCPERFORMANCE()
BEGIN
DECLARE length INT DEFAULT 0;
DECLARE counter INT DEFAULT 0;
SELECT COUNT(*) FROM employees INTO length;
SET counter=0;
WHILE counter<length DO
  INSERT INTO emp_performance(FIRSTNAME, LASTNAME)
  SELECT FIRSTNAME, LASTNAME FROM employees LIMIT counter,1;
  SET counter = counter + 1;
END WHILE;
End;
;;

DELIMITER ;
CALL CALCPERFORMANCE();

Verwenden Sie nach dem Einfügen der Datensätze den Befehl SELECT, um die Ausgabe von emp_performance anzuzeigen.

SELECT * from emp_performance;

Ausgabe (emp_performance-Tabelle mit WHILE-Schleife):

Sobald Sie die gespeicherte Prozedur CALCPERFORMANCE ausführen, werden nur PERFORM_ID, FIRSTNAME und LASTNAME ausgefüllt.

Code-Erklärung mit Zeilennummern

1. Zeile 1 löscht alle bereits erstellten Prozeduren mit dem Namen CALCPERFORMANCE.
2. Zeile 2 ändert den Standardwert Trennzeichen in ;;. Auf diese Weise führt das SQL nicht jede Zeile aus, da wir versuchen, eine Prozedur zu schreiben, um eine bestimmte Aufgabe zu erfüllen.
3. Zeile 4 erstellt die Prozedur unter Verwendung des bereitgestellten Prozedurnamens.
4. Alle notwendigen Anweisungen für dieses Verfahren würden zwischen Zeile 5 und 15 geschrieben werden.
5. Zeile 5 beginnt mit der Prozedur.
6. Zeile 6 und 7 deklarieren eine Variable namens length und counter vom Typ INT, deren DEFAULT-Wert 0 bzw. 0 ist.
7. In Zeile 8 WÄHLEN wir die Anzahl aus der Tabelle Mitarbeiter aus und weisen diesen Wert der Variablen Länge zu.
8. Zeile 9 setzt den Zähler auf 0.
9. Wir haben eine WHILE-Schleife von den Zeilen 10-14, die den FIRSTNAME und LASTNAME aus der employees-Tabelle SELECT und INSERT in die emp_performance-Tabelle. Denken Sie daran, dass bei jeder Iteration nur ein Datensatz ausgewählt und eingefügt wird.
10. Zeile 18 setzt das Trennzeichen wieder auf seinen Standardwert zurück, der ; ist.
11. Zeile 19 ruft die Prozedur auf.

Ebenso können wir CURSOR verwenden, um alle Tabellenzeilen in MySQL zu durchlaufen. Sehen wir uns den folgenden Abschnitt an.

Verwenden Sie CURSOR in einer gespeicherten Prozedur, um alle Zeilen der MySQL-Tabelle zu durchlaufen

Wir verwenden einen CURSOR, um die Ergebnismenge in einer gespeicherten Prozedur zu behandeln. Es lässt uns eine Reihe von Datensätzen (Zeilen) durchlaufen, die von einer Abfrage zurückgegeben werden, und jede Zeile einzeln verarbeiten.

Man muss die folgenden Eigenschaften von CURSOR haben, während man es benutzt.

1. Der CURSOR ist empfindlich; es ist unnötig, dass der Server auch die Kopie der Ergebnistabelle erstellt.
2. Der CURSOR ist nicht aktualisierbar, da er schreibgeschützt ist.
3. Der CURSOR ist nicht scrollbar. Wir können es nur in eine Richtung durchlaufen, ohne Datensätze (Zeilen) in einer Ergebnismenge zu überspringen und zu springen.

Beispielcode:

DROP PROCEDURE IF EXISTS cursor_CALCPERFORMANCE;
DELIMITER ;;

CREATE PROCEDURE cursor_CALCPERFORMANCE()
BEGIN
DECLARE cursor_FIRSTNAME VARCHAR(45) DEFAULT "";
DECLARE cursor_LASTNAME VARCHAR(45) DEFAULT "";
DECLARE done INT DEFAULT FALSE;
DECLARE cursor_e CURSOR FOR SELECT FIRSTNAME,LASTNAME FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cursor_e;
read_loop: LOOP
  FETCH cursor_e INTO cursor_FIRSTNAME, cursor_LASTNAME;
  IF done THEN
    LEAVE read_loop;
  END IF;
  INSERT INTO emp_performance (FIRSTNAME,LASTNAME)
  VALUES (cursor_FIRSTNAME, cursor_LASTNAME);
END LOOP;
CLOSE cursor_e;
END;
;;

DELIMITER ;
CALL cursor_CALCPERFORMANCE();

Verwenden Sie nach dem Einfügen der Datensätze den Befehl SELECT, um die Ausgabe von emp_performance anzuzeigen.

SELECT * from emp_performance;

Ausgabe (Tabelle emp_performance mit CURSOR):

Code-Erklärung mit Zeilennummern

1. Zeile 1 löscht die bestehende gleichnamige Prozedur.
2. Zeile 2 ändert das Trennzeichen in ;;.
3. Zeile 4 erstellt die Prozedur unter Verwendung des angegebenen Prozedurnamens.
4. Alle erforderlichen Anweisungen werden zwischen Zeile 5 und 21 geschrieben.
5. Zeile 5 beginnt mit der Prozedur.
6. Zeile 6, 7 und 8 deklariert eine Variable namens cursor_FIRSTNAME, cursor_LASTNAME und done vom Typ VARCHAR(45), VARCHAR(45) und INT, deren DEFAULT-Werte sind "", "" bzw. FALSE.
7. Zeile 9 deklariert einen CURSOR, der der SELECT-Anweisung zugeordnet ist.
8. Zeile 10 deklariert den Handler NOT FOUND, wo die Variable finished verwendet wird, um anzuzeigen, dass der CURSOR das Ende der Ergebnismenge erreicht hat.
9. Zeile 11 öffnet den CURSOR.
10. In Zeile 12-19 iterieren wir über die Liste von FIRSTNAME und LASTNAME bis INSERT in die Tabelle emp_performance.
11. Zeile 20 beendet den CURSOR, während Zeile 21 die gespeicherte Prozedur beendet.
12. Zeile 24 setzt das Trennzeichen wieder auf seinen Standard zurück, der ; ist.
13. Zeile 25 ruft die Prozedur auf.

Vergleich der Verwendung von WHILE und CURSOR

Wir müssen die Vor- und Nachteile jedes Ansatzes kennen, wenn wir mehrere Möglichkeiten haben, eine Sache zu erreichen.

Vor- und Nachteile der WHILE-Schleife:

Vor- und Nachteile des CURSOR: