Detaillierte Erläuterung der gespeicherten Prozeduren und Funktionen von MySQL

1 Gespeicherte Prozedur

1.1 Was ist eine gespeicherte Prozedur?

Eine gespeicherte Prozedur ist eine Reihe von SQL-Anweisungen zum Ausführen einer bestimmten Funktion. Im Wesentlichen handelt es sich dabei um ein in der Datenbank gespeichertes Codestück. Es kann aus deklarativen SQL-Anweisungen (wie CREATE, UPDATE, SELECT usw.) und prozeduralen SQL-Anweisungen (wie IF...THEN...ELSE-Steuerstrukturanweisungen) bestehen. Die Idee gespeicherter Prozeduren ist sehr einfach: Sie besteht darin, Codes auf der Ebene der Datenbank-SQL-Sprache zu kapseln und wiederzuverwenden.

1.2 Vor- und Nachteile von gespeicherten Prozeduren

Vorteil:

1. Es kann die Funktionalität und Flexibilität der SQL-Sprache verbessern. Die gespeicherte Prozedur kann in einer Prozesssteuerungssprache geschrieben werden, die über eine hohe Flexibilität verfügt und komplexe Beurteilungen und komplexere Berechnungen durchführen kann.

2. Gute Kapselung Nachdem eine gespeicherte Prozedur erstellt wurde, kann sie im Programm mehrmals aufgerufen werden, ohne dass die SQL-Anweisung, die die gespeicherte Prozedur schreibt, neu geschrieben werden muss.

3. Nachdem eine gespeicherte Prozedur mit hoher Leistung einmal ausgeführt wurde, befindet sich ihr Ausführungsplan im Cache-Speicher. Für nachfolgende Vorgänge muss zur Ausführung nur der kompilierte Binärcode aus dem Cache-Speicher aufgerufen werden, wodurch die Systemleistung verbessert wird.

Mangel:

Gespeicherte Prozeduren werden häufig an bestimmte Datenbanken angepasst, da sie unterschiedliche Programmiersprachen unterstützen. Beim Wechsel auf ein Datenbanksystem eines anderen Herstellers muss die ursprüngliche gespeicherte Prozedur neu geschrieben werden.

1.3 Erstellen einer gespeicherten Prozedur

1.3.1 TRENNUNGSZEICHEN

In SQL verwendet der Server standardmäßig ein Semikolon als Endezeichen einer SQL-Anweisung. Beim Erstellen einer gespeicherten Prozedur kann der gespeicherte Prozedurkörper jedoch mehrere SQL-Anweisungen enthalten. Wenn diese SQL-Anweisungen immer noch ein Semikolon als Anweisungsabschluss verwenden, verwendet der Server das Semikolon bei der ersten SQL-Anweisung als Endezeichen des gesamten Programms und verarbeitet die nachfolgenden SQL-Anweisungen nicht mehr.
Um dieses Problem zu lösen, wird normalerweise der Befehl DELIMITER verwendet, um das Endzeichen der SQL-Anweisung vorübergehend in andere Symbole zu ändern.

DELIMITER-Syntaxformat:

TRENNUNGSZEICHEN $$

$$ ist ein benutzerdefiniertes Abschlusszeichen, normalerweise kann dieses Symbol ein Sonderzeichen sein. Vermeiden Sie auch die Verwendung des Backslashs, da es sich dabei um ein Escape-Zeichen handelt.
Wenn Sie wieder zum standardmäßigen Semikolon als Endezeichen wechseln möchten, geben Sie einfach die folgende SQL-Anweisung in die Befehlszeile ein.

TRENNUNGSZEICHEN ;

1.3.2 Erstellen gespeicherter Prozeduren

Verwenden Sie in MySQL die Anweisung CREATE PROCEDURE um eine gespeicherte Prozedur zu erstellen.

PROZEDUR ERSTELLEN p_name([prozedurparameter[,...]])
Routinekörper

Das Syntaxformat des Syntaxelements „proc_parameter“ ist:

[IN|OUT|INOUT]Parametername Typ

1. „p_name“ wird verwendet, um den Namen der gespeicherten Prozedur anzugeben.

