MySQL 5.0 unterstützt gespeicherte Prozeduren seit seiner Veröffentlichung.
Eine gespeicherte Prozedur ist ein Datenbankobjekt, das komplexe Programme in der Datenbank speichert, damit sie von externen Programmen aufgerufen werden können.
Eine gespeicherte Prozedur ist eine Menge von SQL-Anweisungen, die dazu dient, eine bestimmte Funktion zu erfüllen. Sie wird kompiliert, erstellt und in der Datenbank gespeichert. Benutzer können sie aufrufen und ausführen, indem sie den Namen der gespeicherten Prozedur angeben und (bei Bedarf) Parameter übergeben.
Die Idee hinter gespeicherten Prozeduren ist einfach: Kapselung und Wiederverwendung von Code auf der SQL-Sprachebene der Datenbank.
Vorteile
- Gespeicherte Prozeduren können komplexe Geschäftslogik kapseln und verbergen.
- Gespeicherte Prozeduren können Werte zurückgeben und Parameter akzeptieren.
- Gespeicherte Prozeduren können nicht mit dem SELECT-Befehl ausgeführt werden, da sie Unterprogramme sind, im Gegensatz zu Views, Tabellen oder benutzerdefinierten Funktionen.
- Gespeicherte Prozeduren können für Datenvalidierung, Durchsetzung von Geschäftslogik und so weiter verwendet werden.
Nachteile
- Gespeicherte Prozeduren sind oft auf bestimmte Datenbanken zugeschnitten, weil sich die unterstützten Programmiersprachen unterscheiden. Beim Wechsel zu einem Datenbanksystem eines anderen Anbieters müssen vorhandene gespeicherte Prozeduren neu geschrieben werden.
- Die Leistungsoptimierung und das Schreiben gespeicherter Prozeduren sind durch verschiedene Datenbanksysteme eingeschränkt.
I. Erstellung und Aufruf gespeicherter Prozeduren
- Eine gespeicherte Prozedur ist ein benannter Codeblock, der dazu dient, eine bestimmte Funktion zu erfüllen.
- Erstellte gespeicherte Prozeduren werden im Datenwörterbuch der Datenbank gespeichert.
Erstellen einer gespeicherten Prozedur
Wichtige Syntax in gespeicherten MySQL-Prozeduren
Deklarieren Sie das Anweisungs-Trennzeichen; es kann angepasst werden:
DELIMITER $$ 或 DELIMITER //
Gespeicherte Prozedur deklarieren:
CREATE PROCEDURE demo_in_parameter(IN p_in int)
Start- und Endsymbole der gespeicherten Prozedur:
BEGIN .... END
Variablenzuweisung:
SET @p_in=1
Variablendefinition:
DECLARE l_int int unsigned default 4000000;
Gespeicherte Prozeduren und gespeicherte Funktionen in MySQL erstellen:
create procedure 存储过程名(参数)
Rumpf der gespeicherten Prozedur:
create function 存储函数名(参数)
Beispiel
Erstellen Sie eine Datenbank und sichern Sie eine Datentabelle für die Beispieloperation:
Das Folgende ist ein Beispiel für eine gespeicherte Prozedur, die alle Spiele löscht, an denen ein bestimmter Spieler teilgenommen hat:
Analyse:Standardmäßig ist eine gespeicherte Prozedur der Standarddatenbank zugeordnet. Wenn Sie die gespeicherte Prozedur unter einer bestimmten Datenbank erstellen möchten, stellen Sie dem Prozedurnamen den Datenbanknamen voran. Wenn Sie die Prozedur definieren, verwenden SieDELIMITER $$Befehl zum Ändern des Anweisungs-Trennzeichens von einem Semikolon;vorübergehend auf zwei$$, sodass im Prozedurkörper verwendete Semikolons direkt an den Server übergeben und nicht vom Client (wie mysql) interpretiert werden.
Aufruf der gespeicherten Prozedur:
call sp_name[(传参)];
Analyse:In der gespeicherten Prozedur wird eine Variable p_playerno, die einen Parameter erfordert, gesetzt. Beim Aufruf der gespeicherten Prozedur wird p_playerno über den Parameter der Wert 57 zugewiesen, und dann werden die SQL-Operationen in der gespeicherten Prozedur ausgeführt.
Rumpf der gespeicherten Prozedur
- Der Rumpf der gespeicherten Prozedur enthält die Anweisungen, die ausgeführt werden müssen, wenn die Prozedur aufgerufen wird, wie z. B. DML- und DDL-Anweisungen, IF-THEN-ELSE- und WHILE-DO-Anweisungen, DECLARE-Anweisungen zum Deklarieren von Variablen usw.
- Format des Prozedurrumpfs: beginnt mit BEGIN und endet mit END (kann verschachtelt werden)
BEGIN BEGIN BEGIN statements; END END END
Hinweis:Jeder verschachtelte Block und jede darin enthaltene Anweisung muss mit einem Semikolon enden. Der BEGIN-END-Block, der das Ende des Prozedurrumpfs markiert (auch als zusammengesetzte Anweisung bezeichnet), erfordert kein Semikolon.
Beschriftung von Anweisungsblöcken:
[begin_label:] BEGIN [statement_list] END [end_label]
Zum Beispiel:
Labels haben zwei Zwecke:
- 1. Verbesserung der Lesbarkeit des Codes
- 2. Bestimmte Anweisungen (z. B. LEAVE- und ITERATE-Anweisungen) erfordern Labels
II. Parameter von gespeicherten Prozeduren
MySQL-Parameter für gespeicherte Prozeduren werden in der Definition von gespeicherten Prozeduren verwendet. Es gibt drei Parametertypen: IN, OUT und INOUT, in der Form:
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
- IN-Eingabeparameter: zeigt an, dass der Aufrufer einen Wert an die Prozedur übergibt (der Eingabewert kann ein Literal oder eine Variable sein)
- OUT-Ausgabeparameter: zeigt an, dass die Prozedur einen Wert an den Aufrufer zurückgibt (kann mehrere Werte zurückgeben) (der Ausgabewert kann nur eine Variable sein)
- INOUT-Eingabe-/Ausgabeparameter: zeigt an, dass der Aufrufer einen Wert an die Prozedur übergibt und dass die Prozedur einen Wert an den Aufrufer zurückgibt (der Wert kann nur eine Variable sein)
1. IN-Eingabeparameter
Aus dem Obigen ist ersichtlich, dass p_in in der gespeicherten Prozedur geändert wird, aber dies beeinflusst nicht@p_in, den Wert des letzteren, weil ersteres eine lokale Variable und letzteres eine globale Variable ist.
2. OUT-Ausgabeparameter
3. INOUT-Eingabe-/Ausgabeparameter
Hinweis:
1. Wenn die Prozedur keine Parameter hat, müssen Sie trotzdem Klammern nach dem Prozedurnamen schreiben. Zum Beispiel:
CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……
2. Stellen Sie sicher, dass Parameternamen nicht mit Spaltennamen übereinstimmen; andernfalls wird der Parametername im Prozedurkörper als Spaltenname behandelt.
Vorschläge:
- Verwenden Sie IN-Parameter für Eingabewerte.
- Verwenden Sie OUT-Parameter für Rückgabewerte.
- Verwenden Sie INOUT-Parameter möglichst selten.
III. Variablen
1. Variablendefinition
Lokale Variablendeklarationen müssen am Anfang des Rumpfs der gespeicherten Prozedur stehen:
DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];
Hierbei ist Datentyp ein MySQL-Datentyp, wie z. B. INT, FLOAT, DATE, VARCHAR(length)
Zum Beispiel:
2. Variablenzuweisung
SET 变量名 = 表达式值 [,variable_name = expression ...]
3. Benutzervariablen
Verwenden von Benutzervariablen im MySQL-Client:
Verwenden von Benutzervariablen in gespeicherten Prozeduren
Übergabe von Benutzervariablen mit globalem Gültigkeitsbereich zwischen gespeicherten Prozeduren
Hinweis:
- 1. Benutzervariablennamen beginnen im Allgemeinen mit @
- 2. Übermäßige Verwendung von Benutzervariablen kann das Programm schwer verständlich und schwierig zu verwalten machen
IV. Kommentare
Gespeicherte Prozeduren in MySQL können zwei Kommentarstile verwenden
Zwei Bindestriche--: Dieser Stil wird im Allgemeinen für einzeilige Kommentare verwendet.
C-Stil: Wird im Allgemeinen für mehrzeilige Kommentare verwendet.
Zum Beispiel:
Aufrufen gespeicherter Prozeduren in MySQL
Verwenden Sie CALL, gefolgt von Ihrem Prozedurnamen und Klammern. Fügen Sie innerhalb der Klammern bei Bedarf Parameter hinzu. Parameter umfassen Eingabeparameter, Ausgabeparameter und Eingabe-/Ausgabeparameter. Für spezifische Aufrufmethoden siehe die obigen Beispiele.
Abfragen gespeicherter Prozeduren in MySQL
Wenn wir wissen möchten, welche Tabellen unter einer Datenbank existieren, verwenden wir normalerweiseshowtables;, um sie anzuzeigen. Wenn wir dann die gespeicherten Prozeduren unter einer Datenbank anzeigen möchten, können wir diese Methode dann auch verwenden? Die Antwort ist: Wir können die gespeicherten Prozeduren unter einer Datenbank anzeigen, aber auf eine andere Weise.
Wir können die folgende Anweisung verwenden, um abzufragen:
selectname from mysql.proc where db='数据库名'; 或者 selectroutine_name from information_schema.routines where routine_schema='数据库名'; 或者 showprocedure status where db='数据库名';
Wenn wir die Details einer bestimmten gespeicherten Prozedur wissen möchten, was sollten wir tun? Können wir auch DESCRIBE table_name verwenden, um sie genauso wie Tabellen anzuzeigen?
Die Antwort ist:Wir können die Details der gespeicherten Prozedur anzeigen, aber wir müssen eine andere Methode verwenden:
SHOW CREATE PROCEDURE 数据库.存储过程名;
um die Details der aktuellen gespeicherten Prozedur anzuzeigen.
Ändern gespeicherter Prozeduren in MySQL
ALTER PROCEDURE
Das Ändern einer zuvor angegebenen gespeicherten Prozedur, die mit CREATE PROCEDURE erstellt wurde, wirkt sich nicht auf zugehörige gespeicherte Prozeduren oder gespeicherte Funktionen aus.
Löschen gespeicherter Prozeduren in MySQL
Das Löschen einer gespeicherten Prozedur ist relativ einfach, genau wie das Löschen einer Tabelle:
DROPPROCEDURE
Löschen Sie eine oder mehrere gespeicherte Prozeduren aus der MySQL-Tabelle.
Kontrollanweisungen in gespeicherten MySQL-Prozeduren
(1). Gültigkeitsbereich von Variablen
Innere Variablen haben innerhalb ihres Gültigkeitsbereichs höhere Priorität. Wenn die Ausführung END erreicht, verschwinden innere Variablen. Zu diesem Zeitpunkt befinden sie sich bereits außerhalb ihres Gültigkeitsbereichs und die Variablen sind nicht mehr sichtbar, weil diese deklarierte Variable außerhalb der gespeicherten Prozedur nicht mehr gefunden werden kann. Sie können ihren Wert jedoch über einen OUT-Parameter speichern oder indem Sie ihren Wert einer Sitzungsvariablen zuweisen.
(2). Bedingte Anweisungen
1. if-then-else-Anweisungen
2. case-Anweisung:
(3). Schleifenanweisungen
1. while ···· end while
while 条件 do
--循环体
endwhile
2. repeat···· end repeat
Es prüft das Ergebnis nach der Ausführung der Operation, wohingegen while vor der Ausführung prüft.
repeat
--循环体
until 循环条件
end repeat;
3. loop ·····endloop
Die loop-Schleife erfordert keine Anfangsbedingung, ähnlich der while-Schleife, und wie die repeat-Schleife erfordert sie keine Endbedingung. Die leave-Anweisung wird verwendet, um die Schleife zu verlassen.
4. LABELS:
Labels können vor begin-, repeat-, while- oder loop-Anweisungen platziert werden. Anweisungslabels können nur vor gültigen Anweisungen verwendet werden. Sie können aus einer Schleife ausbrechen, wodurch die laufenden Anweisungen den letzten Schritt der Verbundanweisung erreichen.
(4). ITERATE Iteration
ITERATE startet eine Verbundanweisung neu, indem auf ihr Label verwiesen wird:
Referenzartikel:
https://www.cnblogs.com/geaozhang/p/6797357.html
http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html