Procedimientos almacenados con MySQL 5

Hoy, mientras leía mensajes del correo antiguo rescaté unos apuntes de un curso de programación que hice el pasado verano. Los apuntes eran de MySQL 5, más específicamente de cómo hacer procedimientos almacenados , triggers , handlers, funciones y toda esa parafernalia que MySQL implementa en su versión 5.

MySQL ha sido siempre un motor de bases de datos muy rápido y muy utilizado para proyectos open source de código abierto, sobre todo para proyectos web dada su gran velocidad. Pero también a sido muy criticado por la falta de características avanzadas que otros Sistemas Gestores de Bases de Datos , como Oracle, SQLServer de Microsoft o PostgreSQL si tenían. Estas características avanzadas son sobre todo los procedimientos almacenados, triggers, transacciones y demás cosas.

MySQL, que ahora forma parte de Sun, se puso las pilas y en su versión 5 implementó muchas de estas características, dejando un SGBD muy rápido y además muy bien preparado para implementar bases de datos realmente grandes y mantenibles.

Pero, ¿qué es realmente un procedimiento almacenado? Pues es un programa que se almacena físicamente en una tabla dentro del sistema de bases de datos. Este programa esta hecho con un lenguaje propio de cada Gestor de BD y esta compilado, por lo que la velocidad de ejecución será muy rápida.

Principales Ventajas :

  • Seguridad:Cuando llamamos a un procedimiento almacenado, este deberá realizar todas las comprobaciones pertinentes de seguridad y seleccionará la información lo más precisamente posible, para enviar de vuelta la información justa y necesaria y que por la red corra el mínimo de información, consiguiendo así un aumento del rendimiento de la red considerable.
  • Rendimiento: el SGBD, en este caso MySQL, es capaz de trabajar más rápido con los datos que cualquier lenguaje del lado del servidor, y llevará a cabo las tareas con más eficiencia. Solo realizamos una conexión al servidor y este ya es capaz de realizar todas las comprobaciones sin tener que volver a establecer una conexión. Esto es muy importante, una vez leí que cada conexión con la BD puede tardar hasta medios segundo, imagínate en un ambiente de producción con muchas visitas como puede perjudicar esto a nuestra aplicación… Otra ventaja es la posibilidad de separar la carga del servidor, ya que si disponemos de un servidor de base de datos externo estaremos descargando al servidor web de la carga de procesamiento de los datos.
  • ­­

  • Reutilización: el procedimiento almacenado podrá ser invocado desde cualquier parte del programa, y no tendremos que volver a armar la consulta a la BD cada que vez que queramos obtener unos datos.

Desventajas:

El programa se guarda en la BD, por lo tanto si se corrompe y perdemos la información también perderemos nuestros procedimientos. Esto es fácilmente subsanable llevando a cabo una buena política de respaldos de la BD.

Tener que aprender un nuevo lenguaje… esto es siempre un engorro, sobre todo si no tienes tiempo.

¿Alguna otra?

Por lo tanto es recomendable usar procedimientos almacenados siempre que se vaya a hacer una aplicación grande, ya que nos facilitará la tarea bastante y nuestra aplicación será más rápida.

Vamos a ver un ejemplo :

Abrimos una consola de MySQL seleccionamos una base de datos y empezamos a escribir:

Vamos a crear dos tablas en una almacenaremos las personas mayores de 18 años y en otra las personas menores.

create table ninos(edad int, nombre varchar(50));
create table adultos(edad int, nombre varchar(50));

Imagínate que ahora queremos introducir personas en las tablas pero dependiendo de la edad queremos que se introduzcan en una tabla u otra, si estamos usando PHP podríamos comprobar mediante código si la persona es mayor de edad. Lo haríamos así:

$nombre = $_POST[‘nombre’];
$edad = $_POST[‘edad’];

if($edad < 18){
	mysql_query(‘insert into ninos values(’ . $edad . ‘ , “’.$nombre.’”)’);
}else{
mysql_query(‘insert into adultos values(’ . $edad . ‘ , “’.$nombre.’”)’);
}

