MySQL-Cursor für gespeicherte Prozeduren

Detaillierte Erklärung zu gespeicherten MySQL-Prozeduren und Cursorn

In der MySQL-Datenbank ist eine gespeicherte Prozedur ein Satz vorkompilierter SQL-Anweisungen, die als Unterroutine in der Datenbank betrachtet werden können. Gespeicherte Prozeduren werden in der Regel zur Handhabung komplexer Geschäftslogik verwendet, um die Interaktion zwischen Anwendungen und Datenbanken zu vereinfachen und die Effizienz und Sicherheit der Datenverarbeitung zu verbessern. Ein Cursor ist ein Mechanismus zum Verarbeiten von Abfrageergebnismengen in gespeicherten Prozeduren.

In diesem Artikel werden die Verwendung und Eigenschaften von gespeicherten MySQL-Prozeduren und -Cursoren ausführlich vorgestellt.

1. Gespeicherte Prozedur

1.1 Syntaxstruktur der gespeicherten Prozedur

Im Folgenden ist die grundlegende Syntaxstruktur von MySQL gespeichert Prozedur:

CREATE PROCEDURE procedure_name [ (parameter_list) ] BEGIN
    -- 存储过程的逻辑实现
END;

Unter ihnen

• procedure_name: der Name der gespeicherten Prozedur;
• parameter_list: die Parameterliste der Gespeicherte Prozedur, die 0 oder mehrere Parameter enthalten kann. Jeder Parameter besteht aus Parametername und Parametertyp.
• Zwischen BEGIN und END befindet sich der logische Implementierungsteil der gespeicherten Prozedur.

Definieren Sie beispielsweise eine einfache gespeicherte Prozedur, um alle Datensätze in der Studententabelle (Student) abzufragen und Folgendes zurückzugeben:

CREATE PROCEDURE get_all_students()
BEGIN
    SELECT * FROM student;
END;

1.2 Parameter der gespeicherten Prozedur Übergabe

Gespeicherte Prozeduren können Parameter vordefinieren, die beim Aufruf der gespeicherten Prozedur übergeben werden können. Gespeicherte MySQL-Prozeduren unterstützen drei Parameterübergabemethoden: IN, OUT und INOUT.

• IN: Als Eingabeparameter wird der Parameterwert beim Aufruf der gespeicherten Prozedur übergeben.
• OUT: Als Ausgabeparameter wird das Ergebnis berechnet und innerhalb der gespeicherten Prozedur zurückgegeben.
• INOUT: Es ist sowohl ein Eingabeparameter als auch ein Ausgabeparameter. Beim Aufruf einer gespeicherten Prozedur übergeben Sie den Parameterwert und erhalten das berechnete Ergebnis der gespeicherten Prozedur.

Das Folgende ist ein Beispiel für eine gespeicherte Prozedur, die Parameterübergabe verwendet:

CREATE PROCEDURE add_two_numbers(IN num1 INT, IN num2 INT, OUT result INT)
BEGIN
    SET result = num1 + num2;
END;

Im obigen Beispiel empfängt die gespeicherte Prozedur add_two_numbers zwei Eingabeparameter num1 und num2 , und Das Ergebnis ihrer Addition wird dem Ausgabeparameter result zugewiesen.

Sie können die folgenden Anweisungen verwenden, um diese gespeicherte Prozedur aufzurufen:

CALL add_two_numbers(2, 3, @result);
SELECT @result; -- 输出 5

1.3 Flusskontrolle gespeicherter Prozeduren

MySQL-gespeicherte Prozeduren unterstützen die allgemeine Flusskontrolle Anweisungen wie IF, CASE, WHILE, LOOP und LEAVE usw. Verwenden Sie diese Anweisungen, um komplexe Logiksteuerung und Geschäftsverarbeitung zu implementieren.

Das Folgende ist ein Beispiel für eine gespeicherte Prozedur mit IF- und WHILE-Anweisungen:

CREATE PROCEDURE calc_factorial(IN num INT, OUT result BIGINT)
BEGIN
    SET result = 1;
    WHILE num > 1 DO
        SET result = result * num;
        SET num = num - 1;
    END WHILE;
END;

Wenn im obigen Beispiel der Eingabeparameter num größer als 1 ist, wird eine WHILE-Schleife ausgeführt wird zur Berechnung des num-Fakultätswerts verwendet und speichert das Ergebnis im Ausgabeparameter result.

Sie können die folgende Anweisung verwenden, um diese gespeicherte Prozedur aufzurufen:

CALL calc_factorial(6, @result);
SELECT @result; -- 输出 720

2. Cursor

2.1 Das Grundkonzept von Cursor#🎜 🎜#

In gespeicherten MySQL-Prozeduren sind Cursor der Mechanismus, der zum Durchlaufen der Abfrageergebnismenge in der gespeicherten Prozedur verwendet wird. Der Cursor verfolgt die aktuelle Zeile im Abfrageergebnissatz und bewegt sich Zeile für Zeile nach Bedarf, um die Daten in dieser Zeile zu lesen.

