problemilla Procedure

[andres18281]

Cordial Saludo..


Desde hace unos dias he tenido problemas con un procedimiento en mysql
en verdad llevo muchas muchas horas intentando saber cual es el problema que no funciona mi procedure, en el codigo veran entre comentarios un handler que pense que era por falta de excepciones pero igual no me funciono. Les dejo en contexto las tablas que use y unas inserciones de prueba y el procedimiento en este link de pastebin http://pastebin.com/FeKuvAQB si alguien podría ayudarme a entender cual es el problema le agradecería...

[gnzsoloyo]

Postea el código completo aquí en el foro.
No tenemos por qué andar descargando o buscando en otros sitios, cuando do en este tienes los recursos para mostrar lo que se necesita.

[andres18281]

Saludos, tengo el siguiente procedure el cual tengo problemas para que funcione.

Si requieren saber cuales son las tablas, les dejo el siguiente link http://pastebin.com/9XrKZhkH como anexo al problema, no lo publico aqui
porque es extenso y puede ser molesto para alguno.


USE almacen3;
-- Procedimiento con dos parametros de entrada, fecha inicio y fecha final el cual
-- selecciona todas las facturas que estan entre esas fechas. En otro loop hace una
-- busqueda en la tabla que esta relacionada con factura "ord_compra" y selecciona
-- la columna que tiene el nº de factura. En el segundo LOOP maneja un joins triple
-- con el fin de seleccionar el nombre, costo y codigo de los productos que fueron
-- vendidos. Por ultimo se crea una tabla en memoria donde almacenara los valores
-- que se consultaron con el fin de realizar informes mas sencillos.

Código MySQL:

delimiter //
CREATE PROCEDURE devuelve_informe_venta(IN fecha_inicio DATE, IN fecha_final DATE)
 
BEGIN    
   DECLARE fecha_factura DATE;
   DECLARE codigo_facura INT DEFAULT 0;
   DECLARE cod_producto INT DEFAULT 0;
   DECLARE costo DOUBLE DEFAULT 0.0;
   DECLARE total_registro INT DEFAULT 0;
   DECLARE nombre_producto VARCHAR(20);
   DECLARE nit INT DEFAULT 0;
   DECLARE crs_num_fact CURSOR FOR SELECT fact_cod_fac,fact_fecha FROM Factura
                                                WHERE fact_fecha >= fecha_inicio AND fact_fecha <= fecha_final;
   DECLARE crs_cod_prod CURSOR FOR SELECT ord_cod_zap,pro_nomb,cal_cost,pro_nit FROM ord_compra oc, Producto p,Calzado c
                                          WHERE ord_cod_fact = codigo_factura;
   DECLARE cursor_suma CURSOR FOR SELECT COUNT(*) FROM Factura WHERE fact_fecha >= fecha_inicio AND fact_fecha <= fecha_final;
 --  declare Continue Handler FOR SQLSTATE   'un problema encontrado 1064' as msn;
   DROP TABLE IF EXISTS informe;
   CREATE TABLE informe(num INT(5) AUTO_INCREMENT PRIMARY KEY,fecha DATE,cod_cal INT(10),nomb_cal VARCHAR(20),costo DECIMAL(6,2),nit INT(10))ENGINE = MEMORY;
 
   OPEN cursor_suma;
   FETCH cursor_suma INTO total_registro;
   OPEN crs_num_fact;
   BEGIN
         crs_num_fact_loop:LOOP -- LOOP donde recorrera las fechas que se filtraron
         FETCH crs_num_fact INTO cod_factura,fecha_factura;
         SET total_registro = total_registro + 1; -- contador de uno en uno.
         crs_cod_pro_loop:LOOP -- segundo LOOP donde realiza el la busqueda de nombre de productos, codigo y costo.
            OPEN crs_cod_prod;  -- abre el LOOP
            FETCH crs_cod_prod INTO cod_producto, nombre_producto,costo,nit;
                    INSERT INTO informe VALUES('',fecha_factura,cod_producto,nombre_producto,costo,nit);
                        IF total_registro >= crs_num_fact THEN -- finaliza la interaccion de ambos LOOP pues ya ha llegado al final del total de facturas a interar.
                          CLOSE csr_num_fact;
                          CLOSE csr_cod_prod;
                          iterate crs_num_fact_loop; -- cierra el primer LOOP
                          iterate crs_cod_pro_loop; -- cierra el segundo LOOP
                        END IF;
         END LOOP crs_cod_pro_loop;
         END LOOP crs_fact_loop;
    END;
 END;//
delimiter ;
 

[gnzsoloyo]

Por empezar, al menos hay un error conceptual: No puedes cerrar las iteraciones cruzadas.
Si abres LOOP 1 y LOOP 2, estando el segundo dentro del primero , siempre deben cerrarse o enviar a ciclo nuevo en el orden inverso, es decir respetando el anidamieento del proceso.
Me refiero puntualmente a esto:

Código MySQL:

