Melhorando a qualidade de dados nos bancos de dados

por Márcio Tognere há 7 anos 5 min de leitura

A utilização de dados contidos em uma base de dados é algo trivial em qualquer empresa. É fundamental que determinados conjuntos de dados estejam corretos, para que sua manipulação tenha real utilidade. Talvez isso seja óbvio. Se os dados estão contidos na base, temos que partir do princípio que estão corretos. Certo? Nem sempre.

É comum várias aplicações compartilharem da mesma base de dados. É primordial que os programadores realizem as validações necessárias para impedir a inserção ou atualização de dados incorretos. Nem sempre os programadores tem esse cuidado. Nesse contexto, é importante encontrarmos mecanismos para melhorar a qualidade dos dados.

Dificilmente essa preocupação vira antes da criação de uma tabela. Certamente, sua análise identificou que existem dados incorretos, fruto da falta de validação em um dos campos de alguma aplicação. É preciso solucionar esse problema para garantir que futuramente sejam armazenados a maior quantidade de dados corretos.

“Seja criterioso com a qualidade. Algumas pessoas não estão acostumadas com um ambiente onde a excelência é esperada.” Steve Jobs

Realizaremos as manutenções e novas implementações para melhorar a qualidade dos dados na base. Irei propor uma solução para sanar o problema explanado.

Vou elucidar esse problema da seguinte forma. Existe uma tabela no banco de dados muito utilizada. A tabela "cliente". Conforme o código abaixo:

Criação da tabela


CREATE TABLE cliente(
  codcliente NUMBER(10,0) NOT NULL,
  nome       VARCHAR2(30) NOT NULL,
  cpf        CHAR(11)     NULL,
  cnpj       CHAR(14)     NULL,
  CONSTRAINT pk_cliente
    PRIMARY KEY(codcliente)
);

Várias aplicações diferentes utilizam essa mesma estrutura. Em alguma aplicação, um programador não validou os campos "cpf" e "cnpj", ou validou de forma parcial, permitindo o usuário modificar a validação no frontend e submeter o formulário com "cpf" ou "cnpj" inválidos.

See the Pen Cadastro de Cliente by Márcio (@marciotognere) on CodePen.

O resultado da não validação (ou validação parcial) desses campos foi a inserção de cpf's ou cnpj's falsos, com caracteres diferentes de numéricos, caracteres especiais, entre outros. A fim de melhorar a qualidade dos dados contidos nessa tabela, poderíamos criar uma trigger, que chama uma function para verificar se o "cpf" ou o "cnpj" passado é correto. Essa trigger seria chamada sempre que fosse inserido ou atualizado algum registro nessa coluna.

Criaremos a function para verificar o cpf.


CREATE OR REPLACE FUNCTION Verifica_CPF(cpf IN CHAR)
  RETURN CHAR
  IS digito1  INTEGER := 0;
     digito2  INTEGER := 0;
     primeiro INTEGER := 0;
     conta    INTEGER := 0;
  BEGIN
    IF (cpf IS NOT NULL AND REGEXP_REPLACE(cpf,'[^0-9]') IS NULL) OR (LENGTH(cpf) > 11 OR LENGTH(REGEXP_REPLACE(cpf,'[^0-9]')) <> 11) THEN
      RETURN 'F';
    ELSE
      FOR z IN 1 .. 11 LOOP
        IF z = 1 THEN
          primeiro := SUBSTR(cpf, z, 1);
        ELSE
          IF SUBSTR(cpf, z, 1) = primeiro THEN
            conta := conta + 1;
          END IF;
        END IF;
      END LOOP;
        
      IF conta = 10 THEN
        RETURN 'F';
      END IF;
      
      FOR x IN 1 .. 9 LOOP
        digito1 := digito1 + (SUBSTR(cpf, x, 1) * (11 - x));
      END LOOP;
      
      digito1 := MOD(digito1,11);
        
      IF digito1 < 2 THEN
        digito1 := 0;
      ELSE
        digito1 := 11 - digito1;
      END IF;
        
      FOR y IN 1 .. 10 LOOP
        digito2 := digito2 + (SUBSTR(cpf, y, 1) *(12 - y));
      END LOOP;
        
      digito2 := MOD(digito2,11);
        
      IF digito2 < 2 THEN
        digito2 := 0;
      ELSE
        digito2 := 11 - digito2;
      END IF;
      
      IF (digito1 = SUBSTR(cpf,10,1)) AND (digito2 = SUBSTR(cpf,11,1)) THEN
        RETURN 'V';
      END IF;
        
      RETURN 'F';
    END IF;
    
    RETURN 'F';
  END;