2. „proc_parameter“ wird verwendet, um die Parameterliste in der gespeicherten Prozedur anzugeben. Das Syntaxelement „parame_name“ ist der Parametername und „type“ ist der Parametertyp (der Typ kann jeder gültige Datentyp in MySQL sein). Gespeicherte MySQL-Prozeduren unterstützen drei Arten von Parametern, nämlich Eingabeparameter IN, Ausgabeparameter OUT und Eingabe- und Ausgabeparameter INOUT. Eingabeparameter ermöglichen die Übergabe von Daten an eine gespeicherte Prozedur; Ausgabeparameter werden für ein Operationsergebnis verwendet, das eine gespeicherte Prozedur zurückgeben muss; Eingabe- und Ausgabeparameter können sowohl als Eingabeparameter als auch als Ausgabeergebnisse dienen.
Die Parameternamen dürfen nicht mit den Spaltennamen in der Tabelle identisch sein. Andernfalls wird zwar keine Fehlermeldung zurückgegeben, aber die SQL-Anweisung in der gespeicherten Prozedur behandelt den Parameternamen als Spaltennamen, was zu unvorhersehbaren Fehlern führt.

3. Das Syntaxelement „rountine_body“ stellt den Hauptteil der gespeicherten Prozedur dar, auch als Hauptteil der gespeicherten Prozedur bezeichnet, der das auszuführende SQL enthält. Der Prozedurkörper beginnt mit dem Schlüsselwort BEGIN und endet mit dem Schlüsselwort END. Wenn nur eine SQL-Anweisung vorhanden ist, kann das BEGIN….END-Zeichen ignoriert werden.

1.3.3 Lokale Variablen

Im Hauptteil der gespeicherten Prozedur können lokale Variablen deklariert werden, um temporäre Ergebnisse im Hauptteil der Prozedur zu speichern. In MySQL wird die DECLARE-Anweisung verwendet, um lokale Variablen zu deklarieren.

DECLARE var_name Typ [STANDARDwert]

Mit „var_name“ wird der Name der lokalen Variable angegeben; mit „type“ wird der Typ der Variable deklariert; mit „DEFAULT“ wird der Standardwert angegeben, der NULL ist, wenn er nicht angegeben wird.

Hinweis: Lokale Variablen können nur im BEGIN...END-Anweisungsblock des gespeicherten Prozedurhauptteils verwendet werden; lokale Variablen müssen am Anfang des gespeicherten Prozedurhauptteils deklariert werden; der Gültigkeitsbereich lokaler Variablen ist auf den BEGIN...END-Anweisungsblock beschränkt, in dem sie deklariert sind, und Anweisungen in anderen Anweisungsblöcken können sie nicht verwenden.

1.3.4 Benutzervariablen

Benutzervariablen beginnen normalerweise mit @.

Hinweis: Der Missbrauch von Benutzervariablen kann dazu führen, dass Ihr Programm schwer verständlich und verwaltebar ist.

1.3.5 SET-Anweisung

In MySQL lautet das Format zum Zuweisen von Werten zu lokalen Variablen über die SET-Anweisung:

SET var_name = Ausdruck[,var_name2 = Ausdruck]....

1.3.6 SELECT ... INTO-Anweisung

In MySQL können Sie mit der Anweisung SELECT...INTO die Werte der ausgewählten Spalten in lokalen Variablen speichern. Das Format ist:

SELECT Spaltenname[,..] INTO Variablenname[,....] Tabellenausdruck

Mit „col_name“ wird der Spaltenname angegeben; mit „var_name“ wird der zuzuweisende Variablenname angegeben; „table_expr“ stellt den Teil nach FROM in der SELECT-Anweisung dar.

Hinweis: Der von der SELECT...INTO-Anweisung zurückgegebene Ergebnissatz kann nur eine Datenzeile enthalten.

1.3.7 Anweisungen zur Flusssteuerung

Bedingte Anweisungen

if-then-else-Anweisung :

mysql > TRENNUNGSZEICHEN && 
mysql > PROZEDUR ERSTELLEN proc2(IN Parameter int) 
 -> beginnen 
 -> deklariere var int; 
 -> setze var=parameter+1; 
 -> wenn var=0 dann 
 -> in t-Werte einfügen (17); 
 -> Ende, wenn; 
 -> wenn Parameter=0 dann 
 -> aktualisiere t, setze s1=s1+1; 
 -> sonst 
 -> aktualisiere t, setze s1=s1+2; 
 -> Ende, wenn; 
 -> Ende; 
 -> && 
