Los procedimientos almacenados

La base de datos MySQL soporta los procedimientos almacenados. Un procedimiento almacenado es una subrutina almacenada en el catálogo de la base de datos. Las aplicaciones pueden llamar y ejecutar un procedimiento almacenado. La consulta SQL CALL es utilizada para ejecutar un procedimiento almacenado.

Parámetro

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 específica de los diferentes tipos de parámetros.

Parámetro IN

Los parámetros de entrada son proporcionados con la consulta CALL. Asegúrese de escapar correctamente los valores.

Ejemplo #1 Llamada a un procedimiento almacenado

<?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());

El ejemplo anterior mostrará:

array(1) {
  ["id"]=>
  string(1) "1"
}

Parámetro INOUT/OUT

Los valores de los parámetros INOUT/OUT son accedidos utilizando las variables de sesión.

Ejemplo #2 Uso de las variables de sesión

<?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'];

El ejemplo anterior mostrará:

Hi!

Los desarrolladores de aplicaciones y de frameworks pueden proporcionar una API más amigable utilizando una mezcla de las variables de sesión y una inspección del catálogo de la base de datos. Sin embargo, tenga en cuenta el impacto en el rendimiento debido a una solución personalizada basada en la inspección del catálogo.

Gestión de los juegos de resultados

Los procedimientos almacenados pueden devolver juegos de resultados. Los juegos de resultados devueltos desde un procedimiento almacenado no pueden ser recuperados correctamente utilizando la función mysqli::query(). La función mysqli::query() combina la ejecución de la consulta y la recuperación del primer juego de resultados en un juego de resultados en memoria tamponada, si lo hay. Sin embargo, existen otros juegos de resultados provenientes del procedimiento almacenado que están ocultos al usuario y que hacen que la función mysqli::query() falle al recuperar los juegos de resultados esperados por el usuario.

Los juegos de resultados devueltos desde un procedimiento almacenado son recuperados utilizando la función mysqli::real_query() o mysqli::multi_query(). Estas dos funciones permiten la recuperación de cualquier número de juegos de resultados devueltos por una consulta, como la consulta CALL. Fallar en la recuperación de todos los juegos de resultados devueltos por un procedimiento almacenado causa un error.

Ejemplo #3 Recuperación de los resultados provenientes de un procedimiento almacenado

<?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 ($res = $mysqli->store_result()) {
        var_dump($result->fetch_all());
        $result->free();
    }
} while ($mysqli->next_result());

El ejemplo anterior mostrará:

---
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 las consultas preparadas

No se requiere una gestión especial al utilizar la interfaz de preparación de consultas para recuperar los resultados desde el mismo procedimiento almacenado que el anterior. Las interfaces de consulta preparada y no preparada son similares. Tenga en cuenta que todas las versiones del servidor MySQL no soportan la preparación de las consultas SQL CALL.

Ejemplo #4 Procedimientos almacenados y consulta preparada

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Fallo durante la conexión a MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$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()");

if (!$stmt->execute()) {
    echo "Fallo durante la ejecución: (" . $stmt->errno . ") " . $stmt->error;
}

do {
    if ($result = $stmt->get_result()) {
        var_dump($result->fetch_all());
        $result->free();
    }
} while ($stmt->next_results());

El ejemplo anterior mostrará:

---
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)
  }
}

Por supuesto, el uso de la API de enlace para la recuperación también es soportado.

Ejemplo #5 Procedimientos almacenados y consulta preparada utilizando la API de enlace

<?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());

El ejemplo anterior mostrará:

id = 1
id = 2
id = 3
id = 2
id = 3
id = 4

Ver también