Samuele Locatelli
75 lines
1.9 KiB
SQL
75 lines
1.9 KiB
SQL
CREATE OR REPLACE PROCEDURE stp_removeOldData(
|
|
IN day_keep INT
|
|
)
|
|
READS SQL DATA
|
|
BEGIN
|
|
|
|
SET @adesso = ADDDATE(CURRENT_TIMESTAMP, INTERVAL -1 SECOND);
|
|
SET @dt_min = ADDDATE(CURRENT_TIMESTAMP, INTERVAL -day_keep DAY);
|
|
|
|
START TRANSACTION;
|
|
|
|
-- AlarmLog
|
|
DELETE
|
|
FROM AlarmLog
|
|
WHERE DtRif <= @dt_min;
|
|
INSERT INTO TaskExec(DtRif, CodTask, Result)
|
|
SELECT CURRENT_TIMESTAMP AS DtRif
|
|
,'Cleanup AlarmLog' AS CodTask
|
|
,CONCAT('Deleted ', CAST(ROW_COUNT() AS NCHAR), ' rows') AS Result;
|
|
|
|
-- AlarmRec
|
|
DELETE
|
|
FROM AlarmRec
|
|
WHERE DtStart <= @dt_min;
|
|
INSERT INTO TaskExec(DtRif, CodTask, Result)
|
|
SELECT CURRENT_TIMESTAMP AS DtRif
|
|
,'Cleanup AlarmRec' AS CodTask
|
|
,CONCAT('Deleted ', CAST(ROW_COUNT() AS NCHAR), ' rows') AS Result;
|
|
|
|
-- DataLog
|
|
DELETE
|
|
FROM DataLog
|
|
WHERE DtRif <= @dt_min;
|
|
INSERT INTO TaskExec(DtRif, CodTask, Result)
|
|
SELECT CURRENT_TIMESTAMP AS DtRif
|
|
,'Cleanup DataLog' AS CodTask
|
|
,CONCAT('Deleted ', CAST(ROW_COUNT() AS NCHAR), ' rows') AS Result;
|
|
|
|
-- ProdLog
|
|
DELETE
|
|
FROM ProdLog
|
|
WHERE DtRif <= @dt_min;
|
|
INSERT INTO TaskExec(DtRif, CodTask, Result)
|
|
SELECT CURRENT_TIMESTAMP AS DtRif
|
|
,'Cleanup ProdLog' AS CodTask
|
|
,CONCAT('Deleted ', CAST(ROW_COUNT() AS NCHAR), ' rows') AS Result;
|
|
|
|
-- StatusLog
|
|
DELETE
|
|
FROM StatusLog
|
|
WHERE DtRif <= @dt_min;
|
|
INSERT INTO TaskExec(DtRif, CodTask, Result)
|
|
SELECT CURRENT_TIMESTAMP AS DtRif
|
|
,'Cleanup StatusLog' AS CodTask
|
|
,CONCAT('Deleted ', CAST(ROW_COUNT() AS NCHAR), ' rows') AS Result;
|
|
|
|
-- TaskExec
|
|
DELETE
|
|
FROM TaskExec
|
|
WHERE DtRif <= @dt_min;
|
|
INSERT INTO TaskExec(DtRif, CodTask, Result)
|
|
SELECT CURRENT_TIMESTAMP AS DtRif
|
|
,'Cleanup TaskExec' AS CodTask
|
|
,CONCAT('Deleted ', CAST(ROW_COUNT() AS NCHAR), ' rows') AS Result;
|
|
|
|
|
|
COMMIT;
|
|
|
|
-- seleziono risultato
|
|
SELECT *
|
|
FROM TaskExec
|
|
WHERE DtRif > @adesso;
|
|
|
|
END
|