Die Verwendung von Cursorn zum Deklarieren, Öffnen, Schließen und Bedienen von Cursorn erfordert die folgenden vier Befehle:

DECLARE: einen Cursor deklarieren, den Namen des Cursors definieren, und Abfrage von Anweisungs- und Cursortypen usw.
• ÖFFNEN: Öffnen Sie den Cursor und speichern Sie den Abfrageergebnissatz in dem Puffer, auf den der Cursor zeigt.
• FETCH: Holen Sie sich die aktuelle Zeile, auf die der Cursor zeigt, und speichern Sie den Wert der aktuellen Zeile in der entsprechenden Variablen.
• CLOSE: Schließen Sie den Cursor und geben Sie den vom Cursor belegten Speicher frei.

Das Folgende ist ein Beispiel für eine gespeicherte Prozedur, die einen Cursor verwendet:

CREATE PROCEDURE get_all_students()
BEGIN
    DECLARE done INT DEFAULT FALSE; -- 定义游标是否结束的标志
    DECLARE s_id INT; -- 存储查询结果中的学生编号
    DECLARE s_name VARCHAR(255); -- 存储查询结果中的学生姓名
    DECLARE cursor_students CURSOR FOR SELECT id, name FROM student; -- 声明游标，查询表 student 中的所有数据
    -- 打开游标
    OPEN cursor_students;
    -- 遍历游标指向的结果集
    read_loop: LOOP
        -- 获取游标指向的当前行
        FETCH cursor_students INTO s_id, s_name;
        -- 如果游标到达结果集的末尾，则跳出循环
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 输出当前行的值
        SELECT s_id, s_name;
    END LOOP;
    -- 关闭游标
    CLOSE cursor_students;
END;

Im obigen Beispiel wird ein Cursor mit dem Namen „cursor_students“ für die Abfrage der Tabelle „Student“ deklariert alle Daten in . Verwenden Sie nach dem Öffnen des Cursors den Schleifenkörper read_loop, um die Abfrageergebnismenge zu durchlaufen, und verwenden Sie den Befehl FETCH, um den Wert der aktuellen Zeile abzurufen, ihn in den Variablen s_id und s_name zu speichern und an die Konsole auszugeben. Wenn der Cursor das Ende der Ergebnismenge erreicht, brechen Sie die Schleife ab. Wenn die Verwendung des Cursors beendet ist, müssen Sie den Befehl CLOSE verwenden, um den Cursor zu schließen.

2.2 Cursoreigenschaften und Anwendungsszenarien

Obwohl Cursor Entwicklern eine bequeme Möglichkeit bieten, Ergebnismengen abzufragen, erfordern sie die Verwendung von zusätzlichem Speicher und Ressourcen, sodass Sie dies tun müssen Achten Sie bei der Verwendung besonders auf die folgenden Probleme:

Cursoren beeinträchtigen die Leistung, insbesondere bei der Verarbeitung großer Datenmengen. Es wird nicht empfohlen, Cursor in großen Umgebungen oder Umgebungen mit hoher Parallelität zu verwenden. Anderen Methoden wie Unterabfragen und JOIN-Vorgängen sollte Vorrang eingeräumt werden.
• Cursoren können nur in gespeicherten Prozeduren und nicht direkt in SQL-Anweisungen verwendet werden.
• Cursoren müssen mit Vorsicht verwendet werden, denn wenn der Cursor nicht richtig geschlossen wird, belegt die MySQL-Datenbank viele Speicherressourcen und stürzt sogar ab.

Normalerweise eignen sich Cursor für die folgenden Szenarien:

Situationen, in denen komplexe Abfragelogik in einer gespeicherten Prozedur implementiert werden muss.
• Situationen, in denen große Datensätze stapelweise verarbeitet werden müssen.
• Die Abfrageergebnismenge muss Zeile für Zeile verarbeitet werden.

Zusammenfassung

In diesem Artikel werden hauptsächlich die Verwendung und Eigenschaften von gespeicherten Prozeduren und Cursorn in der MySQL-Datenbank vorgestellt. Gespeicherte Prozeduren können die Effizienz und Sicherheit der Interaktionen zwischen Anwendungen und Datenbanken verbessern, und Cursor können Abfrageergebnismengen problemlos durchlaufen. Beachten Sie jedoch, dass Sie bei der Verwendung von Cursorn vorsichtig sein müssen, um Speicherverluste und Leistungsprobleme zu vermeiden.

Referenz:

• MySQL :: MySQL 8.0 Referenzhandbuch :: 13.6.4.1 DECLARE Cursor-Anweisung
• MySQL :: MySQL 8.0 Referenzhandbuch :: 13.6.4.2 OPEN Cursor-Anweisung
• MySQL :: MySQL 8.0-Referenzhandbuch :: 13.6.4.3 FETCH-Cursor-Anweisung
• MySQL :: MySQL 8.0-Referenzhandbuch :: 13.6.4.4 CLOSE-Cursor-Anweisung

Das obige ist der detaillierte Inhalt vonMySQL-Cursor für gespeicherte Prozeduren. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!