Etiqueta: procedimientos almacenados

Llamar a procedimientos almacenados en PHP

en por Donnie Rocken Informática, Lenguajes de programación, SQL3 comentarios

Una entrada que llevaba mucho tiempo en el TODO list, sobre trabajar con procedimientos almacenados con PHP.

Para empezar vamos a plantear un escenario en el que tenemos tres procedimientos almacenados en MySQL (dos selects y un insert) contra una tabla llamada productos, que tiene tres campos (id como clave primaria, descripcion con una descripción del producto, precio con un valor numérico decimal).

El SP de la primera select, que nos devuelve la descripción y el precio del producto según su id, sería este:

CREATE PROCEDURE getProducto(IN id_val INT) 
  BEGIN 
    SELECT descripcion, precio from productos WHERE id = id_val; 
  END;

El SP de la segunda select, que nos devuelve la descripción y el precio todos los productos, por lo que no recibe parámetros:

CREATE PROCEDURE getAllProductos() 
  BEGIN 
    SELECT descripcion, precio from productos ; 
  END;

Y el sp del insert sería este, al que le pasamos los tres valores a meter:

CREATE PROCEDURE addProducto(IN id_val INT,descripcion_in varchar(50), precio_in float) 
  BEGIN 
    INSERT INTO productos VALUES (id_val, descripcion_in, precio_in);
  END;

Bueno, ya tenemos nuestro escenario, entonces ¿cómo hacemos para llamar a estos procedimientos desde nuestro código php? Pues simplemente lanzamos una consulta en la que llamamos a la instrucción CALL de MySQL para que ejecute el procedimiento almacenado que le digamos con los parámetros que tocan.

/*Empezamos con el procedimiento de inserción*/
/*Lo primero es crear un objeto mysqli*/
$mysqli = new mysqli("servidor", "usuario", "pass", "db");

/*Y llamamos al procedimiento para hacer la inserción*/
/*Si falla imprimimos el error*/
if (!$mysqli->query("CALL addProducto(1001, 'Nueces de Macadamia', 12.23)")) {
    echo "Falló la llamada: (" . $mysqli->errno . ") " . $mysqli->error;
}

La propia función query del objeto mysqli nos devolverá TRUE si no viene con errores y FALSE si pasa algo inesperado.

¿Y qué pasa con la recuperación de datos? Ahí nos vamos a un terreno más pantanoso. Para una consulta con un solo resultado no es problema:

/*Empezamos con el procedimiento de recuperación de una fila*/
/*Lo primero es crear un objeto mysqli*/
$mysqli = new mysqli("servidor", "usuario", "pass", "db");

/*Y llamamos al procedimiento para recoger los datos*/
/*Si falla imprimimos el error*/
if (!($res = $mysqli->query("CALL getProducto(1001)"))) {
    echo "Falló la llamada: (" . $mysqli->errno . ") " . $mysqli->error;
}

/*E imprimimos el resultado para ver que el ejemplo ha funcionado*/
var_dump($res->fetch_assoc());

Pero claro, en este caso podemos hacerlo así porque esperamos un solo resultado, pero si esperamos varias filas la cosa es distinta ya que mysqli->query() devuelve sólo el primer conjunto de resultados. Tenemos dos alternativas para esta situación: mysqli_real_query() o mysqli_multi_query(). En el ejemplo usaremos la segunda, y veremos también la forma de recorrer este resultado:

/*Empezamos con el procedimiento de recuperación de una fila*/
/*Lo primero es crear un objeto mysqli*/
$mysqli = new mysqli("servidor", "usuario", "pass", "db");

/*Y llamamos al procedimiento para recoger los datos*/
/*Si falla imprimimos el error*/
if (!$mysqli->multi_query("CALL getAllProductos()")) {
    echo "Falló la llamada: (" . $mysqli->errno . ") " . $mysqli->error;
}

/*Ahora con este bucle recogemos los resultados y los recorremos*/
do {
    /*En el if recogemos una fila de la tabla*/
    if ($res = $mysqli->store_result()) { 
        /*Imprimimos el resultado de la fila y debajo un salto de línea*/
        var_dump($res->fetch_all());
        printf("\n");
        /*La llamada a free() no es obligatoria, pero si recomendable para aligerar memoria y para evitar problemas si después hacemos una llamada a otro procedimiento*/
        $res->free();
    } else {
        if ($mysqli->errno) {
            echo "Store failed: (" . $mysqli->errno . ") " . $mysqli->error;
        }
    }
} while ($mysqli->more_results() && $mysqli->next_result());
/*El bucle se ejecuta mientras haya más resultados y se pueda saltar al siguiente*/

Añadiré a esto que también es posible utilizar las llamadas a procedimientos almacenados usando sentencias preparadas, procediendo del modo habitual a la hora de pasar los parámetros.

Bucles y condicionales en procedimientos almacenados de MySQL

en por Donnie Rocken SQL7 comentarios

A la hora de crear procedimientos almacenados en MySQL tenemos la opción de utilizar distintos tipos de bucles y de condicionales, lo que nos dotará de la capacidad de llevar a cabo acciones más complejas dentro de nuestros procedimientos.

Para esto disponemos de las estructuras de control IF THEN ELSE, CASE, WHILE y REPEAT, además de poder definir y declarar variables locales dentro de los procedimientos.

Variables:
Para declarar una variable la definimos con DEFINE y la inicializamos con SET:

DEFINE varprueba INT; #definimos la variable y su tipo
SET varpruebra = 10; #le damos un valor concordante con el tipo