Si la consulta es corta como en este caso, esta forma es incluso más rápida que tener que crear un procedimiento almacenado, pero si tienes que hacer esto muchas veces a lo largo de tu aplicación es mejor hacer lo siguiente:

Creamos el procedimiento almacenado:

delimiter //

create procedure  introducePersona(in edad int,in nombre varchar(50))
begin
if edad < 18 then
insert into ninos values(edad,nombre);
else
insert into adultos values(edad,nombre);
end if;
end;

//

Ya tenemos nuestro procedimiento , vamos a por una visión más detallada…

La primera linea es para decirle a mySQL que a partir de ahroa hasta que no introduzcamos // no se acaba la sentencia, esto lo hacemos así por que en nuestro procedimiento almacenado tendremos que introducir el carcter “;” para las sentencias, y si pulamos enter MySQL pensará que ya hemos acabado la consulta y dará error.

Con create procedure empezamos la definición de procedimiento con nombre introducePersona. En un procedimiento almacenado existen parámetros de entrada y de salida, los de entrada (precedidos de “in”) son los que le pasamos para usar dentro del procedimiento y los de salida (precedidos de “out”) son variables que se establecerán a lo largo del procedimiento y una vez esta haya finalizado podremos usar ya que se quedaran en la sesión de MySQL.

En este procedimiento simple solo vamos usar de entrada, más adelante veremos como usar parámetros de salida.

Para hacer una llamada a nuestro procedimiento almacenado usaremos la sentencia call:

call introducePersona(25,”JoseManuel”);

Una vez tenemos ya nuestro procedimiento simplemente lo ejecutaremos desde PHP mediante una llamada como esta:

$nombre = $_POST['nombre'];
$edad = $_POST['edad'];

mysql_query(‘call introducePersona(’ . $edad . ‘ ,“ ’.$nombre.’ ”);’);

De ahora en adelante, usaremos siempre el procedimiento para introducir personas en nuestra BD de manera que si tenemos 20 scritps PHP que lo usan y un buen día decidimos que la forma de introducir personas no es la correcta, solo tendremos que modificar el procedimiento introducePersona y no los 20 scrits.

Seguiremos viendo procedimientos más complejos, funciones, triggers, transacciones, handlers y vistas más adelante en futuros posts…

Un Saludo.

