Procedimientos almacenados
Las bases de datos de MySQL soportan procedimientos almacenados. Un procedimiento almacenado es una
subrutina almacenada en el catálogo de la base de datos. Las aplicaciones pueden llamar y
ejecutar el procedimiento almacenado. La sentencia de SQL CALL
se usa para ejecutar un procedimiento almacenado.
Parámetros
Los procedimientos almacenados pueden tener parámetros IN,
INOUT y OUT,
dependiendo de la versión de MySQL. La interfaz mysqli no tiene una noción
especial de los diferentes tipos de parámetros.
Parámetro IN
Los parámetros de entrada son proporcionados con la sentencia CALL.
Asegúrese de que los valores están escapados correctamente.
Ejemplo #1 Llamar a un procedimiento almacenado
<?php
$mysqli = new mysqli("ejemplo.com", "usuario", "contraseña", "basedatos");
if ($mysqli->connect_errno) {
echo "Falló la conexión a MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
echo "Falló la creación de la tabla: (" . $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 "Falló la creación del procedimiento almacenado: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$mysqli->query("CALL p(1)")) {
echo "Falló CALL: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($resultado = $mysqli->query("SELECT id FROM test"))) {
echo "Falló SELECT: (" . $mysqli->errno . ") " . $mysqli->error;
}
var_dump($resultado->fetch_assoc());
?>
El resultado del ejemplo sería:
array(1) {
["id"]=>
string(1) "1"
}
Parámetro INOUT/OUT
A los valores de los parámetros INOUT/OUT
se acceden usando variables de sesión.
Ejemplo #2 Usar variables de sesión
<?php
$mysqli = new mysqli("ejemplo.com", "usuario", "contraseña", "basedatos");
if ($mysqli->connect_errno) {
echo "Falló la conexión a 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 "¡Hola!" INTO msg; END;')) {
echo "Falló la creación del procedimiento almacenado: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$mysqli->query("SET @msg = ''") || !$mysqli->query("CALL p(@msg)")) {
echo "Falló CALL: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($resultado = $mysqli->query("SELECT @msg as _p_out"))) {
echo "Falló la obtención: (" . $mysqli->errno . ") " . $mysqli->error;
}
$fila = $resultado->fetch_assoc();
echo $fila['_p_out'];
?>
El resultado del ejemplo sería:
Los desarrolladores de aplicaciones y framework pueden proporcionar una API más
conveniente usando una mezcla de variables de sesiones e inspección del catálogo de la base de datos.
Sin embargo, observe el posible impacto de rendimiento de una solución
personalizada basada en la inspección del catálogo.
Manejar conjuntos de resultados
Los procedimientos almacenados pueden devolver conjuntos de resultados. Los conjuntos de resultados devueltos desde
un procedimiento almacenado no se pueden obtener correctamente usando mysqli_query().
La función mysqli_query() combina la ejecución de la sentencia
y la obtención del primer conjunto de resultados dentro de un conjunto de resultados almacenado en buffer, si existe.
Sin embargo, existen unos conjuntos de resultados del procedimiento almacenado ocultos
para el usuario que hacen que mysqli_query() falle
al devolver los conjuntos de resultados esperados por el usuario.
Los conjuntos de resultados devueltos desde un procedimiento almacenado son obtenidos usando
mysqli_real_query() o mysqli_multi_query().
Ambas funciones permiten la obtención de cualquier número de conjuntos de resultados devueltos
por una sentencia, como CALL. El fallo de la obtención de todos
los conjuntos de resultados devueltos por un procedimiento almacenado causa un error.
Ejemplo #3 Obtener los resultados de procedimientos almacenados
<?php
$mysqli = new mysqli("ejemplo.com", "usuario", "contraseña", "basedatos");
if ($mysqli->connect_errno) {
echo "Falló la conexión a 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 "Falló la creación de la tabla: (" . $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 "Falló la creación del procedimiento almacenado: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$mysqli->multi_query("CALL p()")) {
echo "Falló CALL: (" . $mysqli->errno . ") " . $mysqli->error;
}
do {
if ($resultado = $mysqli->store_result()) {
printf("---\n");
var_dump($resultado->fetch_all());
$resultado->free();
} else {
if ($mysqli->errno) {
echo "Store failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
}
} while ($mysqli->more_results() && $mysqli->next_result());
?>
El resultado del ejemplo sería:
---
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"
}
}
Uso de sentencias preparadas
No es necesario un trato especial al usar la interfaz de sentencias
preparadas para obtener los resultados del mismo procedimiento almacenado de arriba.
Las interfaces de sentencias preparadas y no preparadas son similares.
Obserque que no todas las versioines del servidor de MYSQL pueden soportar
la preparación de la sentencia SQL CALL.
Ejemplo #4 Procedimientos Almacenados y Sentencias Preparadas
<?php
$mysqli = new mysqli("ejemplo.com", "usuario", "contraseña", "basedatos");
if ($mysqli->connect_errno) {
echo "Falló la conexión a 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 "Falló la creación de la tabla: (" . $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 "Falló la creación del procedimiento almacenado: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($sentencia = $mysqli->prepare("CALL p()"))) {
echo "Falló la preparación: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$sentencia->execute()) {
echo "Falló la ejecución: (" . $sentencia->errno . ") " . $sentencia->error;
}
do {
if ($resultado = $sentencia->get_result()) {
printf("---\n");
var_dump(mysqli_fetch_all($resultado));
mysqli_free_result($resultado);
} else {
if ($sentencia->errno) {
echo "Store failed: (" . $sentencia->errno . ") " . $sentencia->error;
}
}
} while ($sentencia->more_results() && $sentencia->next_result());
?>
Por supuesto, tamibién está soportado el uso de la API de vinculación para la obtención.
Ejemplo #5 Procedimientos Almacenados y Sentencias Preparadas usando la API de vinculación
<?php
if (!($sentencia = $mysqli->prepare("CALL p()"))) {
echo "Falló la preparación: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$sentencia->execute()) {
echo "Falló la ejecución: (" . $sentencia->errno . ") " . $sentencia->error;
}
do {
$id_out = NULL;
if (!$sentencia->bind_result($id_out)) {
echo "Falló la vinculiación: (" . $sentencia->errno . ") " . $sentencia->error;
}
while ($sentencia->fetch()) {
echo "id = $id_out\n";
}
} while ($sentencia->more_results() && $sentencia->next_result());
?>
Véase también