crs_num_fact_loop:LOOP 
...
crs_cod_pro_loop:LOOP -- segundo LOOP donde realiza el la busqueda de nombre de productos, codigo y costo.
        ...
            IF total_registro >= crs_num_fact THEN 
              ...
              iterate crs_num_fact_loop; -- cierra el primer LOOP <-- Cierre cruzado. Orden incorrecto
              iterate crs_cod_pro_loop; -- cierra el segundo LOOP
            END IF;
END LOOP crs_cod_pro_loop;
END LOOP crs_fact_loop;
 

Adicionalmente, tienes esto:

Código MySQL:

OPEN cursor_suma;
   ...
   OPEN crs_num_fact;
   BEGIN
         crs_num_fact_loop:LOOP -- LOOP donde recorrera las fechas que se filtraron
         ...
            crs_cod_pro_loop:LOOP 
            OPEN crs_cod_prod;  -- abre el LOOP <.. No abre un loop, abre un CURSOR
            FETCH crs_cod_prod INTO cod_producto, nombre_producto,costo,nit;
                    INSERT INTO informe VALUES('',fecha_factura,cod_producto,nombre_producto,costo,nit);
                        IF total_registro >= crs_num_fact THEN 
                          CLOSE csr_num_fact; -- <-- Caso 1
                          CLOSE csr_cod_prod; -- <-- Caso 2
                          ...
                        END IF;
         END LOOP crs_cod_pro_loop;
         END LOOP crs_fact_loop;
    END;
 

Caso 1: Cierras un CURSOR que abriste fuera del loop. El loop fallará por el cursor cerrado
Caso 2: Cierras un CURSOR que abriste dentro del loop. Cada iteración vovlera a abrirlo y reeejecutar la query desde el principio. No tiene sentido.

Además no has dejado el HANDLER, por lo que cualquier fallo de cualquier tipo producirá problemas no administrados.

En las FAQs de MySQL yo postee un ejemplo detallado hace ya bastante tiempo, que te puede servir de referencia para ver mejor el tema. En cualquier caso, no te olvides que en los anidamientos se debe respetar la secuencia de aperturas y cierres.

Finalmente, un consejo: En muchísimos casos he visto que una secuencia de queries a tablas temporales son más eficientes que usar cursores. Yo estimo (no puedo hacer pruebas en este momento porque no estoy en casa), que tal vez rediseñando las consultas podrías llegar a obtener iguales resultados sin necesidad de ellos.

[andres18281]

Saludos, gracias por la la informacion, fue de mucha ayuda, sin embargo no logro
dominar este codigo. Intente hacer manejar un Handler pero no supe hacerlo doble por mi falta de experiencia en mysql.
Intente trabajar con un LOOP de tipo REPEAT pero el workBench me recomienda usar
Repeatable no se porque pero lei algo que las tablas cuando es de tipo InnoDB es con Repeatable.

Le pido mil disculpas por la molestia, por favor colaborarme nuevamente en este codigo.

Mil gracias..

Código MySQL:

delimiter //
CREATE PROCEDURE devuelve_informe_venta(IN fecha_inicio DATE, IN fecha_final DATE)
 
BEGIN    
   DECLARE fecha_factura DATE;
   DECLARE codigo_facura INT DEFAULT 0;
   DECLARE cod_producto INT DEFAULT 0;
   DECLARE costo DOUBLE DEFAULT 0.0;
   DECLARE total_registro INT DEFAULT 0;
   DECLARE acumulador INT DEFAULT 0;
   DECLARE nombre_producto VARCHAR(20);
   DECLARE nit INT DEFAULT 0;
  DECLARE accion INT DEFAULT 0;
  DECLARE v_last_row_fetched,v_last_row_fetched2 INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET  v_last_row_fetched=1;
  DECLARE crs_num_fact CURSOR FOR SELECT fact_cod_fac,fact_fecha FROM Factura
                                                WHERE fact_fecha >= fecha_inicio AND fact_fecha <= fecha_final;
 
  DECLARE crs_cod_prod CURSOR FOR SELECT ord_cod_zap,pro_nomb,cal_cost,pro_nit FROM ord_compra oc, Producto p,Calzado c
                                          WHERE ord_cod_fact = codigo_factura;
   DECLARE cursor_suma CURSOR FOR SELECT COUNT(*) FROM Factura WHERE fact_fecha >= fecha_inicio AND fact_fecha <= fecha_final;
   CREATE temporary TABLE informe (num INT(5) AUTO_INCREMENT PRIMARY KEY,fecha DATE,cod_cal INT(10),nomb_calVARCHAR(20),costo DECIMAL(6,2),nit INT(10))engine=memory;
   OPEN cursor_suma;
   FETCH cursor_suma INTO total_registro;
   OPEN crs_num_fact;
   OPEN crs_cod_prod;  -- abre el LOOP
    crs_cod_fact_loop:LOOP
         WHILE acumulador <= total_registro DO-- LOOP donde recorrera las fechas que se filtraron
                FETCH crs_num_fact INTO cod_factura,fecha_factura;
                OPEN crs_cod_prod;
                 SET acumulador = acumulador + 1; -- contador de uno en uno.
                crs_cod_pro_loop:LOOP --
                        FETCH crs_cod_prod INTO cod_producto, nombre_producto,costo,nit;
                        INSERT INTO informe VALUES('',fecha_factura,cod_producto,nombre_producto,costo,nit);
                        IF v_last_row_fetched = 1 THEN
                            Leave crs_num_fact_loop;
                        END IF;
                 END LOOP crs_cod_pro_loop;
         END WHILE;
    CLOSE csr_num_fact;
    CLOSE csr_cod_prod;
  DROP TABLE IF EXISTS informe;
 END;//
