Procedures y Cursores en Mysql

Un Stored Procedure o Procedimiento Almacenado es un programa (o función) que se almacena físicamente en una base de datos.Su implementación varía de un manejador de bases de datos a otro. En la mayoría de los casos, no obstante, los stored procedures permiten definir una API (capa de abstracción) para que el gestor de bases de datos interactúe con tablas u otros objetos de la base de datos, en vez de tener un cliente de la aplicación interactuando directamente.

Aunque la mayoría de los gestores de bases de datos existentes brindan soporte para estos procedimientos, existe discusión entre los desarrolladores acerca de las ventajas de su uso.

Por eso empezaremos este post definiendo las ventajas y desventajas del uso de Stored Procedures.

Cabe mencionar que el uso de procedimientos almacenados depende muchas veces de la arquitectura del sistema y del alcance del mismo.

Ventajas

Es notorio que existen más ventajas que desventajas en el uso de procedimientos.

• 
  
  Es ejecutado directamente en el motor de bases de datos, el cual usualmente corre en un servidor separado. Como tal, posee acceso directo a los datos que necesita manipular y sólo necesita enviar sus resultados de regreso al usuario, deshaciéndose de la sobrecarga resultante de comunicar grandes cantidades de datos salientes y entrantes.
• 
  
  Encapsulamiento de un proceso complejo o que podría requerir la ejecución de varias consultas SQL, tales como la manipulación de un gran conjunto de datos para producir un resultado resumido. Esto reduce la necesidad de codificar lógica innecesaria en el programa cliente y tener un mejor manejo de la capa de negocio de nuestra aplicación.
• 
  
  Centralización de la definición, al formar parte de la base de datos los procedimientos almacenados están en un lugar centralizado y pueden ser ejecutados por cualquier aplicación que tenga acceso a la misma. Evitando la excesiva declaración de procedimientos de ejecución en el gestor de bases de datos; ya que puede ser perjudicial para el rendimiento del servidor.
• 
  
  Reducción del tráfico de red, una sentencia formada por decenas, cientos o incluso miles de líneas de código SQL puede escribirse como un procedimiento almacenado en el servidor y ejecutarse simplemente mediante el nombre de dicho procedimiento.

Desventajas

Como lo mencione en un principio el uso de los procedures depende mucho de la arquitectura y alcance del sistema. En una arquitectura multi-base de datos en donde la información deberá ser manejada en distintos motores de base de datos sea MySql, Postgresql, Oracle, Sql Server, etc.O en una arquitectura en donde querramos migrar después a otro motor de base de datos. No conviene el uso de procedures de ninguna manera.

• 
  
  Reducción de la escalabilidad: Los procedimientos almacenados nos esclavizan al motor de base de datos. Para migrar de un gestor de base de datos con muchos procedimientos almacenados a otro, se deberá reescribir casi la totalidad de los mismos. Esto se debe, principalmente, a que los lenguajes de procedimientos almacenados de distintos fabricantes no son compatibles entre sí.

Teniendo en cuenta las ventajas y desventajas es aconsejable no abusar de los procedimientos almacenados y utilizarlos sólo cuando su aplicación sea realmente efectiva y el alcance del sistema este bien definido. Con respecto al uso de los procedures siempre habrá controversia entre los desarrolladores.


Implementación y Sintaxis en Mysql 5

Desde MySQL 5 los procedimientos almacenados empezaron a ser soportados.
Son implementados desde la aplicación mediante CALL nombre_procedure (…parametros).

Voy a poner un par de ejemplos definiendo la sintaxis de creación de un procedure y dentro de él el uso de cursores en mysql. La finalidad de estos procedures es lo de menos, lo que nos interesa es la sintaxis para poder desarrollar nuestros propios procedimientos, con lógica a medida de nuestras necesidades.

Nota: El uso del cursor sirve para recorrer uno por uno los registros del resultado de la select y poder hacer operaciones con ella. Es como recorrer una lista con un for java y hacer operaciones con cada registro.

Ejemplo 1.- Definimos un procedure con 2 parámetros de entrada, variables que nos permitirán el desarrollo de nuestra lógica, el cursor que nos permitirá recorrer uno a uno el resultado de la select, y el retorno de una cadena que será procesada posteriormente por el programa cliente.

-- en esta línea eliminamos el procedure de la base de datos si existe.
DROP PROCEDURE IF EXISTS `nombreBD`.`cur_pagination_SP`;
DELIMITER \\

