DECLARE
VAR_INDEX NUMBER;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE TmpOids(
OID NUMBER CONSTRAINT PK_TmpOid PRIMARY KEY)';
--Criar uma sequence para inserir registro sequenciais nas tabelas, pois o oracle não tem auto increment
EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_LIMPACOLD
MINVALUE 1
MAXVALUE 999999
NOCYCLE';
EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_PARLIMCOLD
MINVALUE 1
MAXVALUE 999999
NOCYCLE';
-- Criar tabelas de logs
EXECUTE IMMEDIATE 'CREATE TABLE TBLOGLIMPDOCSCOLD(
IdLimpDocs NUMBER NOT NULL,
HorIniLim DATE NOT NULL,
DiaVidDoc NUMBER,
TotExcTbCold NUMBER,
TotExcTbColdEvt NUMBER,
TotLoopDocumentos NUMBER,
HorFimLim date,
StatusAtual varchar2(100))';
EXECUTE IMMEDIATE 'CREATE TABLE TBLOGMSGSCOLD(
IdLogMSg NUMBER NOT NULL,
IdLOGLIMPDOCS NUMBER NOT NULL,
HorLog date NOT NULL,
TxtMsg varchar2(200))';
EXECUTE IMMEDIATE 'CREATE TABLE TBPARLIMCOLD(
Id NUMBER CONSTRAINT PK_TbParlimColdId PRIMARY KEY,
DiaVidDoc NUMBER NOT NULL,
QtdRegLimLoo NUMBER NOT NULL,
TemAtrLoo TIMESTAMP NOT NULL,
TotDiaExc NUMBER NOT NULL,
MsgLog NUMBER DEFAULT 0,
ColdTbName varchar2(50) NOT NULL,
ColdEvtTbName varchar2(50) NOT NULL,
TpDoc varchar2(4) NOT NULL)';
END;
/
--===================================================================================================================
--===================================================================================================================
--=========================================PROCEDURE PARA GRAVAR LOG=================================================
create or replace
PROCEDURE ESCREVE_LOG(IdLimDocs NUMBER, TxtLog varchar2)AS
BEGIN
INSERT INTO TBLOGMSGSCOLD (IDLOGMSG,IdLOGLIMPDOCS, HorLog, TxtMsg) values (SEQ_LIMPACOLD.NEXTVAL,IdLimDocs, sysdate, TxtLog);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('DADOS NAO ENCONTRADOS NA TABELA DE PARAMETROS');
END;
--===================================================================================================================
--===================================================================================================================
--=====================================PROCEDURE DE LIMPEZA DAS TABELAS==============================================
create or replace
PROCEDURE LIMPA_REGISTROS_COLD (configId IN NUMBER )
AS
Var_DBName varchar2(50);
Var_IdLimDocs number;
Var_diasdevida number;
Var_totalregistro number;
Var_qtdloop number;
Var_CountLoop number;
Var_totalLimpezaLoop number;
Var_tempodeatraso varchar2(50);
Var_LogStr varchar(200);
Var_InicioExec timestamp;
Var_SQL VARCHAR2(2000);
Var_RowCount number;
Var_ColdTbName VARCHAR2(50);
Var_ColdEvtTbName VARCHAR2(50);
Var_TpDoc VARCHAR2(4);
Var_DateEmiColumn VARCHAR2(50);
Var_SqlCountOids VARCHAR(1000);
Var_SqlDelete VARCHAR(1000);
Var_MsgLog number;
BEGIN
SELECT SYS_CONTEXT('USERENV', 'INSTANCE_NAME') into Var_DBName FROM DUAL;
BEGIN
-- carrega as variáreis de acordo com a tabela TBPARLIMCOLD
SELECT DiaVidDoc,QtdRegLimLoo,TemAtrLoo,ColdTbName,ColdEvtTbName,TpDoc, MsgLog
INTO Var_diasdevida,Var_totalregistro,Var_tempodeatraso,Var_ColdTbName,Var_ColdEvtTbName,Var_TpDoc, Var_MsgLog
FROM TBPARLIMCOLD
WHERE Id = configId
ORDER BY 1;
-- insere a linha de log para a execução
INSERT INTO TBLOGLIMPDOCSCOLD (IdLimpDocs,HorIniLim,DiaVidDoc,TotExcTbCold,TotExcTbColdEvt,TotLoopDocumentos)
values (SEQ_LIMPACOLD.NEXTVAL,sysdate,Var_diasdevida,0,0,0);
-- captura o id da execução na tabela TBPARLIMCOLD
SELECT SEQ_LIMPACOLD.CURRVAL INTO Var_IdLimDocs FROM DUAL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('DADOS NAO ENCONTRADOS NA TABELA TBPARLIMCOLD');
END;
Var_InicioExec := to_timestamp(to_char(sysdate,'DD-MM-YY HH24:MI:SS'),'DD-MM-YY HH24:MI:SS');
--Elimina logs da última execução
EXECUTE IMMEDIATE 'TRUNCATE TABLE TBLOGMSGSCOLD';
IF Var_MsgLog = 1 then
ESCREVE_LOG(Var_IdLimDocs, 'Início da Limpeza');
END IF;
IF UPPER(Var_TpDoc) = 'NFE'
then Var_DateEmiColumn := 'IDE_DEMI';
ELSE
Var_DateEmiColumn := 'IDE_DHEMI';
END IF;
Var_qtdloop := 0;
-- início da captura de limites de informções
IF Var_MsgLog = 1 then
ESCREVE_LOG(Var_IdLimDocs, 'Verificado total de registros para limpezas');
END IF;
-- INICIO DA EXCLUSAO DE DOCUMENTOS EXPIRADOS
Var_qtdloop := 0;
UPDATE TBLOGLIMPDOCSCOLD SET StatusAtual = 'Inicio da limpeza de documentos expirados' where IdLimpDocs = Var_IdLimDocs;
Var_totalLimpezaLoop := Var_totalregistro;
Var_SqlCountOids := 'SELECT COUNT(oid) oidCount FROM '||Var_ColdTbName||' WHERE '||Var_DateEmiColumn||' < to_timestamp('''||to_char(Var_InicioExec,'DD-MM-YY HH24:MI:SS')||''',''DD-MM-YY HH24:MI:SS'') - '||to_char(Var_diasdevida)||' order by 1';
EXECUTE IMMEDIATE Var_SqlCountOids INTO Var_CountLoop;
IF Var_MsgLog = 1 then
Var_LogStr := 'Total de documentos expirados encontrados: ' || to_char(Var_CountLoop);
ESCREVE_LOG(Var_IdLimDocs, Var_LogStr);
END IF;
WHILE Var_CountLoop > 0 LOOP
-- limpa tabelas para seleção dos registros
IF Var_MsgLog = 1 then
ESCREVE_LOG(Var_IdLimDocs, 'Iniciado laço de limpeza da TbLogDocument');
END IF;
EXECUTE IMMEDIATE 'TRUNCATE TABLE TmpOids';
INSERT INTO TBLOGMSGSCOLD (IDLOGMSG,IdLOGLIMPDOCS, HorLog, TxtMsg) values (SEQ_LIMPACOLD.NEXTVAL,Var_IdLimDocs, SYSDATE, 'Capturados valores mínimos');
Var_qtdloop := Var_qtdloop + 1;
UPDATE TBLOGLIMPDOCSCOLD SET StatusAtual = 'Carga dos documentos expirados' where IdLimpDocs = VAR_IdLimDocs;
Var_SQL := 'INSERT INTO TmpOids SELECT * FROM ( SELECT Oid FROM '||Var_ColdTbName||' WHERE '||Var_DateEmiColumn||' < to_timestamp('''||to_char(Var_InicioExec,'DD-MM-YY HH24:MI:SS')||''',''DD-MM-YY HH24:MI:SS'') - '||to_char(Var_diasdevida)||' order by 1) WHERE ROWNUM <='||to_char(Var_totalregistro);
EXECUTE IMMEDIATE Var_SQL;
SELECT count(*) into Var_totalLimpezaLoop from tmpoids;
IF Var_MsgLog = 1 then
Var_LogStr := 'Inseriu registros na tmpLogsDocIds -> ' || to_char(Var_totalLimpezaLoop);
ESCREVE_LOG(Var_IdLimDocs, Var_LogStr);
END IF;
IF Var_totalLimpezaLoop = 0 then
IF Var_MsgLog = 1 then
ESCREVE_LOG(Var_IdLimDocs,'Encerrada a limpeza de documentos normais. Não existem documentos expirados no período de tempo especificado');
END IF;
EXIT;
END IF;
/*
INICIO DAS EXCLUSÕES
*/
-- remove os registros de eventos que estão vinculados aos tmpLogsDocIds que serão removidos
--DELETE FROM TbEvent WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds);
Var_SqlDelete := 'DELETE FROM '||Var_ColdEvtTbName||' WHERE OID IN (SELECT OID FROM TMPOIDS)' ;
execute immediate Var_SqlDelete;
Var_RowCount := SQL%ROWCOUNT;
UPDATE TBLOGLIMPDOCSCOLD SET TotExcTbColdEvt = TotExcTbColdEvt + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;
IF Var_MsgLog = 1 then
ESCREVE_LOG(Var_IdLimDocs, 'Deletou da tablea de eventos');
END IF;
Var_SqlDelete := 'DELETE FROM '||Var_ColdTbName||' WHERE OID IN (SELECT OID FROM TMPOIDS)';
execute immediate Var_SqlDelete;
Var_RowCount := SQL%ROWCOUNT;
UPDATE TBLOGLIMPDOCSCOLD SET TotExcTbCold = TotExcTbCold + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;
IF Var_MsgLog = 1 then
ESCREVE_LOG(Var_IdLimDocs, 'Deletou da tabela cold');
END IF;
EXECUTE IMMEDIATE Var_SqlCountOids INTO Var_CountLoop;
--WAITFOR DELAY @tempodeatraso
END LOOP;
EXECUTE IMMEDIATE 'TRUNCATE TABLE TmpOids';
UPDATE TBLOGLIMPDOCSCOLD SET TotLoopDocumentos = Var_qtdloop where IdLimpDocs = Var_IdLimDocs;
--FINAL DA EXCLUSAO DE DOCUMENTOS EXPIRADOS
UPDATE TBLOGLIMPDOCSCOLD SET HorFimLim = sysdate, StatusAtual = 'Limpeza Finalizada' where IdLimpDocs = Var_IdLimDocs;
IF Var_MsgLog = 1 then
ESCREVE_LOG(Var_IdLimDocs, 'Finalizou Limpeza');
END IF;
END LIMPA_REGISTROS_COLD;
|