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

CREATE [DEFINER = { user | CURRENT_USER }]  PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body:   Gültige SQL Routine -Anweisung [begin_label:] BEGIN   [statement_list]     …… END [end_label]

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:

mysql> create database db1; mysql> use db1; mysql> create table PLAYERS as select * from TENNIS.PLAYERS; mysql> create table MATCHES as select * from TENNIS.MATCHES;

Das Folgende ist ein Beispiel für eine gespeicherte Prozedur, die alle Spiele löscht, an denen ein bestimmter Spieler teilgenommen hat:

mysql> delimiter $$  # Ändern Sie vorübergehend das Anweisungs-Trennzeichen von einem Semikolon ; auf zwei $$ (kann angepasst werden) mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER) -> BEGIN ->   DELETE FROM MATCHES -> WHERE playerno = p_playerno; -> END$$ Query OK, 0 rows betroffen (0.01 sec) mysql> delimiter;  # Stellen Sie das Anweisungs-Trennzeichen wieder auf ein Semikolon ein

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[(传参)];
mysql> select * from MATCHES; +---------+--------+----------+-----+------+ | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | +---------+--------+----------+-----+------+ | 1 | 1 | 6 | 3 | 1 | | 7 | 1 | 57 | 3 | 0 | | 8 | 1 | 8 | 0 | 3 | | 9 | 2 | 27 | 3 | 2 | | 11 | 2 | 112 | 2 | 3 | +---------+--------+----------+-----+------+ 5 rows im Satz (0.00 Sek.) mysql> call delete_matches(57); Query OK, 1 Zeile betroffen (0.03 sec) mysql> select * from MATCHES; +---------+--------+----------+-----+------+ | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | +---------+--------+----------+-----+------+ | 1 | 1 | 6 | 3 | 1 | | 8 | 1 | 8 | 0 | 3 | | 9 | 2 | 27 | 3 | 2 | | 11 | 2 | 112 | 2 | 3 | +---------+--------+----------+-----+------+ 4 rows in Satz (0.00 sec)

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:

label1: BEGIN   label2: BEGIN     label3: BEGIN       Anweisungen;     END label3 ;   END label2; END label1

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

mysql> delimiter $$ mysql> create procedure in_param(in p_in int) -> begin ->   select p_in; ->   set p_in=2; -> select P_in; -> end$$ mysql> delimiter ; mysql> set @p_in=1; mysql> call in_param(@p_in); +------+ | p_in | +------+ | 1 | +------+ +------+ | P_in | +------+ | 2 | +------+ mysql> select @p_in; +-------+ | @p_in | +-------+ | 1 | +-------+

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

mysql> delimiter // mysql> create procedure out_param(out p_out int) -> begin -> select p_out; -> set p_out=2; -> select p_out; -> end -> // mysql> delimiter ; mysql> set @p_out=1; mysql> call out_param(@p_out); +-------+ | p_out | +-------+ | NULL | +-------+   # Da OUT ein Ausgabeparameter an den Aufrufer ist und keine Eingabeparameter akzeptiert, ist p_out in der gespeicherten Prozedur NULL +-------+ | p_out | +-------+ | 2 | +-------+ mysql> select @p_out; +--------+ | @p_out | +--------+ | 2 | +--------+   # Die gespeicherte Prozedur out_param wurde aufgerufen, Ausgabeparameter, wodurch der Wert der Variablen p_out geändert wird

3. INOUT-Eingabe-/Ausgabeparameter

mysql> delimiter $$ mysql> create procedure inout_param(inout p_inout int) -> begin -> select p_inout; -> set p_inout=2; -> select p_inout; -> end -> $$ mysql> delimiter ; mysql> set @p_inout=1; mysql> call inout_param(@p_inout); +---------+ | p_inout | +---------+ | 1 | +---------+ +---------+ | p_inout | +---------+ | 2 | +---------+ mysql> select @p_inout; +----------+ | @p_inout | +----------+ | 2 | +----------+ # Die gespeicherte Prozedur inout_param wurde aufgerufen, die Eingabeparameter entgegennimmt und auch Parameter ausgibt und dabei die Variable ändert

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:

DECLARE l_int int unsigned default 4000000; DECLARE l_numeric number(8,2) DEFAULT 9.95; DECLARE l_date date DEFAULT '1999-12-31'; DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59'; DECLARE l_varchar varchar(255) DEFAULT 'Dies wird nicht aufgefüllt';