--definiendo la creación del procedure con 2 parametros de entrada 
CREATE PROCEDURE `nombreBD`.`cur_pagination_SP` ( IN v_id_taller INTEGER,
                                                  IN v_pagination INTEGER )
    READS SQL DATA
BEGIN
--declaración de variables
DECLARE l_last_row INT DEFAULT 0;
DECLARE v_ape_pat VARCHAR(255);
DECLARE contador int default 0;
DECLARE rpt VARCHAR(300) default '';

--declaración del cursor de nombre ´cursor1´
DECLARE cursor1 CURSOR FOR
SELECT u.ape_pat 
FROM tb_usuario u
WHERE NOT EXISTS (
       SELECT * FROM tb_cabecera ci
       WHERE  u.id_usuario = ci.id_usuario and 
              ci.id_capacitacion = v_id_taller
) AND u.is_deleted !=1 and u.perfil in ('E','D')
ORDER BY u.ape_pat ASC;

--Flag que permitirá saber si existen más registros por recorrer
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row=1;

--abrimos el cursor para empezar a recorrerlo
OPEN cursor1;
c1_loop: LOOP
    -- cada registro se le otorga a la variable 'v_ape_pat'
    FETCH cursor1 INTO v_ape_pat;
        IF (l_last_row=1) THEN
            LEAVE c1_loop;
        END IF;

        --lógica propia de este procedure
        if contador%v_pagination = 0 then
          SET rpt = CONCAT(rpt,substring(v_ape_pat,1,1));
        end if;

        SET contador = contador + 1;

END LOOP c1_loop;

--cerramos el cursor y retornamos el dato que nos interesa en este caso la variable 'rpt'
CLOSE cursor1;
SELECT rpt;

END\\
DELIMITER;

Ejemplo 2.- A diferencia del primer ejemplo veremos como crear dos cursores dentro del mismo procedure.

DROP PROCEDURE IF EXISTS `nombreBD`.`porcentaje_SIS_CAP`;
DELIMITER \\

CREATE PROCEDURE `nombreBD`.`porcentaje_SIS_CAP` ( IN v_id_cabecera INTEGER)
    READS SQL DATA
BEGIN

DECLARE l_last_row INT DEFAULT 0;
DECLARE v_cantidad INT DEFAULT 0;
DECLARE v_total INT DEFAULT 0;
DECLARE v_nota VARCHAR(5) default '';
DECLARE rpt VARCHAR(150) default '';

--declaración del primer cursor
DECLARE cur_total CURSOR FOR
SELECT
    COUNT(*) AS 'CANTIDAD'
FROM tb_cabecera a
INNER JOIN tb_inscripcion c ON a.id_cabecera=c.id_cabecera
WHERE a.id_cabecera = v_id_cabecera
GROUP BY c.nota;

--declaración del segundo cursor
DECLARE cur_calculo CURSOR FOR
SELECT
    c.nota_evalua  AS "NOTA",COUNT(*) AS 'CANTIDAD'
FROM tb_cabecera a
INNER JOIN tb_inscripcion c ON a.id_cabecera=c.id_cabecera
WHERE a.id_cabecera = v_id_cabecera
GROUP BY c.nota
ORDER BY c.nota_evalua ASC;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row=1;

OPEN cur_total;
REPEAT
    FETCH cur_total INTO v_cantidad;
    IF not l_last_row THEN
       SET v_total = v_total + v_cantidad;
    END IF;   
UNTIL l_last_row END REPEAT;

CLOSE cur_total;

SET v_cantidad = 0;
set l_last_row = 0;

OPEN cur_calculo;
c2_loop: LOOP
    FETCH cur_calculo INTO v_nota,v_cantidad;
    IF l_last_row THEN
        LEAVE c2_loop;
    END IF;
SET rpt = CONCAT(rpt,'&',v_nota,'-',v_cantidad,'-',(v_cantidad/v_total)*100,'-',v_total);

END LOOP c2_loop;
CLOSE cur_calculo;

SELECT rpt;

END\\
DELIMITER;

Espero que les pueda servir este post para la mejor toma de decisiones, al momento de definir la arquitectura de su aplicación. Saludos a todos.... se despide su amigo edisonjc7 .. no sin antes dejarles un regalillo para los amantes del mysql .. jeje xD..

Un regalillo para todos los seguidores de Mysql

La biblia del Mysql - Download

Manual de Referencia en Español - Download