mysql > TRENNUNGSZEICHEN; 

Fallanweisung:

mysql > TRENNUNGSZEICHEN && 
mysql > PROZEDUR ERSTELLEN proc3 (im Parameter int) 
 -> beginnen 
 -> deklariere var int; 
 -> setze var=parameter+1; 
 -> Fall var 
 -> wenn 0 dann 
 -> in t-Werte einfügen (17); 
 -> wenn 1 dann 
 -> in t-Werte einfügen (18); 
 -> sonst 
 -> in t-Werte einfügen (19); 
 -> Fall beenden; 
 -> Ende; 
 -> && 
mysql > TRENNUNGSZEICHEN;

Schleifenanweisungen
während ···· Ende während:

mysql > TRENNUNGSZEICHEN && 
mysql > PROZEDUR ERSTELLEN proc4() 
 -> beginnen 
 -> deklariere var int; 
 -> setze var=0; 
 -> während var<6 
 -> in t-Werte (var) einfügen; 
 -> setze var=var+1; 
 -> Ende während; 
 -> Ende; 
 -> && 
mysql > TRENNUNGSZEICHEN;

wiederholen···· Wiederholung beenden:

Es prüft das Ergebnis nach der Durchführung der Operation, während es vor der Durchführung der Operation prüft.

mysql > TRENNUNGSZEICHEN && 
mysql > PROZEDUR ERSTELLEN proc5 () 
 -> beginnen 
 -> deklariere v int; 
 -> setze v=0; 
 -> wiederholen 
 -> in t-Werte einfügen (v); 
 -> setze v=v+1; 
 -> bis v>=5 
 -> Wiederholung beenden; 
 -> Ende; 
 -> && 
mysql > TRENNUNGSZEICHEN;

wiederholen
 --Schleifenkörper bis zum Ende der Schleifenbedingung wiederholen;

Schleife ·····Endschleife:

Die Schleife erfordert keine Anfangsbedingung, was der while-Schleife ähnelt. Wie die repeat-Schleife erfordert sie keine Endbedingung. Die leave-Anweisung wird verwendet, um die Schleife zu verlassen.

mysql > TRENNUNGSZEICHEN && 
mysql > PROZEDUR ERSTELLEN proc6 () 
 -> beginnen 
 -> deklariere v int; 
 -> setze v=0; 
 -> LOOP_LABLE:Schleife 
 -> in t-Werte einfügen (v); 
 -> setze v=v+1; 
 -> wenn v >=5 dann 
 -> LOOP_LABLE verlassen; 
 -> Ende, wenn; 
 -> Schleife beenden; 
 -> Ende; 
 -> && 
mysql > TRENNUNGSZEICHEN;

ITERATE-Iteration:

mysql > TRENNUNGSZEICHEN && 
mysql > PROZEDUR ERSTELLEN proc10 () 
 -> beginnen 
 -> deklariere v int; 
 -> setze v=0; 
 -> LOOP_LABLE:Schleife 
 -> wenn v=3 dann 
 -> setze v=v+1; 
 -> LOOP_LABLE iterieren; 
 -> Ende, wenn; 
 -> in t-Werte einfügen (v); 
 -> setze v=v+1; 
 -> wenn v>=5 dann 
 -> LOOP_LABLE verlassen; 
 -> Ende, wenn; 
 -> Schleife beenden; 
 -> Ende; 
 -> && 
mysql > TRENNUNGSZEICHEN;

1.3.8 Cursor

Der Cursor in MySQL kann als iterierbares Objekt verstanden werden (ähnlich wie iterierbare Objekte wie Listen und Wörterbücher in Python). Er kann verwendet werden, um den Ergebnissatz einer Select-Anweisung zu speichern. Dieser Ergebnissatz kann mehrere Datenzeilen enthalten, sodass wir eine iterative Methode verwenden können, um nacheinander jede Datenzeile vom Cursor abzurufen.

