Gespeicherte Prozeduren (Stored Procedures)
Die MySQL-Datenbank unterstützt gespeicherte Prozeduren. Eine gespeicherte
Prozedur ist ein Unterprogramm, das im Datenbankkatalog gespeichert ist.
Anwendungen können die gespeicherte Prozedur aufrufen und ausführen. Um
eine gespeicherte Prozedur auszuführen, wird die SQL-Anweisung
CALL verwendet.
Parameter
In Abhängigkeit von der MySQL-Version können gespeicherte Prozeduren die
Parameter IN, INOUT und
OUT haben. Die mysqli-Schnittstelle selbst hat keine
speziellen Bezeichnungen für die verschiedenen Arten von Parametern.
Der Parameter IN
Die Eingabeparameter werden mit der Anweisung
CALL bereitgestellt. Bitte stellen Sie sicher, dass die
Werte korrekt maskiert sind.
Beispiel #1 Aufrufen einer gespeicherten Prozedur
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query("CREATE PROCEDURE p(IN id_val INT) BEGIN INSERT INTO test(id) VALUES(id_val); END;");
$mysqli->query("CALL p(1)");
$result = $mysqli->query("SELECT id FROM test");
var_dump($result->fetch_assoc());
Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
array(1) {
["id"]=>
string(1) "1"
}
Die Parameter INOUT/OUT
Auf die Werte der Parameter INOUT/OUT
wird über Session-Variablen zugegriffen.
Beispiel #2 Verwendung von Session-Variablen
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query('CREATE PROCEDURE p(OUT msg VARCHAR(50)) BEGIN SELECT "Hi!" INTO msg; END;');
$mysqli->query("SET @msg = ''");
$mysqli->query("CALL p(@msg)");
$result = $mysqli->query("SELECT @msg as _p_out");
$row = $result->fetch_assoc();
echo $row['_p_out'];
Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
Anwendungs- und Framework-Entwickler können gegebenenfalls eine
komfortablere API bereitstellen, die neben Session-Variablen auch das
direkte Durchsuchen von Datenbankkatalogen verwendet. Dabei sind jedoch die
Leistungseinbußen zu beachten, die eine benutzerdefinierte Lösung auf Basis
der Kataloginspektion haben kann.
Umgang mit Ergebnismengen
Gespeicherte Prozeduren können Ergebnismengen zurückgeben. Ergebnismengen,
die von einer gespeicherten Prozedur zurückgegeben werden, können mit
mysqli::query() nicht korrekt abgerufen werden. Die
Funktion mysqli::query() führt die Anweisung aus
und ruft, falls vorhanden, die erste Ergebnismenge in einen Puffer ab.
Gespeicherte Prozeduren können jedoch weitere Ergebnismengen zurückgeben,
die dem Benutzer verborgen sind, was dazu führt, dass
mysqli::query() nicht die vom Benutzer erwarteten
Ergebnismengen zurückgibt.
Ergebnismengen, die von einer gespeicherten Prozedur zurückgegeben werden,
werden mit mysqli::real_query() oder
mysqli::multi_query() abgerufen. Beide Funktionen
ermöglichen das Abrufen einer beliebigen Anzahl von Ergebnismengen, die
von einer Anweisung wie CALL zurückgegeben werden.
Gelingt es nicht, alle Ergebnismengen abzurufen, die von einer
gespeicherten Prozedur zurückgegeben wurden, löst das einen Fehler aus.
Beispiel #3 Ergebnisse von gespeicherten Prozeduren abrufen
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');
$mysqli->multi_query("CALL p()");
do {
if ($result = $mysqli->store_result()) {
printf("---\n");
var_dump($result->fetch_all());
$result->free();
}
} while ($mysqli->next_result());
Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
---
array(3) {
[0]=>
array(1) {
[0]=>
string(1) "1"
}
[1]=>
array(1) {
[0]=>
string(1) "2"
}
[2]=>
array(1) {
[0]=>
string(1) "3"
}
}
---
array(3) {
[0]=>
array(1) {
[0]=>
string(1) "2"
}
[1]=>
array(1) {
[0]=>
string(1) "3"
}
[2]=>
array(1) {
[0]=>
string(1) "4"
}
}
Verwendung von vorbereiteten Anweisungen
Es ist keine besondere Vorgehensweise erforderlich, wenn die Schnittstelle
für vorbereitete Anweisungen zum Abrufen von Ergebnissen aus der gleichen
gespeicherten Prozedur wie oben verwendet wird. Die Schnittstellen für
vorbereitete und nicht-vorbereitete Anweisungen sind ähnlich. Es ist zu
beachten, dass nicht jede Version des MYSQL-Servers die Vorbereitung der
SQL-Anweisung CALL unterstützt.
Beispiel #4 Gespeicherte Prozeduren und vorbereitete Anweisungen
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');
$stmt = $mysqli->prepare("CALL p()");
$stmt->execute();
do {
if ($result = $stmt->get_result()) {
printf("---\n");
var_dump($result->fetch_all());
$result->free();
}
} while ($stmt->next_result());
Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
---
array(3) {
[0]=>
array(1) {
[0]=>
int(1)
}
[1]=>
array(1) {
[0]=>
int(2)
}
[2]=>
array(1) {
[0]=>
int(3)
}
}
---
array(3) {
[0]=>
array(1) {
[0]=>
int(2)
}
[1]=>
array(1) {
[0]=>
int(3)
}
[2]=>
array(1) {
[0]=>
int(4)
}
}
Natürlich wird auch die Verwendung der bind-API für das Abrufen von Daten
unterstützt.
Beispiel #5 Gespeicherte Prozeduren und vorbereitete Anweisungen mit der bind-API
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');
$stmt = $mysqli->prepare("CALL p()");
$stmt->execute();
do {
if ($stmt->store_result()) {
$stmt->bind_result($id_out);
while ($stmt->fetch()) {
echo "id = $id_out\n";
}
}
} while ($stmt->next_result());
Das oben gezeigte Beispiel erzeugt folgende Ausgabe:
id = 1
id = 2
id = 3
id = 2
id = 3
id = 4
Siehe auch
