Criar Store Procedure

[Nadia Teles]

Boa tarde Pessoal.

Nunca criei store procedure no oracle e agora preciso fazê-lo.

Preciso que diariamente esta store procedure seja executada para limpar uma determinada tabela com base na data. Qual é a melhor maneira de fazer isto?

Como crio um job para esta execução?

Att.

Nádia

[pauloaleo]

Criação do JOB

Selecionar tudo

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => 'nome da proc;'
   ,next_date => to_date('11/08/2009 09:33:33','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'sysdate+(1)'
   ,no_parse  => FALSE
  );
END;
/

Criação da procedure

Selecionar tudo

CREATE OR REPLACE PROCEDURE OWNER.NOME DA PROC AS
  vVALORANTERIOR    NUMBER(15,2);
  vVALORATUAL       NUMBER(15,2);
  vVALORUSS         NUMBER(15,2);
  vVALORCLICORP     NUMBER(15,2);
  vVALORINDENIZAVEL NUMBER(15,2);
  iASSISTENCIA      NUMBER;

iCONTROLE INTEGER;

CURSOR ASV_FECHA IS 
         SELECT 
         FROM 
         WHERE
;

BEGIN
       
  iCONTROLE := 0;
  
  FOR R2 IN ASV_FECHA LOOP
  
        vVALORUSS         := 0;
        vVALORCLICORP     := 0;
        vVALORINDENIZAVEL := 0;
        vVALORATUAL       := 0;
        vVALORANTERIOR    := 0;
  
  
        SELECT 
        FROM
        WHERE
;
         
        SELECT
        FROM
        WHERE
;
            
        IF vVALORANTERIOR IS NULL THEN
          vVALORANTERIOR := 0;    
        END IF;  
        
        IF vVALORATUAL IS NULL THEN
          vVALORATUAL := 0;
        END IF;  



        IF vVALORANTERIOR = 0 THEN
          IF vVALORATUAL <= 2000 THEN
            vVALORINDENIZAVEL := vVALORATUAL;
            vVALORUSS         := 0;
            vVALORCLICORP     := 0;
          ELSE
            IF ((vVALORATUAL >= 2000) AND (vVALORATUAL < 3000)) THEN
              vVALORINDENIZAVEL := 2000;
              vVALORUSS         := (vVALORATUAL - 2000);
              vVALORCLICORP     := 0;
            ELSE
              IF (vVALORATUAL >= 3000) THEN
                vVALORINDENIZAVEL := 2000;
                vVALORUSS         := 1000;
                vVALORCLICORP     := (vVALORATUAL - 3000);
              END IF;
            END IF;
          END IF;
        ELSE
          IF (vVALORANTERIOR + vVALORATUAL) < 2000 THEN
            vVALORINDENIZAVEL := vVALORATUAL;
            vVALORUSS         := 0;
            vVALORCLICORP     := 0;
          ELSE 
            IF ((vVALORANTERIOR < 2000) AND ((vVALORANTERIOR + vVALORATUAL) >= 2000) AND ((vVALORANTERIOR + vVALORATUAL) < 3000) ) THEN
              vVALORINDENIZAVEL := vVALORATUAL - (vVALORATUAL - (2000 - (vVALORANTERIOR)));
              vVALORUSS         := (vVALORATUAL - vVALORINDENIZAVEL);
              vVALORCLICORP     := 0;
            ELSE
              IF ((vVALORANTERIOR < 2000) AND ((vVALORANTERIOR + vVALORATUAL) >= 3000)) THEN
                vVALORINDENIZAVEL := vVALORATUAL - (vVALORATUAL - (2000 - (vVALORANTERIOR)));
                vVALORUSS         := 1000;
                vVALORCLICORP     := vVALORATUAL  - (vVALORINDENIZAVEL + vVALORUSS);
              ELSE
                IF ((vVALORANTERIOR >= 2000) AND (vVALORANTERIOR < 3000) AND ((vVALORATUAL + vVALORANTERIOR) < 3000)) THEN
                  vVALORINDENIZAVEL := 0;
                  vVALORUSS         := vVALORATUAL;
                  vVALORCLICORP     := 0;  
                ELSE
                  IF (vVALORANTERIOR >= 2000) AND (vVALORANTERIOR < 3000) AND ((vVALORATUAL + vVALORANTERIOR) >= 3000) THEN
                    vVALORINDENIZAVEL := 0;
                    vVALORUSS         := vVALORATUAL - (vVALORATUAL - (3000 - (vVALORANTERIOR)));
                    vVALORCLICORP     := vVALORATUAL - vVALORUSS;  
                  ELSE
                    IF (vVALORANTERIOR >= 3000) THEN
                      vVALORINDENIZAVEL := 0;
                      vVALORUSS         := 0;
                      vVALORCLICORP     := vVALORATUAL;  
                    END IF;
                  END IF;
                END IF;
              END IF;
            END IF;
          END IF;
        END IF;


        UPDATE  SET
        WHERE
             ;
          
        COMMIT;
  END LOOP;