Funktionen des MySQL-Cursors:
1. Nur lesen: Daten in der Basistabelle können nicht über den Cursor aktualisiert werden.
2. Nicht scrollbar: Zeilen können nur in der durch die Select-Anweisung festgelegten Reihenfolge abgerufen werden. Es ist nicht möglich, Zeilen in umgekehrter Reihenfolge abzurufen. Darüber hinaus können Sie keine Zeilen überspringen oder zu einer bestimmten Zeile im Ergebnissatz springen.
3. Sensitiv: Es gibt zwei Arten von Cursorn: sensible und unempfindliche Cursor. Sensible Cursor zeigen auf die tatsächlichen Daten, und insensitive Cursor verwenden eine temporäre Kopie der Daten. Ein sensibler Cursor wird schneller ausgeführt als ein unempfindlicher Cursor, da kein temporäres Kopieren von Daten erforderlich ist. MySQL-Cursor sind empfindlich.

1. Deklarieren Sie einen Cursor

Die Cursordeklaration muss nach der Variablendeklaration erfolgen. Wenn Sie vor einer Variablendeklaration einen Cursor deklarieren, gibt MySQL einen Fehler aus. Ein Cursor muss immer mit einer Select-Anweisung verknüpft sein.

Deklarieren Sie den Cursor_Namen für die Select_Statement-Anweisung.

2. Öffnen Sie den Cursor

Mit der Anweisung open können Sie den Cursor öffnen. Erst wenn der Cursor geöffnet ist, können Daten gelesen werden.

öffne Cursorname;

3. Den Cursor lesen

Verwenden Sie die Fetch-Anweisung, um eine Datenzeile abzurufen, auf die ein Cursor zeigt, und bewegen Sie den Cursor zur nächsten Zeile im Ergebnissatz.

Cursorname in Varname holen;

4. Schließen Sie den Cursor

Verwenden Sie die Anweisung „close“, um den Cursor zu schließen.

Cursorname schließen;

Wenn ein Cursor nicht mehr verwendet wird, sollte er geschlossen werden. Wenn Sie MySQL-Cursor verwenden, müssen Sie auch einen Notfound-Handler deklarieren, um den Fall zu behandeln, dass der Cursor keine Zeilen findet. Denn bei jedem Aufruf der Fetch-Anweisung versucht der Cursor, nacheinander alle Datenzeilen im Ergebnissatz zu lesen. Wenn der Cursor das Ende des Ergebnissatzes erreicht, können keine Daten abgerufen werden und es wird eine Bedingung generiert. Der Handler wird verwendet, um diese Situation zu bewältigen.

Deklarieren Sie den Continue-Handler für nicht gefundenen Settyp = 1;

Typ ist eine Variable, die angibt, dass der Cursor das Ende des Ergebnissatzes erreicht hat.

