CREATE PROCEDUREsp_name
([proc_parameter
[,...]]) [characteristic
...]routine_body
CREATE FUNCTIONsp_name
([func_parameter
[,...]]) RETURNStype
[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
-Anweisung enthalten.
value
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
ausgeführt (und
wieder rückgängig gemacht, wenn die Routine endet).
db_name
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“).
Zuweisungen werden auf Datentypinkompatibilitäten und Überlauf überprüft. Konvertierungs- und Überlaufprobleme werden mit Warnungen oder im Strict-Modus sogar mit Fehlern quittiert.
Für Zeichendatentypen gilt: Wenn in der Deklaration eine
CHARACTER SET
-Klausel steht, wird der
angegebene Zeichensatz mit seiner Standardkollation
verwendet. Fehlt eine solche Klausel, werden der Zeichensatz
und die Kollation der Datenbank benutzt. (Diese sind durch
die Systemvariablen
character_set_database
und
collation_database
vorgegeben.)
Parametern oder Variablen können nur Skalarwerte zugewiesen
werden. Eine Anweisung wie etwa SET x = (SELECT 1,
2)
wäre ungültig.
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.
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.