Procedimientos almacenados mysql

[GrupoC]

Buenas,

Tengo instalado MySQL 5.0.51a y estoy intentando hacer un procedimiento almacenado que elimine registros si su fecha de creación superan la semana... Pero no me funciona pegando el código en phpMyAdmin. El código es:

delimiter //

Código MySQL:

CREATE procedure eliminaPrensaExterna()
BEGIN
DECLARE hecho INT DEFAULT 0;
DECLARE fecha,fechaActual DATE;
SELECT CURDATE() INTO fechaActual;
DECLARE cursor_pe CURSOR FOR SELECT distinct(fchPublicacion) FROM prensa_externa WHERE estado=0 OR estado=2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET hecho=1;
OPEN cursor_pe;
REPEAT
   FETCH cursor_pe INTO fecha;
   IF NOT hecho THEN
      IF fechaAcutal+0>fecha+7 THEN
         DELETE FROM prensa_externa WHERE fchPublicacion=fecha AND (estado=0 OR estado=2);
      END IF;
   END IF;
UNTIL hecho END REPEAT;
CLOSE cursor_pe;
END;
 
//
 

¿Alguien sabe si falla algo, o simplemente esta versión de MySQL no soporta procedimientos almacenados?

Muchas gracias!

[gnzsoloyo]

Los procedimientos almacenados se implementaron precisamente para la versión 5.0.x, por lo que tu problema no está allí: está en el código que tiene un defecto básico: No puedes leer y borrar en la misma tabla al mismo tiempo...

Me explico: Un CURSOR abre una tabla en modo de lectura exclusivo, por lo que automáticamente está bloqueada para toda otra acción, excepto dirty reads. Este bloqueo dura mientras el CURSOR esté abierto, porque en realidad estás recorriendo los registros uno a uno.
Como la tabla está bloqueada, simplemente no puedes borrar un registro en base a una lectura de la misma tabla, y menos aún el registro sobre el que en ese momento estás parado. Sería igual que cortar la rama del árbol en la que te estás sosteniendo...
Tu segundo problema es que esta acción debería estar devolviendo un error de ejecución, que tu no estás capturando. Es de buena programación capturar todos los mensajes de error que se producen, especialmente en la etapa de desarrollo.

Además de estas consideraciones, ese procedimiento podría ser más simple, y no estás usando en algunos casos las sentencias correctas para las acciones a realizar.
Por lo pronto, ten en cuenta que el borrado de los registros tiene una condición bastante simple: estado debe ser 0 ó 2 y la fecha de publicación, la de hace siete días. Las dos cosas tiene forma simples de escribirse, y para las fechas es siempre mejor usar funciones que hacer operaciones aritméticas.
Eso sería simplemente:

Código MySQL:

DELETE FROM prensa_externa 
WHERE fchPublicacion = SUB_DATE(CURDATE(), INTERVAL 7 DAY) AND estado IN(0,2);
 

o bien:

Código MySQL:

DELETE FROM prensa_externa 
WHERE ADD_DATE(fchPublicacion, INTERVAL 7 DAY) = CURDATE() AND estado IN(0,2);
 

Si lo quieres en un SP:

Código MySQL:

DELIMITER$$
DROP PROCEDURE IF EXISTS eliminaPrensaExterna()$$
CREATE PROCEDURE eliminaPrensaExterna()
BEGIN
    DECLARE fechaActual DATE;
    SET = fechaActual = CURDATE();
    DELETE FROM prensa_externa 
    WHERE fchPublicacion = SUB_DATE(fechaActual, INTERVAL 7 DAY) AND estado IN(0,2);
END$$
 

Toma nota de que para asignar valores a las variables no necsitas usar SELECT INTO, sino SET. SELECT INTO se usa fundamentalmente si los valores provienen de un SELECT,, pero no si son producto de funciones u operaciones con datos ya existentes.

[GrupoC]

Buenas,

Muchísimas gracias por tu ayuda.

Tengo preguntas para ti :D.

Lo primero, no te falta razón :S... No puedo borrar algo que saco de un cursor... ¿Hay alguna función para borrar el elemento anterior de la posición actual del cursor?

¿Cómo capturo los errores? Pon un ejemplo, por favor.

Ya sé que se podía hacer fácilmente con una consulta y ejecutarla con PHP, pero es una acción que se ejecutará muy a menudo... Por lo que es preferible guardarla como procedimiento almacenado, ¿no?

Ha quedado clarísimo lo de SET y no SELECT.

Muchísimas gracias, eres un genio.

[gnzsoloyo]

¿Cómo capturo los errores? Pon un ejemplo, por favor.