Criaremos a trigger que chama a function para verificar a autenticidade do cpf.


CREATE OR REPLACE TRIGGER valida_cpf
  BEFORE INSERT OR UPDATE OF cpf ON cliente FOR EACH ROW
BEGIN
  IF (:NEW.cpf IS NOT NULL) AND (VERIFICA_CPF(:NEW.cpf) = 'F')  THEN
    RAISE_APPLICATION_ERROR(-20055,('O cpf '||:NEW.cpf||' inserido é inválido'));
  END IF;
END;

Criaremos a function para verificar o cnpj.


CREATE OR REPLACE FUNCTION Verifica_CNPJ(cnpj IN CHAR)
  RETURN CHAR
  IS digito1  INTEGER := 0;
     digito2  INTEGER := 0;
     primeiro INTEGER := 0;
     conta    INTEGER := 0;
  BEGIN
    IF (cnpj IS NOT NULL AND REGEXP_REPLACE(cnpj,'[^0-9]') IS NULL) OR (LENGTH(cnpj) > 14 OR LENGTH(REGEXP_REPLACE(cnpj,'[^0-9]')) <> 14) THEN
      RETURN 'F';
    ELSE
      FOR y IN 1 .. 14 LOOP
        IF y = 1 THEN
          primeiro := SUBSTR(cnpj, y, 1);
        ELSE
          IF SUBSTR(cnpj, y, 1) = primeiro THEN
            conta := conta + 1;
          END IF;
        END IF;
      END LOOP;
      
      IF conta = 13 THEN
        RETURN 'F';
      END IF;
    
      FOR z IN 1 .. 12 LOOP
        IF z = 1 OR z = 9 THEN
          digito1 := digito1 + (5 * SUBSTR(cnpj, z, 1));
        END IF;
        IF z = 2 OR z = 10 THEN 
          digito1 := digito1 + (4 * SUBSTR(cnpj, z, 1));
        END IF;
        IF z = 3 OR z = 11 THEN 
          digito1 := digito1 + (3 * SUBSTR(cnpj, z, 1));
        END IF;
        IF z = 4 OR z = 12 THEN 
          digito1 := digito1 + (2 * SUBSTR(cnpj, z, 1));
        END IF;
        IF z = 5 THEN 
          digito1 := digito1 + (9 * SUBSTR(cnpj, z, 1));
        END IF;
        IF z = 6 THEN 
          digito1 := digito1 + (8 * SUBSTR(cnpj, z, 1));
        END IF;
        IF z = 7 THEN 
          digito1 := digito1 + (7 * SUBSTR(cnpj, z, 1));
        END IF;
        IF z = 8 THEN 
          digito1 := digito1 + (6 * SUBSTR(cnpj, z, 1));
        END IF;
      END LOOP;
      
      digito1 := MOD(digito1,11);
      
      IF digito1 < 2 THEN
        digito1 := 0;
      ELSE
        digito1 := 11 - digito1;
      END IF;
      
      FOR x IN 1 .. 13 LOOP
        IF x = 1 OR x = 9 THEN
          digito2 := digito2 + (6 * SUBSTR(cnpj, x, 1));
        END IF;
        IF x = 2 OR x = 10 THEN 
          digito2 := digito2 + (5 * SUBSTR(cnpj, x, 1));
        END IF;
        IF x = 3 OR x = 11 THEN 
          digito2 := digito2 + (4 * SUBSTR(cnpj, x, 1));
        END IF;
        IF x = 4 OR x = 12 THEN 
          digito2 := digito2 + (3 * SUBSTR(cnpj, x, 1));
        END IF;
        IF x = 5 OR x = 13 THEN 
          digito2 := digito2 + (2 * SUBSTR(cnpj, x, 1));
        END IF;
        IF x = 6 THEN 
          digito2 := digito2 + (9 * SUBSTR(cnpj, x, 1));
        END IF;
        IF x = 7 THEN 
          digito2 := digito2 + (8 * SUBSTR(cnpj, x, 1));
        END IF;
        IF x = 8 THEN 
          digito2 := digito2 + (7 * SUBSTR(cnpj, x, 1));
        END IF;
      END LOOP;
      
      digito2 := MOD(digito2,11);
      
      IF digito2 < 2 THEN
        digito2 := 0;
      ELSE
        digito2 := 11 - digito2;
      END IF;
      
      IF (digito1 = SUBSTR(cnpj,13,1)) AND (digito2 = SUBSTR(cnpj,14,1)) THEN
        RETURN 'V';
      END IF;
      
      RETURN 'F';
    END IF;
    
    RETURN 'F';
  END;