END;
/

Selecionar tudo

CREATE PUBLIC SYNONYM NOME DA PROC FOR OWNER.NOME DA PROC;


GRANT EXECUTE ON  OWNER . NOME DA PROC TO USUARIO;

GRANT DEBUG ON  OWNER.NOME DA PROD TO USUARIO;

Mando dois scripts para você de criação e job e de criação de procedure.

Veja se serve.

[Nadia Teles]

Muito obrigada pela ajuda. Já criei a procedure e o job e estão funcionando.

Mas ainda tenho dúvidas:

Minha procedure irá limpar 5 tabelas do banco de dados de acordo com a data que cada tabela contem, ou seja, permanecerão registros somente de 6 meses em diante. Veja exemplo abaixo. Coloquei todos os comandos dentro da procedure.

--Exemplo de um dos comandos
delete from Teste where data < to_char(sysdate - 180, 'RRRRMMDD');
commit;

Duvida 1:
Caso dê erro na limpeza de alguma tabela como o oracle trata isto? Ele executa denovo?

Duvida 2:
Como estou fazendo a limpeza com base na data, é possivel ocorrer um erro no oracle e limpar todos os registros da tabela? Isto não pode acontecer jamais. Devo usar rollback, me de um exemplo caso seja necessário.

Obrigada

Nádia

[jessica.ff]

Oi Nadia

Com relação a duvida 1:
isso vai depender do tratamento da sua exception, você diz que tipo de erro? você pode utilizar o when others ai qualquer erro entra nessa exception.

Com relação a duvida 2:
Acredito que não tenha como isso ocorrer

[fsitja]

Nadia, uma observação. Se você quer excluir apenas os registros anteriores a 6 meses, repare que 180 dias não são iguais a 6 meses.
Em vez de (sysdate - 180)

Selecionar tudo

delete from Teste where data < add_months(sysdate, -6)

A função add_months soma N meses, onde N é o segundo argumento e retorna a data correspondente. Usando -6 faz ele subtrair.

Sobre suas perguntas.
Qualquer exception faz o comando falhar e nada é deletado a menos que você execute o comando novamente e ele não dê exception.
Para controlar erros, coloque cada comando de DML (select, update ou delete) dentro de um bloco e controle a transação lá. Por exemplo:

Selecionar tudo

BEGIN
  DELETE FROM teste WHERE data < add_months(sysdate, -6);
EXCEPTION
WHEN OTHERS THEN
  ROLLBACK;
  dbms_output.put_line('Erro: ' || sqlcode || ' - ' || sqlerrm);
END;

Aí dá para deixar a imaginação voar: fazer log, dar "RAISE;" e disparar o erro para o bloco superior, executar o delete de novo com parâmetros diferentes, etc...

[Nadia Teles]

obrigada pela dica