La captura de errores se hace por programación, por lo que dependerá del lenguaje usado para la aplicación que vayas a diseñar.

En el caso de los SP, la ejecución misma del SP debe devolver un error, que suelen aparecer como mensajes en la misma interfase que usas para las pruebas (phpMyAdmin, en tu caso, yo prefiero usar MySQL Workbench). COmo los SP no son debuggeables en MySQL, la única forma de obtener los errores es con HANDLERs, controlando los diferentes SQLSTATE, pero puede volverse muy engorroso.

Respecto de los cursores, estos son grandes consumidores de procesador y tiempo, por lo que son recomendables si y sólo si debes forzosamente verificar o realizar operaciones con un conjunto dado de datos en cada registro. Usarlos para localizar un conjunto de registros que cumplen una condición, pero no para operar con los datos, a mi entender no es una buena práctica.

Mi recomendación es que si quieres obtener un conjunto de registros que luego serán borrados, es más simple y rápido hacerlo con tablas temporales. Además, las tablas temporales se borran solas al cerrar la conexión, por lo que no molestan.

Así pues, hay dos propuestas: Una es la que ya te hice:

Código MySQL:

DELIMITER$$
DROP PROCEDURE IF EXISTS eliminaPrensaExterna()$$
CREATE PROCEDURE eliminaPrensaExterna()
BEGIN
    DECLARE fechaActual DATE;
    SET = fechaActual = CURDATE();
    DELETE FROM prensa_externa 
    WHERE fchPublicacion = SUB_DATE(CURDATE(), INTERVAL 7 DAY) AND estado IN(0,2);
END$$
 

La otra sería con tablas temporales:

Código MySQL:

DELIMITER$$
DROP PROCEDURE IF EXISTS eliminaPrensaExterna()$$
CREATE PROCEDURE eliminaPrensaExterna()
BEGIN
    DROP TEMPORARY TABLE IF EXISTS t_prensa_externa t_prensa_externa ;
    CREATE TEMPORARY TABLE t_prensa_externa LIKE prensa_externa;
    INSERT INTO t_prensa_externa
    SELECT * 
    FROM prensa_externa 
    WHERE fchPublicacion = SUB_DATE(CURDATE(), INTERVAL 7 DAY) AND estado IN(0,2);
    DELETE FROM prensa_externa 
    WHERE id_prensa_externa IN (SELECT id_prensa_externa  FROM t_prensa_externa);
    SELECT * 
    FROM t_prensa_externa;
END$$
 

Este SP devuelve como salida una tabla conteniendo los registros que fueron borrados de la tabla. Desaparecerá cuando la conexión del usuario se cierre.

Postdata: Los códigos que te había puesto antes tenían algunos errores de sintaxis.

[GrupoC]

Muchas gracias gnzsoloyo... El stored procedure funciona perfectamente, pero tengo un problema al ejecutarlo...

Lo ejecuto de la siguiente forma:

Código MySQL:

call eliminaPrensaExterna();
 

Pero no funciona... Me devuelve el error: #1312 - PROCEDURE bbdd.eliminaPrensaExterna can't return a result set in the given context

¿Me puedes ayudar? He buscado, y hay gente que lo ha solucionado cambiando la sentencia de conexión a BBDD...

Muchas gracias.

[gnzsoloyo]

#1312 - PROCEDURE bbdd.eliminaPrensaExterna can't return a result set in the given context

Este es un tema para el Foro de PHP, pero te puedo dar un tip previo: Eso sucede cuando usas las funciones MySQL de PHP como mysql_query(), y lo que debes usar es el modelo OO, que es el MySQLI.
El problema sucede porque la tabla devuelta por una consulta debe ser una tabla real en el caso del mysql_query(), y no es el caso, porque la fuente de datos no es una consulta en buffer de consultas sino que proviene de un stored procedure. En ese caso, la otra librería (MYSQLI) si puede operar contra SP.

SI el lenguaje fuese VB.Net o C#.net, por ejemplo, este problema no existiría.

[GrupoC]

Gracias gnzsoloyo, pero el problema está en que este SP lo estoy ejecutando directamente sobre phpMyAdmin...

Miraré lo que comentas sobre MySQLi.

Gracias de nuevo.

[gnzsoloyo]

Es muy probable que phpMyAdmin esté usando internamente precisamente esa librería. No te olvides que es una interfase programada en PHP...

[GrupoC]

Solucionado, muchas gracias gnzsoloyo.

El problema era un bug de phpMyAdmin, pero he actualizado y ya está :D

Gracias por tu inestimable ayuda!