Criaremos a trigger que chama a function para verificar a autenticidade do cnpj.


CREATE OR REPLACE TRIGGER valida_cnpj
  BEFORE INSERT OR UPDATE OF cnpj ON cliente FOR EACH ROW
BEGIN
  IF (:NEW.cnpj IS NOT NULL) AND (VERIFICA_CNPJ(:NEW.cnpj) = 'F') THEN
    RAISE_APPLICATION_ERROR(-20056,('O cnpj '||:NEW.cnpj||' inserido é inválido'));
  END IF;
END;

Dessa forma, estamos tratando esse problema na base. Ainda que o programador não tratasse a validação, nós tratamos na base. Com isso, temos a garantia que o dado correto está sendo armazenado na base.

Vamos testar?

Criaremos uma sequence para facilitar nosso teste.

Criação da sequence


CREATE SEQUENCE seq_cliente_codcliente
  START WITH 1
  INCREMENT BY 1
  NOMAXVALUE
  MINVALUE 1
  NOCACHE
  NOCYCLE;

Testaremos as inserções.

Quando inserimos um cpf CORRETO, como esperado, o registro é inserido:


INSERT INTO cliente(codcliente,nome,cpf,cnpj)
  VALUES(seq_cliente_codcliente.nextval,'Márcio Tognere','46464771221',null);

1 row inserted.

Quando inserimos um cpf INCORRETO, como esperado, é exibido um erro:


INSERT INTO cliente(codcliente,nome,cpf,cnpj)
  VALUES(seq_cliente_codcliente.nextval,'Márcio Tognere','12345678944',null);

Error starting at line : 21 in command -
INSERT INTO cliente(codcliente,nome,cpf,cnpj)
  VALUES(seq_cliente_codcliente.nextval,'Márcio Tognere','12345678944',null)
Error report -
ORA-20055: O cpf 12345678944 inserido é inválido
ORA-06512: at "TOGNERE.VALIDA_CPF", line 3
ORA-04088: error during execution of trigger 'TOGNERE.VALIDA_CPF'

Quando inserimos um cnpj CORRETO, como esperado, o registro é inserido:


INSERT INTO cliente(codcliente,nome,cpf,cnpj)
  VALUES(seq_cliente_codcliente.nextval,'Márcio Tognere',null,'26333191000105');

1 row inserted.

Quando inserimos um cnpj INCORRETO, como esperado, é exibido um erro:


INSERT INTO cliente(codcliente,nome,cpf,cnpj)
  VALUES(seq_cliente_codcliente.nextval,'Márcio Tognere',null,'12345678912344');

Error starting at line : 21 in command -
INSERT INTO cliente(codcliente,nome,cpf,cnpj)
  VALUES(seq_cliente_codcliente.nextval,'Márcio Tognere',null,'12345678912344')
Error report -
ORA-20056: O cnpj 12345678912344 inserido é inválido
ORA-06512: at "TOGNERE.VALIDA_CNPJ", line 3
ORA-04088: error during execution of trigger 'TOGNERE.VALIDA_CNPJ'

O mesmo comportamento ocorre quando realizamos atualização de algum registro.

Esse procedimento pode ser realizado em diversos casos. A escolha das colunas "cpf" e "cnpj" foram apenas um exemplo. É importante identificar em sua base onde precisemos tratar o dado que está sendo inserido para melhorar a qualidade dos dados.

Códigos fonte: https://github.com/marciotognere/valida_cpf_cnpj

Márcio Tognere

Com passagens por empresas como IBM, Stefanini, Mutant, minha trajetória profissional abrange uma variedade de papéis, incluindo Engenheiro de Dados, Analista de Dados e Cientista de Dados.