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:

Hi!

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