PHP: Procedimientos almacenados

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:

¡Hola!

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

mysqli::query()
mysqli::multi_query()
mysqli_result::next-result()
mysqli_result::more-results()

add a note

User Contributed Notes 2 notes

down

Valverde ¶

5 years ago


<?php

// Store procedure call without params

$MyConnection = new mysqli ("DB_SERVER", "DB_USER", "DB_PASS", "DB_NAME");

mysqli_multi_query ($MyConnection, "CALL MyStoreProcedure") OR DIE (mysqli_error($MyConnection));

while (mysqli_more_results($MyConnection)) {

       if ($result = mysqli_store_result($MyConnection)) {

              while ($row = mysqli_fetch_assoc($result)) {

                     // i.e.: DBTableFieldName="userID"
                     echo "row = ".$row["DBTableFieldName"]."<br />";
                     ....

              }
              mysqli_free_result($result);
       }
       mysqli_next_result($conn);

}
?>
*******************************************************************
<?php

// Store procedure call using params

$MyConnection = new mysqli ("DB_SERVER", "DB_USER", "DB_PASS", "DB_NAME");

mysqli_query($MyConnection ,"SET @p0='".$MyParam1."'");
mysqli_query($MyConnection ,"SET @p1='".$MyParam2."'");
mysqli_multi_query ($MyConnection, "CALL MyStoreProcedure (@p0,@p1)") OR DIE (mysqli_error($MyConnection));

while (mysqli_more_results($MyConnection)) {

       if ($result = mysqli_store_result($MyConnection)) {

              while ($row = mysqli_fetch_assoc($result)) {

                     // i.e.: DBTableFieldName="userID"
                     echo "row = ".$row["DBTableFieldName"]."<br />";
                     ....

              }
              mysqli_free_result($result);
       }
       mysqli_next_result($conn);

}
?>

down

-10

paulo dot reis dot rosa at gmail dot com ¶

7 years ago


<?php
    /**
    * Small function to facilitate call procedure with multiple arguments (supports in/inout/out)
    */
    $db = new mysqli('localhost', 'root', 'password', 'database');

    $lt_query = callProcedure(
        $db, 
        "stored_procedure",
        array(
            "in_param1"    => "Value1",
            "in_param2"    => "Value2",
            "inout_param3" => "Value3",
            "out_param4"   => "",
            "out_param5"   => ""
        ));

    function callProcedure( $po_db, $pv_proc, $pt_args )
    {
        if (empty($pv_proc) || empty($pt_args))
        {
            return false;
        }
        $lv_call   = "CALL `$pv_proc`(";
        $lv_select = "SELECT";
        $lv_log = "";
        foreach($pt_args as $lv_key=>$lv_value)
        {
            $lv_query = "SET @_$lv_key = '$lv_value'";
            $lv_log .= $lv_query.";\n";
            if (!$lv_result = $po_db->query($lv_query))
            {
                /* Write log */
                return false;
            }
            $lv_call   .= " @_$lv_key,";
            $lv_select .= " @_$lv_key AS $lv_key,";
        }
        $lv_call   = substr($lv_call, 0, -1).")";
        $lv_select = substr($lv_select, 0, -1);
        $lv_log .= $lv_call;
        if ($lv_result = $po_db->query($lv_call))
        {
            if($lo_result = $po_db->query($lv_select))
            {
                $lt_result = $lo_result->fetch_assoc();
                $lo_result->free();
                return $lt_result;
            }
            /* Write log */
            return false;
        }
        /* Write log */
        return false;
    }
    
    /**
    * This will return an array like this:
    * 
    * $lt_query = array(
    *     'in_param1'   = 'Value1', // Same value as in call
    *     'in_param2'   = 'Value2', // Same value as in call
    *     'inout_param3' = ?,       // Value is changed accordingly
    *     'out_param4'   = ?,       // Value is changed accordingly
    *     'out_param5'   = ?        // Value is changed accordingly
    * )
    */
?>

add a note