IF THEN ELSE:
El condicional simple IF que tenemos en la mayoría de los lenguajes de programación, que puede ser complementado o no con un ELSE para el caso de que no se cumpla. La sintaxis es IF condición THEN (el ELSE si hace falta) END IF. Podéis ver un ejemplo:

delimiter $$
CREATE procedure ej(IN val int)     /* Parámetro de entrada */
  begin    
    IF val = 0 then
      INSERT INTO tbejemplo VALUES('No disponible');
    else
      INSERT INTO tbejemplo VALUES(val);
    end IF;
  end$$
delimiter ;

CASE:
Condicional que nos permite evaluar el valor de una variable y realizar diversas acciones según varias posibilidades. Es una forma de abreviar un montón de IF anidados. En este caso el ELSE nos permite definir un valor por defecto (como el default en un switch/case de C, Java o PHP):

delimiter $$
CREATE procedure ej(IN val int)     /* Parámetro de entrada */
  begin
    case val
     when 5 then INSERT INTO prueba VALUES('Suficientes');
     when 4 then INSERT INTO prueba VALUES('Reserva');
     else INSERT INTO prueba VALUES ('Insuficientes);
    end case;
  end$$
delimiter;

WHILE:
Un bucle que hace que un procedimiento se repita mientras se de una condición. Si la condición ya no se da antes de entrar al bucle, no entra.

delimiter $$
CREATE procedure ej(IN val int)     /* Parámetro de entrada */
  begin
    define i int;
    set i = 0;
    while i<5 do
      INSERT INTO prueba VALUES (i);
      set i=i+1;
    end while;
  end$$
delimiter ;

REPEAT:
Lo contratio que WHILE, repeat es un bucle UNTIL, se realiza hasta que se de una condición. Si bien también comparte con el DO…WHILE de C (y Java, y PHP…) que siempre se ejecuta al menos una vez.

delimiter $$
CREATE procedure ej(IN val int)     /* Parámetro de entrada */
  begin
    define i int;
    set i = 0;
    repeat
      INSERT INTO prueba VALUES (i);
      set i=i+1;
      until i <= 5;
    end repeat;
  end$$
delimiter ;

Con esto ya podéis empezar a programar un poco más en serio dentro de MySQL.

Procedimientos almacenados con parámetros variables en el WHERE

en por Donnie Rocken Informática, SQL4 comentarios

Me excusaba ayer en Twitter, tengo un nuevo empleo y menos tiempo para escribir en el blog. No os preocupéis, que no lo dejaré de lado pero sí es cierto que el ritmo de publicaciones bajará un poquillo. Pero lo bueno de tener un nuevo trabajo es que al afrontar nuevos retos tengo material para nuevos artículo sobre programación (aparte de lo de cobrar regularmente a fin de mes y hacer currículum).

Y una cosa con la que estoy trabajando estos días mucho es con procedimientos almacenados en MySQL. Hoy se me planteada una cuestión, que era un procedimiento que recibe varios parámetros desde un formulario en PHP (para otro día dejo «Llamar a procedimientos almacenados desde PHP») para realizar una búsqueda. El problema: hay parámetros que pueden ir vacíos, ya que el usuario puede estar buscando por todos o por uno. Y el procedimiento almacenado no permite definir parámetros opcionales, como sí podría hacer en una función de PHP. ¿Solución? Simple y fácil.

Lo primero es pasar todos los parámetros que no están definidos al procedimiento con valor NULL. Lo siguiente es que cada comparación que se haría en el WHERE ahora formará parte de un OR donde también comprobamos si el parámetro enviado es nulo. Esto que suena a churro se explica fácilmente con un supuesto y un ejemplo de código:

Imaginemos que queremos hacer una búsqueda en una lista de, por ejemplo, alumnos de un centro. Podemos buscar por nombre, apellidos y un máximo y un mínimo de edad. Bueno, pues el procedimiento sería algo así:

DELIMITER $$ --esto para no tener conflicto con el punto y coma, puede valer otra opción
CREATE PROCEDURE ps_BusquedaVarParam(IN nombre_in varchar(30), apellidos_in varchar(50), minEdad int, maxEdad int)
BEGIN
  SELECT * FROM alumnos WHERE (nombre_in is null or Nombre = nombre_in) and (apellidos_in is null or Apellidos = apellidos_in) and (minEdad is null or Edad>=minEdad) and (maxEdad is null or Edad<=maxEdad);
END $$
DELIMITER;

La idea de hacer así este tipo de comparación es que si le pasamos un nulo la comparación con null devolverá TRUE. De esa forma si es NULL devuelve TRUE, si se da la condición también devuelve TRUE y si no es nulo pero no comple devuelve FALSE. O lo que es lo mismo, si va un NULL te devuelve TRUE y así ese parámetro no te jode que se cumple el AND de la comparativa. Así logramos que no haya problema para hacer la búsqueda sin mandar todos los parámetros.

Y listo, tranquilos, irán más espaciados, pero seguirá habiendo movimientos en este blog. Y de calidad, como siempre.

Ojo a los nombres de variables, por cierto. Si el parámetro tiene el mismo nombre que el campo, aunque uno esté en mayúsculas y otro en minúsculas, fallará. En teoría debería no dar problemas, pero dependiendo de la configuración de MySQL puede fallar o no (de hecho tuve que modificar la consulta, porque en una base de datos me funcionaba pero en otra fallaba), así que mejor les añadís a las variables un prefijo o sufijo y vía (en este caso le puse _in)