Trennzeichen $$
Erstellen Sie das PROZEDUR phoneDeal()
BEGINNEN
 DECLARE id varchar(64); -- Ich würde
 DECLARE phone1 varchar(16); -- Telefon
 DECLARE password1 varchar(32); -- Passwort DECLARE name1 varchar(64); -- ID
 --Durchlaufdaten-Endmarkierung DECLARE done INT DEFAULT FALSE;
 - Cursor DECLARE cur_account CURSOR FÜR „Telefon, Passwort, Name aus account_temp auswählen“;
 - Binden Sie das Ende-Flag an den Cursor. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
 - Öffnen Sie den Cursor OPEN cur_account; 
 -- Leseschleife durchlaufen: SCHLEIFE
 -- Werte aus mehreren Feldern abrufen FETCH NEXT from cur_account INTO phone1,password1,name1;
 WENN erledigt, DANN
 LEAVE read_loop;
 ENDE, WENN;
 
 - Die Operation, die Sie ausführen möchten, fügen Sie in das Konto (ID, Telefon, Passwort, Name) den Wert (UUID(), Telefon1, Passwort1, CONCAT (Name1, „Passwort“) ein.
 ENDE DER SCHLEIFE;
 
 -- Schließen Sie den Cursor. CLOSE cur_account;
ENDE $$

1.3.7 Aufrufen einer gespeicherten Prozedur

Aufrufen einer gespeicherten Prozedur mit der Call-Anweisung

call sp_name[(übergebene Parameter)];

1.3.8 Löschen einer gespeicherten Prozedur

Verwenden der Drop-Anweisung zum Löschen einer gespeicherten Prozedur

DROP-PROZEDUR sp_name

2 Speicherfunktionen

2.1 Was ist eine gespeicherte Funktion?

Gespeicherte Funktionen sind wie gespeicherte Prozeduren Codeblöcke, die aus SQL und Anweisungen bestehen.
Eine gespeicherte Funktion kann keine Eingabeparameter haben und kann direkt ohne eine Aufrufanweisung aufgerufen werden und muss eine RETURN-Anweisung enthalten.

2.2 Erstellen einer gespeicherten Funktion

Verwenden Sie die Anweisung CREATE FUNCTION in MySQL, um Folgendes zu erstellen:

FUNKTION ERSTELLEN fun_name (par_name Typ[,...])
RETURNS-Typ
[Eigenschaften] 
Spaß_Körper

Dabei ist fun_name der Funktionsname. Der Name ist eindeutig und darf nicht mit dem einer gespeicherten Prozedur identisch sein. par_name ist der angegebene Parameter, type ist der Parametertyp; die RETURNS-Klausel wird verwendet, um den Rückgabewert und den Rückgabewerttyp zu deklarieren. fun_body ist der Funktionskörper. Alle SQL-Anweisungen in der gespeicherten Prozedur können auch in der gespeicherten Funktion verwendet werden. Der gespeicherte Funktionskörper muss jedoch eine RETURN-Anweisung enthalten.
Eigenschaften gibt die Merkmale der gespeicherten Prozedur an und hat die folgenden Werte:

• LANGUAGE SQL: Gibt an, dass der Routine_Body-Teil aus SQL-Anweisungen besteht. Die vom aktuellen System unterstützte Sprache ist SQL. SQL ist der einzige Wert der LANGUAGE-Eigenschaft.
• [NICHT] DETERMINISTISCH: Gibt an, ob das Ergebnis der Ausführung der gespeicherten Prozedur bestimmt ist. DETERMINISTIC bedeutet, dass das Ergebnis deterministisch ist. Bei jeder Ausführung der gespeicherten Prozedur erzeugt dieselbe Eingabe dieselbe Ausgabe. NOT DETERMINISTIC bedeutet, dass das Ergebnis unsicher ist. Dieselbe Eingabe kann unterschiedliche Ausgaben erzeugen. Wenn kein Wert angegeben ist, ist der Standardwert NOT DETERMINISTIC.
• [ENTHÄLT SQL|KEIN SQL|LEST SQL-DATEN|ÄNDERT SQL-DATEN]: Gibt Einschränkungen für die Verwendung von SQL-Anweisungen durch die Subroutine an. CONTAINS SQL gibt an, dass die Subroutine SQL-Anweisungen enthält, jedoch keine Anweisungen zum Lesen und Schreiben von Daten. NO SQL gibt an, dass die Subroutine keine SQL-Anweisungen enthält. READS SQL DATA gibt an, dass die Subroutine Anweisungen zum Lesen von Daten enthält. MODIFIES SQL DATA gibt an, dass die Subroutine mit dem Tabellennamen Anweisungen zum Schreiben von Daten enthält. Standardmäßig ist CONTAINS SQL angegeben.
• SQL SECURITY[DEFINER|INVOKER]: Gibt an, wer die Berechtigung zur Ausführung hat. DEFINER bedeutet, dass es nur vom Definierer ausgeführt werden kann. INVOKER bedeutet, dass der Anrufer mit UFIDA-Berechtigungen es ausführen kann. Standardmäßig wird das System als DEFINER bezeichnet.
• COMMENT „Zeichenfolge“: Kommentarinformationen zur Beschreibung der gespeicherten Prozedur oder Funktion.

Trennzeichen $$
Funktion „getAnimalName(animalId int)“ erstellen. Gibt VARCHAR(50) zurück.
DETERMINISTISCH
beginnen
 Deklarationsname VARCHAR (50);
 Name festlegen = (Name des Tieres auswählen, wobei ID = Tier-ID);
 Rückkehr (Name);
Ende$$
Trennzeichen;

-- Rufen Sie select getAnimalName(4) auf.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.