CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
    
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
    
func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statement

Diese Anweisungen erzeugen gespeicherte Routinen. Um sie nutzen zu können, ist das CREATE ROUTINE-Recht erforderlich. Wenn Binärlogging eingeschaltet ist, kann für die CREATE FUNCTION-Anweisung auch das SUPER-Recht erforderlich sein, wie in Abschnitt 19.4, „Binärloggen gespeicherter Routinen und Trigger“, beschrieben. MySQL erteilt dem Erzeuger einer Routine automatisch das ALTER ROUTINE- und das EXECUTE-Recht.

Nach Voreinstellung wird die Routine mit der Standarddatenbank verbunden. Um sie explizit mit einer bestimmten Datenbank zu verbinden, geben Sie bei der Erzeugung der Routine den Namen der Datenbank in der Form db_name.sp_name an.

Wenn der Name der Routine gleichlautend mit dem Namen einer eingebauten SQL-Funktion ist, müssen Sie zwischen dem Namen und der nachfolgenden Klammer ein Leerzeichen setzen, wenn Sie die Routine definieren, sonst tritt ein Syntaxfehler ein. Das gilt auch, wenn Sie die Routine zu einem späteren Zeitpunkt aufrufen. Aus diesem Grund raten wir Ihnen, keine Namen vorhandener SQL-Funktionen für Ihre eigenen gespeicherten Routinen zu verwenden.

Der SQL-Modus IGNORE_SPACE gilt für eingebaute Funktionen und nicht für gespeicherte Routinen. Es ist immer zulässig, Leerzeichen hinter den Namen einer Routine zu setzen, egal ob IGNORE_SPACE eingeschaltet ist oder nicht.

Die Parameterliste in runden Klammern muss immer vorhanden sein. Wenn keine Parameter übergeben werden, muss eine leere Parameterliste () verwendet werden. Jeder Parameter ist nach Voreinstellung ein IN-Parameter. Um einen Parameter anderslautend zu definieren, setzen Sie das Schlüsselwort OUT oder INOUT vor den Parameternamen.

Hinweis: Als IN, OUT oder INOUT können nur PROCEDURE-Parameter definiert werden. (FUNCTION-Parameter gelten immer als IN-Parameter.)

Jeder Parameter kann mit jedem zulässigen Datentyp deklariert werden, nur das Attribut COLLATE darf nicht verwendet werden.

Die RETURNS-Klausel kann nur für eine FUNCTION angegeben werden; hier ist sie sogar obligatorisch. Sie gibt den Rückgabetyp der Funktion an. Der Funktionsrumpf muss eine RETURN value-Anweisung enthalten.

Der routine_body besteht aus einer gültigen SQL-Prozeduranweisung. Diese kann eine einfache Anweisung wie etwa ein SELECT oder INSERT sein, sie kann aber auch eine zusammengesetzte Anweisung mit BEGIN und END sein. Die Syntax zusammengesetzter Anweisungen wird in Abschnitt 19.2.5, „BEGIN ... END-Syntax für komplexe Anweisungen“, erläutert. Zusammengesetzte Anweisungen können Deklarationen, Schleifen und andere Anweisungen mit Kontrollstrukturen enthalten. Die Syntax dieser Anweisungen wird weiter unten in diesem Kapitel erklärt, beispielsweise unter Abschnitt 19.2.6, „Syntax der DECLARE-Anweisung“, und Abschnitt 19.2.10, „Konstrukte für die Ablaufsteuerung“.

Die CREATE FUNCTION-Anweisung diente in früheren MySQL-Versionen der Unterstützung von UDFs (User-defined Functions, benutzerdefinierte Funktionen). Siehe auch Abschnitt 26.3, „Hinzufügen neuer Funktionen zu MySQL“. UDFs werden weiterhin unterstützt, obwohl inzwischen auch die gespeicherten Funktionen existieren. Eine UDF kann man als externe gespeicherte Funktion betrachten. Bitte beachten Sie jedoch, dass gespeicherte Funktionen denselben Namensraum wie UDFs benutzen.

Eine Prozedur oder Funktion gilt als „deterministisch“, wenn sie für gleiche Eingabeparameter immer gleiche Resultate erzeugt; ansonsten ist sie „nichtdeterministisch“. Wenn in der Definition der Routine weder DETERMINISTIC noch NOT DETERMINISTIC steht, ist die Voreinstellung NOT DETERMINISTIC.

Im Zusammenhang mit Replikation wird eine Routine durch Verwendung der Funktion NOW() (oder ihrer Synonyme) oder RAND() nicht unbedingt nichtdeterministisch. Für NOW() enthält das Binärlog den Zeitstempel und repliziert korrekt. RAND() repliziert ebenfalls richtig, sofern es in einer Routine nur ein einziges Mal aufgerufen wird. (Den Ausführungszeitstempel der Routine und den Zufallszahlen-Seed können Sie als implizite Eingaben betrachten, die auf Master und Slave identisch sind.)

Zurzeit wird das DETERMINISTIC-Merkmal vom Optimierer zwar akzeptiert, aber noch nicht benutzt. Wenn jedoch Binärlogging eingeschaltet ist, nimmt dieses Merkmal Einfluss darauf, welche Routinendefinitionen von MySQL akzeptiert werden. Siehe Abschnitt 19.4, „Binärloggen gespeicherter Routinen und Trigger“.

