Les procédures stockées
La base de données MySQL supporte les procédures stockées. Une procédure stockée
est une sous routine stockée dans le catalogue de la base de données. Les
applications peuvent appeler et exécuter une procédure stockée. La
requête SQL CALL
est utilisée pour exécuter
une procédure stockée.
Paramètre
Les procédures stockées peuvent avoir des paramètres IN
,
INOUT
and OUT
, suivant la version de MySQL.
L'interface mysqli n'a pas de notion spécifique des différents types de paramètres.
Paramètre IN
Les paramètres d'entrée sont fournis avec la requête CALL
.
Assurez-vous d'échapper correctement les valeurs.
Exemple #1 Appel d'une procédure stockée
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Échec lors de la connexion à MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
echo "Échec lors de la création de la table : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$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;")) {
echo "Échec lors de la création de la procédure stockée : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$mysqli->query("CALL p(1)")) {
echo "Échec lors de l'appel à la procédure stockée : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($res = $mysqli->query("SELECT id FROM test"))) {
echo "Échec de la requête : (" . $mysqli->errno . ") " . $mysqli->error;
}
var_dump($res->fetch_assoc());
?>
L'exemple ci-dessus va afficher :
array(1) {
["id"]=>
string(1) "1"
}
Paramètre INOUT/OUT
Les valeurs des paramètres INOUT
/OUT
sont accédées en utilisant les variables de session.
Exemple #2 Utilisation des variables de session
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Échec lors de la connexion à MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||
!$mysqli->query('CREATE PROCEDURE p(OUT msg VARCHAR(50)) BEGIN SELECT "Hi!" INTO msg; END;')) {
echo "Échec lors de la création de la procédure stockée : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$mysqli->query("SET @msg = ''") || !$mysqli->query("CALL p(@msg)")) {
echo "Échec de l'appel à la procédure stockée : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($res = $mysqli->query("SELECT @msg as _p_out"))) {
echo "Échec lors de la récupération : (" . $mysqli->errno . ") " . $mysqli->error;
}
$row = $res->fetch_assoc();
echo $row['_p_out'];
?>
L'exemple ci-dessus va afficher :
Les développeurs d'application et de framework peuvent fournir une API
plus conviviale utilisant un mix des variables de session et une inspection
du catalogue de la base de données. Cependant, veuillez garder à l'esprit
l'impact sur les performances dû à une solution personnalisée basée
sur l'inspection du catalogue.
Gestion des jeux de résultats
Les procédures stockées peuvent retourner des jeux de résultats. Les jeux de
résultats retournés depuis une procédure stockée ne peuvent être récupérés
correctement en utilisant la fonction mysqli_query().
La fonction mysqli_query() combine l'exécution de la requête
et la récupération du premier jeu de résultats dans un jeu de résultats mis en
mémoire tampon, s'il y en a. Cependant, il existe d'autres jeux de résultats
issus de la procédure stockée qui sont cachés de l'utilisateur et qui
font que la fonction mysqli_query() échoue lors de la
récupération des jeux de résultats attendus de l'utilisateur.
Les jeux de résultats retournés depuis une procédure stockée sont
récupérés en utilisant la fonction mysqli_real_query() ou
la fonction mysqli_multi_query().
Ces deux fonctions autorisent la récupération de n'importe quel nombre
de jeux de résultats retournés par une requête, comme la requête
CALL
. L'échec dans la récupération de tous les jeux de résultats
retournés par une procédure stockée cause une erreur.
Exemple #3 Récupération des résultats issus d'une procédure stockée
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Échec lors de la connexion à MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT)") ||
!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {
echo "Échec lors de la création de la table : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$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;')) {
echo "Échec lors de la création de la procédure stockée : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$mysqli->multi_query("CALL p()")) {
echo "Échec lors de l'appel à CALL : (" . $mysqli->errno . ") " . $mysqli->error;
}
do {
if ($res = $mysqli->store_result()) {
printf("---\n");
var_dump($res->fetch_all());
$res->free();
} else {
if ($mysqli->errno) {
echo "Échec de STORE : (" . $mysqli->errno . ") " . $mysqli->error;
}
}
} while ($mysqli->more_results() && $mysqli->next_result());
?>
L'exemple ci-dessus va afficher :
---
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"
}
}
Utilisation des requêtes préparées
Aucune gestion spéciale n'est requise lors de l'utilisation de l'interface
de préparation des requêtes pour récupérer les résultats depuis la même procédure
stockée que celle ci-dessous. Les interfaces de requête préparée et non préparée
sont similaires. Veuillez noter que toutes les versions du serveur MySQL ne
supporte pas la préparation des requêtes SQL CALL
.
Exemple #4 Procédures stockées et requête préparée
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Échec lors de la connexion à MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT)") ||
!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {
echo "Échec lors de la création de la table : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$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;')) {
echo "Échec lors de la création de la procédure stockée : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($stmt = $mysqli->prepare("CALL p()"))) {
echo "Échec lors de la préparation : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->execute()) {
echo "Échec lors de l'exécution : (" . $stmt->errno . ") " . $stmt->error;
}
do {
if ($res = $stmt->get_result()) {
printf("---\n");
var_dump(mysqli_fetch_all($res));
mysqli_free_result($res);
} else {
if ($stmt->errno) {
echo "Échec de STORE : (" . $stmt->errno . ") " . $stmt->error;
}
}
} while ($stmt->more_results() && $stmt->next_result());
?>
Bien sûr, l'utilisation de l'API de liage pour la récupération est également supportée.
Exemple #5 Procédures stockées et requête préparée en utilisant l'API de liage
<?php
if (!($stmt = $mysqli->prepare("CALL p()"))) {
echo "Échec lors de la préparation : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->execute()) {
echo "Échec lors de l'exécution : (" . $stmt->errno . ") " . $stmt->error;
}
do {
$id_out = NULL;
if (!$stmt->bind_result($id_out)) {
echo "Échec lors du liage : (" . $stmt->errno . ") " . $stmt->error;
}
while ($stmt->fetch()) {
echo "id = $id_out\n";
}
} while ($stmt->more_results() && $stmt->next_result());
?>
Voir aussi