delimiter ;
 

[gnzsoloyo]

Una pregunta, antes de avanzar en el caso.
En el cursor "crs_cod_prod " tienes esta query:

Código MySQL:

SELECT ord_cod_zap, pro_nomb, cal_cost, pro_nit 
FROM ord_compra oc, Producto p, Calzado c 
WHERE ord_cod_fact = codigo_factura;
 

¿Cómo se relaciona la tabla "Calzado"? Porque como la tienes puesta, estás generando un producto cartesiano, lo que daría resultados completamente falsos...

Por otro lado, hacer un CURSOR para ejecutar esto:

Código MySQL:

SELECT COUNT(*) 
    FROM Factura 
    WHERE fact_fecha >= fecha_inicio 
        AND fact_fecha <= fecha_final
 

no tiene ningún sentido. Es un desperdicio de recursos de servidor

Es muchísimo más simple hacer:

Código MySQL:

SELECT COUNT(*) 
FROM Factura 
WHERE fact_fecha >= fecha_inicio 
        AND fact_fecha <= fecha_final 
INTO total_registro;
 

Adicionalmente, veo que ni te has molestado en leer el link que te pasé, ya que estás creando el HANDLER antes de los cursores.
Si te tomas el trabajo de leer el link verás este detalle:

2. No se pueden definir variables en el SP con posterioridad al CURSOR, ni se puede definir un HANDLER antes del CURSOR.

Esa nota está tomada del Manual de referencia de MySQL, donde se expresa:

Los cursores deben declararse antes de declarar los handlers, y las variables y condiciones deben declararse antes de declarar cursores o handlers.

Por favor, cuando se te pase un link, léelo. Lo ponemos para que leas alguna referencia que viene al tema.

Yo sigo analizando el SP porque creo que hay un modo más simple de lograr lo que pretendes.

[andres18281]

Saludos, gracias por las recomendaciones ya hize las correcciones y por lo menos no muestra errores en "compilacion" en el workBench.

Resulta que si es verdad, arroja el query que tengo en el joins con tres tablas unos
resultados aunque no falsos si repetitivos.

Logre quitar una variable acumuladora y el codigo quedo asi. Trato de corregir el query para mirar resultados y sigo con el otro procedimiento que me falta el cual consiste en que la tabla en memoria almacene los query y realize informes muchos mas faciles.

Código SQL:

delimiter //
CREATE PROCEDURE devuelve_informe_venta(IN fecha_inicio DATE, IN fecha_final DATE)
 
BEGIN    
   DECLARE fecha_factura DATE; 
   DECLARE codigo_factura INT DEFAULT 0;
   DECLARE cod_producto INT DEFAULT 0;
   DECLARE costo DOUBLE DEFAULT 0.0;
   DECLARE total_registro INT DEFAULT 0;
   DECLARE acumulador INT DEFAULT 0;
   DECLARE nombre_producto VARCHAR(20); 
   DECLARE nit INT DEFAULT 0;
  DECLARE v_control bool DEFAULT FALSE;
  
   DECLARE crs_cod_fact CURSOR FOR SELECT fact_cod_fac,fact_fecha FROM Factura 
                        WHERE fact_fecha >= fecha_inicio AND fact_fecha <= fecha_final;
   
   DECLARE crs_cod_prod CURSOR FOR SELECT ord_cod_zap,pro_nomb,cal_cost,pro_nit FROM ord_compra oc, Producto p,Calzado c
                      WHERE ord_cod_fact = codigo_factura;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET  v_control = TRUE;
   CREATE TEMPORARY TABLE informe (num INT(5) AUTO_INCREMENT PRIMARY KEY,fecha DATE,cod_cal INT(10),nomb_cal VARCHAR(20),costo DECIMAL(6,2),nit INT(10))engine=memory;
 
    crs_cod_fact_loop:loop
        fetch crs_cod_fact INTO codigo_factura,fecha_factura;
          IF v_control THEN
              leave crs_cod_fact_loop;
              close crs_cod_fact;
          END IF;         
         crs_cod_pro_loop:LOOP -- segundo LOOP donde realiza el la busqueda de nombre de productos, codigo y costo.
            OPEN crs_cod_prod;
            fetch crs_cod_prod INTO cod_producto, nombre_producto,costo,nit;
            INSERT INTO informe VALUES('',fecha_factura,cod_producto,nombre_producto,costo,nit);
            IF v_control THEN   
                close crs_cod_prod;
                leave crs_cod_pro_loop;
            END IF;
         END LOOP crs_cod_pro_loop;
    END LOOP crs_cod_fact_loop; 
  DROP TABLE IF EXISTS informe;
 END;// 
delimiter ;