Caso Prático: Carga de Tabelas

Pois bem, hoje algo técnico. Falarei um pouco sobre banco de dados.

Assunto: carga de tabelas entre sistemas de banco de dados (interfaces). Vamos supor que a tabela pedidos faça parte da modelagem do sistema e por motivos de segurança, não temos acesso direto à ela, ou seja, não podemos inserir, atualizar ou deletar registros. Muitas vezes a tabela não é abastecida diretamente, existe um intermediário e é o que faremos agora. O uso deste é para: se o sistema cair, não ficar a dúvida de "atualizou ou não atualizou?" e para fins de auditoria.

Bom, para isso, mais duas tabelas serão criadas. Primeiro a tabela tmp_pedidos, que é exatamente igual à tabela pedidos. A segunda é a tabela log, que consta alguns campos necessários para que a procedure seja executada. A idéia é simples:
- Alguma operação (inserir, atualizar ou deletar) é executada na tabela tmp_pedidos;
- Uma trigger captura essas informações e guarda na tabela log;
- Uma procedure, lê a tabela log, busca as informações na tabela tmp_pedidos com as informações do log e abastace a tabela pedidos;

Esse tipo de implementação é usada muito, então ao trabalho.

Primeiro, iremos criar as tabelas e suas chaves.

create table tmp_pedidos (num number, data date, num_func varchar2(10));
create table pedidos (num number, data date, num_func varchar2(10));
create table log (id number, chave number, tipo varchar2(1), data date, processado varchar2(1));

Na tabela log, a columa chave recebe o valor de num da tabela tmp_pedidos, tipo é I/A/E (inserir, atualizar ou excluir) e o campo processado S/N (que eu representarei com 0 ou 1).

Agora criaremos as chaves.

alter table tmp_pedidos add constraint pk_tmp_pedidos primary key (num);
alter table pedidos add constraint pk_pedidos primary key (num);
alter table log add constraint pk_log primary key (id);

OK. Agora vamos criar a trigger responsável em abastecer a tabela log a partir dos dados inseridos, atualizados ou deletados da tabela tmp_pedidos.

create or replace trigger trg_evento_pedidos
after insert or update or delete on tmp_pedidos
for each row
begin

if inserting then insert into log values (seq_evento.nextval, :new.num, 'I',sysdate,0);
end if;
if updating then insert into log values (seq_evento.nextval, :new.num, 'A',sysdate,0);
end if;
if deleting then insert into log values (seq_evento.nextval, :old.num, 'D',sysdate,0);
end if;
end;
/

Lembre-se que uma trigger é disparada automaticamente. Aqui é disparada depois de inserir, atualizar ou deletar registro na tabela tmp_pedidos. Feito isso, insira alguns registros na tabela tmp_pedidos.

into tmp_pedidos values (1,sysdate, 2);
into tmp_pedidos values (2,to_date('18/09/07','dd/mm/YY'), 2);

Faça uma busca na tabela log.

select * from log;

Nota-se as chaves que são os num da tabela tmp_pedidos e o tipo I, de inserção. Agora faremos a procedure que lê a tabela log, busca as informações na tabela tmp_pedidos e abastece a tabela pedidos.

create or replace procedure sincroniza is
tmp_num number;
tmp_data date;
tmp_func varchar2(10);
begin
for dados in(select * from log where processado=0 order by (data))loop
if (dados.tipo = 'I') then
begin
select num,data,num_func into tmp_num,tmp_data,tmp_func from tmp_pedidos where num = dados.chave;
insert into pedidos values(tmp_num, tmp_data, tmp_func);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
end;
elsif (dados.tipo = 'A') then
begin
select num,data,num_func into tmp_num, tmp_data, tmp_func from tmp_pedidos where num = dados.chave;
update pedidos set data = tmp_data, num_func = tmp_func, num = tmp_num where num = tmp_num;
end;
elsif (dados.tipo = 'D') then
begin
delete from pedidos where num = dados.chave;
end;
end if;
update log set processado = 1 where id = dados.id;
end loop;
end;

Pois bem. Está pronto. A procedure lê a tabela log buscando pelo o que ainda não foi processado, ou seja, processado = 0. Com a chave dessa busca, a procedure procura pelos dados na tabela tmp_pedidos onde num seja igual a chave. Depois disso, é feita a verificação de qual operação é feita e executada a rotina.

Note o exception. Sua função é para que se ocorra uma inserção e logo depois exclusão, não dê erro. Se não for colocado, assim que a procedure procurar pelos dados de um registro não encontrará, pois o mesmo já foi excluído.