Comments

  1. Diego Alejandro Mora says

    oye amigo y si quieres que ese procedimiento almacenado te de como salida un xml? ayudaaa

  2. fens says

    bkn, genial el aporte, simplecito asi komo para motivar a seguir investigando a los ke kieren aprender…

  3. azkotoki says

    No quiero parecer un pelma, pero…

    No utiliceis procedimientos almacenados, por favor. ¡Pensad en los niños!

    Esa idea de encapsular consultas sql dentro de procedimientos almacenados en aras de obtener mayor rendimiento y seguridad es la cosa más estúpida que he oido jamás, que a día de hoy, está desfasada.

    Tomaros por favor el tiempo de leer los siguientes artículos, que seguro explican el asunto mejor que yo (en inglés, lo siento), ántes de que sea demasiado tarde:
    http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
    http://www.codinghorror.com/blog/archives/000117.html

    Un saludo

  4. says

    Hola azkotoki , ya habia leido esos articulos, y tienen parte de razon.
    Creo que los procedimientos almacenados no son la solucion siempre, pero si el proyecto es grande son necesarios.
    Aumenta el rendimiento, no solo por el hecho de que estan compilados , si no por que viaja la minima informacion indispensable por la red desde el servidor de base de datos hasta el cliente que ejecuta la consulta.
    Esta claro que si la aplicacion es pequeña no merece la pena empezar a escribir código en lenguaje sql, pero si la aplicación es grande creo que puede ahorrar mucho tiempo, y sobre todo, después no vas a tener que ir buscando entre cien mil lineas de codigo el lugar donde pusistes la sentencia sql para hacer “X”.

    Esa es mi opinion.

    Un saludo a todos!

  5. azkotoki says

    Hola david, puede que mi anterior post pareciese un poco agresivo, pero bueno, no iba con ánimos de empezar ninguna flame-war ni nada por el estilo ;) era casi la hora de comer…

    Siempre he estado en contra de las supersticiones, y en el caso de los procedimientos almacenados puedo decir que es otro caso más. La gente evangeliza demasiado sin contrastar lo suficiente los argumentos, y esas cosas hacen mucho daño en el mundo de la informática.

    Mi experiencia con los procedimientos almacenados hasta la fecha no ha sido muy agradable que se diga, y resumo aquí mis puntos de vista:
    * Rendimiento: Se suele exagerar mucho este argumento, y en el caso de SQL Server aún más, ya que *NO* existe precompilación, sino que se cachea el plan de ejecución, al igual que con las consultas sql directas.
    * Separación de código: Se consigue precisamente lo contrario. La base de datos debería de ser sólo para almacenamiento, y nunca contener el comportamiento de la aplicación. Por otro lado, sin saberlo, estamos sacrificando la legibilidad de nuestro código, por un puñado de millonésimas de segundo. Un programador debería centrarse primero en la legibilidad, y después en el rendimiendo.
    * Reducción de carga de red: Aquí también la ganancia es irrisoria. Como media, calculo que se ahorran unos 300bytes por consulta, que no es nada comparado con los datos que devuelve la misma.
    * Seguridad: Si lo que pretendemos es crear una capa de abstracción vamos desencaminados. Asignando permisos basándonos en roles podemos conseguir el mismo efecto y de manera más eficiente.
    * Adiós portabilidad: Usando procedimientos almacenados ya nos podemos olvidar de la portabilidad de nuestra aplicación, porque tendremos que lidiar con las peculiaridades de cada dialecto de los sistemas de base de datos.

    Con esto no quiero decir que haya que abolir por completo los procedimientos almacenados. En escenarios donde el rendimiento sea crítico estoy a favor de utilizarlos, pero nunca más allá de ese propósito, ya que no se trataría de usar, sino de abusar.

    Si tu mayor inquietud es el mezclar código sql en tu aplicación, la generación dinámica de consultas o el mapeo objeto-relacional podrían tratarse de una opción razonable. Existen varias soluciones para ello en php: Doctrine, Propel, …

    Y nada más, gracias por haber llegado hasta aquí…

    ¡Un saludo!

  6. says

    Hola azkotoki …

    Estoy de acuerdo contigo en algunas cosas y en otras no tanto..

    Si se va a hacer una aplicación que no precise de mucho rendimiento, liarse a escribir sql es un estorbo.

    En cuanto a lo de la mantenibilidad… a mi me resulta mas comodo tener lso procedimeintos almacenados en la BD, e intento no escribir logica de negocio en los procedimientos, para que el comportamiento de la aplicación no dependa de estos.

    Quizás el ejemplo que haya puesto en el post no sea el más claro para hacer un buen uso de procedimeitno almacenado, pero lo hice rapidamente para iulustrar.

    Un saludo y gracias por el aporte!!

  7. nixon says

    Muchas gracias por el articulo, realmente estaba buscando como hacer un procedimiento almacenado y fue preciso.

    Muchisimas gracias

  8. John says

    Como se crearia un procedimiento almacenado que genere un backup de un bd

  9. CARO says

    Buenas tardes. tengo una consulta si requiero almacenar el valor d eun select en una variable, al menos en el sqlçyo lo hacia asi…

    set variable = select campo where codigo=’001′
    quiero hacer esto mismo en mysql dentro de un procedure, no me sale error, pero en “variable” no almacena nada y siemrpe sale “variable= NULL” a pesar q deberia arrojar un valor
    me podrian ayudar por favor?

  10. Lucio says

    David, se nota la dedicación que pones. Esto es una prueba de la excelente herramienta que constituye la red y la posibilidad de que gente como tu nos ayude desinteresadamente y pues nosotros, los beneficiados, no podemos menos que darte las gracias. Te escribo desde un pequeño pueblito de Argentina y lo que me diste con la nota no tiene precio.

  11. says

    Necesito saber porque los procedimientos de selección no se ejecutan desde el php……Lo explicare mejor lo que pasa es que si yo pongo un procedimiento almacenado asi:
    CREATE DEFINER=`root`@`localhost` PROCEDURE `MostrarPais`()
    BEGIN

    SELECT * FROM pais p;

    END
    cuando lo llamo en php no puedo motrar la consulta…..

  12. mauro says

    Tengo un procedimiento almacenado y recibe como paramtro el nombre de UNA TABLA y lo que quieri hacer es :
    CREATE TABLE (PARAMETRO).

    Como hago eso????
    cuando compilo

    CREATE DEFINER=`root`@`%` PROCEDURE `lectura`( in NAMETABLA char(50))
    BEGIN

    create table NAMETABLA
    END;

    Lo que me sale creando es una tabla que se llama NAMETABA, lo que yo quiero es que se cree una tabla pero con el nombre que le paso por parametro…..como hago eso?

  13. says

    Estoy ejecutando el procedimiento almacenado que ponen de ejemplo y dá este error…no sé por qué….

    Error

    consulta SQL:

    CREATE PROCEDURE introducePersona( IN edad int, IN nombre varchar( 50 ) ) BEGIN IF edad <18 THEN INSERT INTO ninos
    VALUES (
    edad, nombre
    );

    MySQL ha dicho: Documentación
    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

  14. says

    Bueno cada uno con sus ipininiones, pero lo mas importante es la portabilidad del sistema y la eficiencia con la que se manejara. he comenzado hace poco tiempo en php y me gustaria saber como es posible leer los mensajes de los procedimientos de mysql en php, ejemplo:
    yo tengo el siguiente procedimiento:
    DELIMITER $$

    DROP PROCEDURE IF EXISTS `BeFisi`.`usp_InsertUpdateLector`$$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_InsertUpdateLector`(in Condicion integer,in nLectorID integer, in nCodMatricula char(6),in nUsuario varchar(25),in nClave varchar(25), in nNombres varchar(50), in nApePat varchar(25), in nApeMat varchar(25),in nDireccion varchar(100), in nEmail varchar(50), in nFecNac datetime,in nTipo_LectorID integer,in nCarreraID integer)
    begin
    declare existe integer;
    if Condicion=1 then
    select count(Usuario) into existe from Lector where Usuario=nUsuario;
    if existe then
    select ‘el usuario ya existe’ as mensaje;
    else
    insert into Lector(CodMatricula, Usuario, Clave,Nombres, ApePat, ApeMat, Email,Direccion, FecNac,Tipo_LectorID,CarreraID) values(nCodMatricula, nUsuario, md5(lower(nClave)),nNombres, nApePat, nApeMat,nDireccion, nEmail, nFecNac,nTipo_LectorID,nCarreraID);
    select ‘los datos se insertaron correctamente’ as mensaje;
    end if;
    end if;

    if Condicion=2 then
    select count(LectorID) into existe from Lector where LectorID=nLectorID;
    if existe then
    update Lector set CodMatricula=nCodMatricula, Usuario=nUsuario, Clave=nClave,Nombres=nNombres, ApePat=nApePat, ApeMat=nApeMat,Direccion=nDireccion, Email=nEmail, FecNac=nFecNac,Tipo_LectorID=nTipo_LectorID,CarreraID=nCarreraID where LectorID=nLectorID;
    select ‘Los datos se modificaron con exito’ as mensaje;
    else
    select ‘el lector no existe’ as mensaje;

    end if;
    end if;

    end$$

    DELIMITER ;
    el cual quiero que php los interprete esos mensajes del procedimiento que se emitira en la validacion, para que me muestre en php

  15. Chava says

    Buen dia a todos, muy buen post. Muchas gracias.

    Para poder recibir datos que provienen de un procedimiento almacenado con php se debe utilizar las funciones mysqli_query().

    *Nota: Ver que la funcion es mysqli_query() y no mysql_query()… se agrega “i” latina despues de la palabra “mysql”
    Desde la conexion debe hacerlo de la misma manera con la funcion mysqli_connect(), de igual manera para recorrer la coleccion de datos debe hacerlo con mysqli_fecth_assoc() o el que ustedes decidan utilizar.

    Espero que sirva de ayuda el comentario.

    Saludos,