Einige Merkmale informieren über das Wesen der von der Routine verwendeten Daten. CONTAINS SQL weist darauf hin, dass die Routine keine Anweisungen zum Lesen oder Schreiben von Daten enthält. NO SQL bedeutet, dass sie keine SQL-Anweisungen enthält. READS SQL DATA kennzeichnet Routinen mit lesenden, aber ohne schreibende Anweisungen und MODIFIES SQL DATA Routinen mit Anweisungen, die Daten schreiben können. CONTAINS SQL ist der Standardwert, wenn kein anderes dieser Merkmale explizit angegeben ist. Die Merkmale haben nur beratenden Charakter. Sie schränken den Server nicht ein, wenn es darum geht, welche Arten von Anweisungen ausgeführt werden dürfen oder nicht.

Das Merkmal SQL SECURITY gibt an, ob die Routine mit den Berechtigungen des Benutzers, der sie erzeugte, oder des Benutzers, der sie aufruft, ausgeführt werden soll. Der Standardwert ist DEFINER. Dieses Feature ist neu in SQL:2003. Der Erzeuger oder Aufrufer muss die Zugriffsberechtigung auf die Datenbank haben, mit der die Routine verbunden ist. Um die Routine auszuführen, ist das EXECUTE-Recht erforderlich. Der Benutzer, der diese Berechtigung haben muss, kann nur entweder der Erzeuger oder der Aufrufer sein, je nachdem, wie SQL SECURITY eingestellt ist.

MySQL speichert die Einstellung der Systemvariablen sql_mode, die gerade in Kraft ist, wenn eine Routine erzeugt wird, und führt diese Routine dann immer mit dieser Einstellung aus.

Beim Aufruf der Routine wird ein implizites USE db_name ausgeführt (und wieder rückgängig gemacht, wenn die Routine endet). USE-Anweisungen sind in gespeicherten Routinen nicht erlaubt.

Der Server verwendet den Datentyp eines Routinenparameters oder Funktionsrückgabewerts wie folgt. Diese Regeln gelten auch für lokale Laufzeitvariablen, die mit der DECLARE-Anweisung angelegt wurden (Abschnitt 19.2.7.1, „Lokale DECLARE-Variablen“).

Die COMMENT-Klausel ist eine MySQL-Erweiterung und kann zur Beschreibung der gespeicherten Routine genutzt werden. Diese Information wird mit den Anweisungen SHOW CREATE PROCEDURE und SHOW CREATE FUNCTION angezeigt.

In MySQL dürfen Routinen auch DDL-Anweisungen wie beispielsweise CREATE und DROP enthalten. Darüber hinaus dürfen gespeicherte Prozeduren (nicht aber gespeicherte Funktionen) in MySQL auch Transaktionsanweisungen in SQL enthalten, wie etwa COMMIT. Gespeicherte Funktionen dürfen keine Anweisungen enthalten, die explizit oder implizit ein Commit oder Rollback ausführen. Eine Unterstützung für diese Anweisungen wird vom SQL-Standard auch gar nicht verlangt, der Standard besagt lediglich, dass jeder DBMS-Hersteller selbst entscheiden kann, ob er sie erlauben will oder nicht.

Gespeicherte Routinen dürfen kein LOAD DATA INFILE enthalten.

Anweisungen, die eine Ergebnismenge zurückgeben, dürfen nicht innerhalb einer gespeicherten Funktion verwendet werden. Dazu gehören auch SELECT-Anweisungen, soweit sie nicht mithilfe einer INTO-Klausel Spaltenwerte in Variablen laden, sowie SHOW und andere Anweisungen wie beispielsweise EXPLAIN. Anweisungen, für die zur Definitionszeit festgelegt werden kann, dass sie eine Ergebnismenge zurückliefern, lösen einen Not allowed to return a result set from a function-Fehler aus (ER_SP_NO_RETSET_IN_FUNC). Anweisungen, für die nur zur Laufzeit festgelegt werden kann, dass sie eine Ergebnismenge zurückliefern, lösen einen PROCEDURE %s can't return a result set in the given context-Fehler aus (ER_SP_BADSELECT).

Das folgende Beispiel zeigt eine einfache gespeicherte Prozedur mit einem OUT-Parameter. Das Beispiel verwendet den mysql-Clientbefehl delimiter, um das Begrenzungszeichen für die Anweisung von ; in // zu ändern, während die Prozedur definiert wird. So kann das Begrenzungszeichen ; im Prozedurrumpf an den Server durchgereicht werden, ohne von mysql selbst interpretiert zu werden.

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

Wenn Sie den Befehl delimiter benutzen, geben Sie bitte nicht das Backslash-Zeichen (‘\’) an, da es das Escape-Symbol für MySQL ist.

Das folgende Beispiel zeigt eine Funktion, die einen Parameter entgegennimmt, eine Operation mit einer SQL-Funktion ausführt und das Ergebnis zurückliefert. In diesem Fall ist es nicht nötig, delimiter zu benutzen, da die Funktionsdefinition keine ;-Zeichen als interne Anweisungstrennzeichen enthält:

mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

Eine gespeicherte Funktion gibt einen Wert zurück, dessen Datentyp in ihrer RETURNS-Klausel angegeben ist. Wenn die RETURN-Anweisung einen Wert eines anderen Typs zurückgibt, wird dieser mit Gewalt in den richtigen Typ umgewandelt. Wenn eine Funktion beispielsweise einen Rückgabewert vom Typ ENUM oder SET hat, die RETURN-Anweisung jedoch einen Integer zurückgibt, so liefert die Funktion den String für das entsprechende ENUM-Element oder die Menge der SET-Elemente.