2. Variablenzuweisung

SET 变量名 = 表达式值 [,variable_name = expression ...]

3. Benutzervariablen

Verwenden von Benutzervariablen im MySQL-Client:

mysql > SELECT 'Hello World' into @x; mysql > SELECT @x; +-------------+ | @x | +-------------+ | Hello World | +-------------+ mysql > SET @y='Goodbye Cruel World'; mysql > SELECT @y; +---------------------+ | @y | +---------------------+ | Goodbye Cruel World | +---------------------+ mysql > SET @z=1+2+3; mysql > SELECT @z; +------+ | @z | +------+ | 6 | +------+

Verwenden von Benutzervariablen in gespeicherten Prozeduren

mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); mysql > SET @greeting='Hello'; mysql > CALL GreetWorld( ); +----------------------------+ | CONCAT(@Begrüßung,' Welt') | +----------------------------+ | Hallo World | +----------------------------+

Übergabe von Benutzervariablen mit globalem Gültigkeitsbereich zwischen gespeicherten Prozeduren

mysql> CREATE PROCEDURE p1() SET @last_procedure='p1'; mysql> CREATE PROCEDURE p2() SELECT CONCAT('Letzte Prozedur war ',@last_procedure); mysql> CALL p1( ); mysql> CALL p2( ); +-----------------------------------------------+ | CONCAT('Letzte Prozedur war ',@last_proc | +-----------------------------------------------+ | Letzte Prozedur war p1 | +-----------------------------------------------+

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:

mysql > DELIMITER // mysql > CREATE PROCEDURE proc1 --nameName der gespeicherten Prozedur ->(IN parameter1 INTEGER) -> BEGIN -> DECLARE variable1 CHAR(10); -> IF parameter1 = 17 THEN -> SET variable1 = 'birds'; -> ELSE -> SET variable1 = 'beasts'; -> END IF; -> INSERT INTO table1 VALUES (variable1); -> END -> // mysql > DELIMITER ;

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.

mysql > DELIMITER // mysql > CREATE PROCEDURE proc3() -> begin -> declare x1 varchar(5) default 'outer'; -> begin -> declare x1 varchar(5) default 'inner'; -> select x1; -> end; -> select x1; -> end; -> // mysql > DELIMITER ;

(2). Bedingte Anweisungen

1. if-then-else-Anweisungen

mysql > DELIMITER // mysql > CREATE PROCEDURE proc2(IN parameter int) -> begin -> declare var int; -> set var=parameter+1; -> if var=0 then -> insert into t values(17); -> end if; -> if parameter=0 then -> update t set s1=s1+1; -> else -> update t set s1=s1+2; -> end if; -> end; -> // mysql > DELIMITER ;

2. case-Anweisung:

mysql > DELIMITER // mysql > CREATE PROCEDURE proc3 (in parameter int) -> begin -> declare var int; -> set var=parameter+1; -> case var -> when 0 then -> insert into t values(17); -> when 1 then -> insert into t values(18); -> else -> insert into t values(19); -> end case; -> end; -> // mysql > DELIMITER ; case when var=0 then insert into t values(30); when var>0 then when var<0 then else end case

(3). Schleifenanweisungen

1. while ···· end while

mysql > DELIMITER // mysql > CREATE PROCEDURE proc4() -> begin -> declare var int; -> set var=0; -> while var<6 do -> insert into t values(var); -> set var=var+1; -> end while; -> end; -> // mysql > DELIMITER ;
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.

mysql > DELIMITER // mysql > CREATE PROCEDURE proc5 () -> begin -> declare v int; -> set v=0; -> repeat -> insert into t values(v); -> set v=v+1; -> until v>=5 -> end repeat; -> end; -> // mysql > DELIMITER ;
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.

mysql > DELIMITER // mysql > CREATE PROCEDURE proc6 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -> insert into t values(v); -> set v=v+1; -> if v >=5 then -> leave LOOP_LABLE; -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;

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:

mysql > DELIMITER // mysql > CREATE PROCEDURE proc10 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -> if v=3 then -> set v=v+1; -> ITERATE LOOP_LABLE; -> end if; -> insert into t values(v); -> set v=v+1; -> if v>=5 then -> leave LOOP_LABLE; -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;

Referenzartikel:

https://www.cnblogs.com/geaozhang/p/6797357.html

http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html