Guideline para Criação de Tabelas Customizadas

Montagem do script de criação de tabela

1. Para a criação de novas colunas pede-se que seja utilizado o template abaixo:

   Template: Criacao_TABELA_CUSTOM Expandir origem

   select count(*) into nAux from ALL_tables WHERE OWNER = SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AND table_name ='CSTM_NOME_TABELA';
   IF naux = 0 then
   stmt := q'[
   CREATE TABLE CSTM_NOME_TABELA (
       COD_EMPRESA               CHAR(5)     NOT NULL,
       NUM_LOJA                  VARCHAR2(4) NOT NULL,
       COD_COMPONENTE            VARCHAR2(5) NOT NULL,
       DATA_TRANSACAO            DATE        NOT NULL,
       NUM_NSU                   NUMBER(12)  NOT NULL,
       (...)  
       DATA_INCLUSAO             DATE           DEFAULT SYSDATE
       )]';
   execute immediate stmt;
     dbms_output.put_line('Criada tabela CSTM_NOME_TABELA');
   END IF;
   
   --CHAVE PRIMARIA
   SELECT COUNT(*) INTO nAux FROM ALL_INDEXES WHERE OWNER = SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AND UPPER(INDEX_NAME) = 'PK_CSTM_NOME_TABELA' ;
   IF nAux = 0 THEN
     stmt :=q'[CREATE UNIQUE INDEX PK_CSTM_NOME_TABELA ON CSTM_NOME_TABELA (COD_EMPRESA, COD_LOJA, DATA_TRANSACAO, NUM_COMPONENTE, NUM_NSU_TRANSACAO) TABLESPACE ]'|| PCK_P2K_UTIL.GET_INDEX_TABLESPACE;
   EXECUTE IMMEDIATE stmt;
     dbms_output.put_line('Criado índice PK_CSTM_NOME_TABELA');
   END IF;
   
   SELECT COUNT(*) INTO naux FROM ALL_constraints WHERE OWNER = SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AND constraint_name = 'PK_CSTM_NOME_TABELA' AND table_name = 'CSTM_NOME_TABELA';
   IF naux = 0 THEN
   stmt := q'[ALTER TABLE CSTM_NOME_TABELA ADD CONSTRAINT PK_CSTM_NOME_TABELA PRIMARY KEY (COD_EMPRESA, COD_LOJA, DATA_TRANSACAO, NUM_COMPONENTE, NUM_NSU_TRANSACAO)]';
     execute immediate stmt;
     dbms_output.put_line('Criado constraint PK_CSTM_NOME_TABELA');
   END IF;
   
   --INDICES
   --IX_1_CSTM_NOME_TABELA
     SELECT COUNT(*) INTO nAux FROM all_indexes WHERE owner = SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AND index_name = 'IX_1_CSTM_NOME_TABELA';
     IF naux = 0 THEN
       stmt := q'[ 
       CREATE INDEX IX_1_CSTM_NOME_TABELA ON CSTM_NOME_TABELA (DATA_TRANSACAO) TABLESPACE ]'  ||PCK_P2K_UTIL.GET_INDEX_TABLESPACE;
       execute immediate stmt;
       dbms_output.put_line('Criado índice IX_1_CSTM_NOME_TABELA');
     END IF;
   
   --DICIONARIO DE DADOS
   begin
     stmt := q'[comment on table CSTM_NOME_TABELA                                 is 'Descricao CSTM_NOME_TABELA']';
     execute immediate stmt;
     
     stmt := q'[comment on column CSTM_NOME_TABELA.COD_EMPRESA                    is 'Codigo da empresa']';
     execute immediate stmt;
     
     stmt := q'[comment on column CSTM_NOME_TABELA.COD_LOJA                       is 'Codigo da loja']';
     execute immediate stmt;
     
     stmt := q'[comment on column CSTM_NOME_TABELA.DATA_TRANSACAO                 is 'Data que a transacao aconteceu. (AAAAMMDD)']';
     execute immediate stmt;
     
     stmt := q'[comment on column CSTM_NOME_TABELA.NUM_COMPONENTE                 is 'Numero do Componente (PDV) que realizou a transacao.']';
     execute immediate stmt;
     
     stmt := q'[comment on column CSTM_NOME_TABELA.NUM_NSU_TRANSACAO              is 'Numero sequencial unico da transacao']';
     execute immediate stmt;
     
     stmt := q'[comment on column CSTM_NOME_TABELA.NOME_COLUNAn                   is 'Descricao NOME_COLUNA5']';
     execute immediate stmt;
     (...)
     stmt := q'[comment on column CSTM_NOME_TABELA.NOME_COLUNAn                   is 'Descricao NOME_COLUNAn']';
     execute immediate stmt;
     (...)
     stmt := q'[comment on column CSTM_NOME_TABELA.DATA_INCLUSAO                  is 'Data/hora de inclusao do registro']';
     execute immediate stmt;
   end;
   
   

2. Recomendamos que o arquivo seja salvo com o nome da tabela customizada. Caso seja utilizada a ferramenta gera-installBD o arquivo deve ser salvo na pasta TABELAS do projeto.

   
   

Montagem do script de criação de coluna

As colunas serão criadas à necessidade de criação de atributos em tabelas.

Para criação de novas colunas deve-se, obrigatoriamente, criar antes uma tabela customizada, não sendo permitido em nenhuma hipótese a criação de colunas nas tabelas do BD_STANDARD.

Exemplo: Para uma nova coluna com informações adicionais sobre o cadastro de um produto recomendamos criar uma tabela CSTM_PRODUTO baseando-se nas informações citadas para a criação das mesmas, em seguida adicionando às novas colunas. 

Nos tópicos a seguir recomendamos como boas práticas para criação de novas colunas:

1. Tipos de dados CHAR não deve ser utilizado para a criação de colunas em tabelas no banco de dados;
2. Para tipo de dado numérico (inteiro ou fracionado) deve-se definir tamanho fixo do campo;
3. Inserir comentário sobre o tipo de informação a ser populada na coluna;
4. Na construção do script fazer validação da existencial da coluna/tabela no banco antes da criação para prevenir erro na execução do script;
5. Evitar o uso de valor default;

Sugestão de nomenclatura para criação de novas colunas:

Exemplos:

COD_LOJA
COD_ITEM_TRANSACAO
COD_CAMPANHA

Para a criação de novas colunas pede-se que seja utilizado o template abaixo:

SELECT COUNT(*) INTO nAux FROM ALL_TAB_COLUMNS WHERE OWNER = SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AND TABLE_NAME = 'NOME_TABELA' AND COLUMN_NAME = 'NOME_COLUNA';
IF nAux = 0 THEN
    stmt := q'[ALTER TABLE NOME_TABELA ADD NOME_COLUNA TIPO_DE_DADO(TAMANHO) ]';
    EXECUTE IMMEDIATE stmt;
    stmt := q'[comment on column NOME_TABELA.NOME_COLUNA is 'Descricao']';
    execute immediate stmt;
    dbms_output.put_line('Criada coluna NOME_TABELA.NOME_COLUNA');
END IF;

 Segue a tabela com sugestões de nomenclatura para o prefixo das colunas e seu respectivo tipo de dados:

Montagem do script de criação de índice

Os índices são criados para melhoria de performance das consultas aos dados, pois realiza um processo para percorrer a estrutura em árvore para extrair apenas os registros necessários para atender a cláusula da query. Caso contrário, o SGBD lê toda a tabela para encontrar os dados desejados.

É necessário que se avalie a necessidade de criação do mesmo, pois a ocupação de espaço em disco que a criação do índice causa é alta.

Orientamos que seja evitado a criação de índices com função e índices em tabelas pequenas por não trazerem benefícios.

Características para recomendação de índices:

1 - Tabela com um grande volume de dados.

2 - Coluna que não sofrem modificações com frequências.

2- Colunas NOT NULL e que possuam dados exclusivos.

Nomenclatura para criação de índices customizados

Onde:

    Sequencial: Número sequencial do índice da tabela. Utilizar um algarismo como padrão, podendo aumentar gradativamente de acordo com a necessidade.

    Nome da tabela customizada: Tabela que vai receber o índice.

Exemplos:

    IX_1_HUB_CSTM_CASHBACK

    IX_1_CSTM_CASHBACK

    IX_2_CSTM_CASHBACK

Para a criação de novos índices orientamos que seja utilizado o template abaixo:

--ISSUE xxxxx INICIO
SELECT COUNT(*) INTO nAux FROM ALL_INDEXES WHERE OWNER = SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AND UPPER(INDEX_NAME) = 'IX_N_NOME_TABELA' ;
IF nAux = 0 THEN
    stmt :=    q'[CREATE INDEX IX_N_NOME_TABELA ON NOME_TABELA (NOME_COLUNA1,NOME_COLUNA2,NOME_COLUNAN) TABLESPACE ]'|| PCK_P2K_UTIL.GET_INDEX_TABLESPACE;
    EXECUTE IMMEDIATE stmt;
  dbms_output.put_line('Criado índice IX_N_NOME_TABELA');
END IF;

-- N é o número sequencial de ordem de criação
--ISSUE xxxxx FIM

Teste de execução dos scripts

1. Para realizar os devidos testes caso utilize o padrão de geração do gera-installBD é necessário acrescentar um cabeçalho e rodapé, mas este cabeçalho e rodapé é incluso pela ferramenta gera-installBD durante o processo de geração dos scripts.

   Cabecalho_rodape.sql
   
   

   --PADRAO DE CRIAÇÃO DE SCRIPT.
   SET TIMING ON
   SET SERVEROUTPUT ON
   SET ECHO OFF
   SPOOL C:\SCRIPTS\HUB_CSTM_CASHBACK.log
   DECLARE
   NAUX NUMBER;
   STMT VARCHAR2(4000);
   BEGIN
   
   /* Script a ser executado */
   
   COMMIT;
   EXCEPTION
   WHEN OTHERS THEN
   dbms_output.put_line('Erro no Comando:' || SUBSTR(stmt,1,230));
   dbms_output.put_line(TO_CHAR(SQLCODE));
   If SQLCODE <> 0 And SQLCODE <> -20001 Then
   RAISE;
   END IF;
   END;
   /
   SPOOL OFF;

           

2. Conectar em uma base de desenvolvimento e conforme orientação acima, aconselhamos que seja certificado que o script pode ser executada pelo menos 2 vezes sem apresentar erro.

   2.1. Na primeira execução é exibida a mensagem de criação da tabela, índices e constraints.

   2.2. Na segunda execução apenas o feedback do banco de dados deve ser exibido.

   

Tabelas de cadastro

Para tabela de cadastro recomendamos que seja trabalhado com tabelas externas e internas: A tabela externa para ser postado os dados pelo cliente/ERP e a tabela interna para receber os dados a ser trabalhado pela aplicação customizada.

Na tabela externa, responsável por receber os dados postados pelo cliente, orientamos ter colunas para registrar: A quantidade de execução do registro realizado no dia, o status do registro, a data e horário da inclusão do registro, a data e horário do último processamento, o tipo de operação e a mensagem do erro, se ocorrer, conforme o quadro 1. E propomos utilizar o prefixo HUB_CSTM como identificação que se trata de uma tabela externa customizada.

Quadro 1 - Colunas de controle para importação.

Importação de cadastro

A recomendação é utilizar uma rotina de banco de dados para realizar o processo de importação, que geralmente utilizamos um objeto do tipo PACKAGE e que proporcione ser executado mais de uma vez. Como recomendação, pode ser utilizado o prefixo PKG_CSTM, acrescentando o sufixo IMP para destacar que se trata de uma rotina de importação, para mais informações sobre nomenclatura vide documento Guideline para Criação de Objetos Customizados. Utilizando uma procedure com prefixo IMPORTA podendo ter um cursor para leitura dos dados da tabela externa, que neste cursor sugerimos a utilização de parametrização, cujo código seja o parâmetro 922 contendo o valor NP ou EP para variável do cursor identificar se os registros a ser processados são da importação periódica ou automática. Nesta rotina sugerimos que seja contemplado com 2 tipos de importação: Uma importação automática ao qual é realizada no período da madrugada ou horário com menos movimentação para uma carga com um grande volume de dados e uma importação periódica para contemplar importação de uma pequena quantidade de registros que possa ser realizado em qualquer horário. Para distinguir esses processos, pode ser utilizado o status dos registros "NP" para informar que o registro pode ser feito na importação periódica ou "EP" para importação automática, podendo ser assumido um valor default "NP" para coluna, da mesma forma a coluna referente a data e horário que pode ser assumido um valor default SYSDATE.

Nesta mesma coluna do status do registro, poderia ser utilizado para marcar com ER em caso de erro na tentativa de importação.

CREATE OR REPLACE PACKAGE pkg_cstm_imp_cstm_cashback IS
PROCEDURE importa_cashback(...);
END;
/
SHOW ERRORS;

CREATE OR REPLACE PACKAGE BODY pkg_cstm_imp_cstm_cashback IS

  PROCEDURE importa_cashback(...)
  IS
  CURSOR c_cstm_cashback(status) is 
  SELECT cod_cashback
         ,val_cashback
         ,tipo_operacao
         ,...
   FROM hub_cstm_cashback
  WHERE val_cashback <> 0
    AND status_registro='NP' 
    AND status_registro=status;
   
  v922 p2k_parametros.valor%type;
  
  BEGIN
  v922:= PCK_P2K_UTIL.FN_GET_P2K_PARAMETROS(922);
  
  FOR vc_cstm_cashback IN c_cstm_cashback(v922) LOOP

  BEGIN
  IF tipo_operacao = 'I' THEN
     
       INSERT INTO cstm_cashback(cod_cashback, val_cashback) 
           VALUES(vc_cstm_cashback.cod_cashback,vc_cstm_cashback.val_cashback);

       UPDATE hub_cstm_cashback SET
              status_registro = 'PR'
             ,data_proc       = SYSDATE
        WHERE cod_cashback     = vc_cstm_cashback.cod_cashback;

  IF tipo_operacao = 'A' THEN

       UPDATE cstm_cashback SET 
              val_cashback = vc_cstm_cashback.val_cashback
        WHERE cod_cashback = vc_cstm_cashback.cod_cashback;

      UPDATE hub_cstm_cashback SET
              status_registro = 'PR'
             ,data_proc       = SYSDATE
       WHERE cod_cashback     = vc_cstm_cashback.cod_cashback;
       
  IF tipo_operacao = 'E' THEN

       DELETE FROM cstm_cashback 
        WHERE cod_cashback = vc_cstm_cashback.cod_cashback;

       UPDATE hub_cstm_cashback SET
              status_registro = 'PR'
             ,data_proc       = SYSDATE
        WHERE cod_cashback = vc_cstm_cashback.cod_cashback;
       (...)
  ELSE 

       UPDATE hub_cstm_cashback SET 
              status_registro = 'ER'
             ,data_proc     = SYSDATE
             ,desc_erro_reg = 'Tipo de operacao invalida'
       WHERE cod_cashback = vc_cstm_cashback.cod_cashback;

  END IF;
     EXCEPTION
     WHEN OTHERS THEN
       -- Em caso de erro
       (...)
       UPDATE hub_cstm_cashback SET 
              status_registro = 'ER'
             ,data_proc     = SYSDATE
             ,desc_erro_reg = SQLERRM
       WHERE cod_cashback = vc_cstm_cashback.cod_cashback;
       
     END;
  
  END LOOP; 
  END;

END;
/
SHOW ERRORS;

No tipo de operação, indica se o registro irá realizar um processo de inserção, atualização ou exclusão, a coluna com esta informação poderia assumir o valor default "I". Em caso de exclusão salienta-se haver uma preocupação em relação as dependências, pois pode ser assumido que haverá a exclusão dos registros dependentes ou marcar como erro e informar a dependência.

Orientamos que as colunas da tabela externa não sejam criadas como NOT NULL, apenas as que fazem parte da chave primária, mesmo que na tabela da aplicação customizada seja obrigatória, pois o cliente pode optar por preencher posteriormente, desta forma não seja um impedimento para a realização de uma carga inicial e se caso permanecer com o valor nulo ou indevido, a rotina realize os devidos tratamentos se for coerente. De igual forma, sugerimos não haver restrição entre as tabelas externas. No quadro 2 há um exemplo de uma tabela externa e no quadro 3 um exemplo de uma tabela interna. Aconselhamos que haja a possibilidade da importação durante o processo de carga do cliente sem causar problema de lock.

Durante o processo de importação salientamos que o problema em algum registro nunca interrompa todo o processo, apenas que seja marcado erro ocorrido e passe para o próximo.

Quadro 2 - Modelo de uma tabela externa: HUB_CSTM_CASHBACK.

Quadro 3 - Modelo de uma tabela interna: CSTM_CASHBACK.

Conduzimos a verificação da utilização da tabela ENTRADA_LOTE no processo de geração de LOTE para que seja vista a necessidade da rotina contemplar esta etapa, se fazendo necessário a montagem em uma única linha para o PDV dentro de um padrão utilizado pela aplicação com as informações necessárias da tabela externa. Ressaltamos que o padrão a ser utilizado deve estar pré-acordado com a aplicação.

No processo para inserir a ENTRADA_LOTE é feito a concatenação das colunas de uma tabela customizada em uma única linha, separadas por um delimitador, que é interpretado pela aplicação SP. Deve-se validar o tipo de operação a ser gerado no lote (inserção, atualização ou exclusão)  e registrar um número de lote para indicar a sequência da entrada. Os campos seguem uma ordem predefinida junto a aplicação para evitar erros durante o processamento do lote.

Exemplo de um registro de lote:

{CstmCash}{#$#}10{#$#}30{#$#}{CstmCash}

CREATE OR REPLACE PROCEDURE SP_CSTM_GERA_LOTE 
IS
  CURSOR c_cstm_cashback(status) is 
  SELECT '{CstmCash}' AS CABECALHO
         ,'{#$#}'||cod_cashback||'{#$#}' AS cod_cashback
         ,'{#$#}'||val_cashback||'{#$#}' AS val_cashback
         ,'{CstmCash}' AS RODAPE
         ,tipo_operacao
   FROM hub_cstm_cashback
  WHERE val_cashback <> 0
    AND status_registro='NP' 
    AND status_registro=status;

V_CSTM VARCHAR2(2000) ;
    
BEGIN 

FOR vc_cstm_cashback IN c_cstm_cashback(status) LOOP

-- Montagem da linha
V_CSTM := vc_cstm_cashback.cab||vc_cstm_cashback.cod_cashback||vc_cstm_cashback.val_cashback||vc_cstm_cashback.rodape;
INSERT INTO CSTM_ENTRADA_LOTE (COD_OPERACAO, DESC_CONTEUDO) VALUES (vc_cstm_cashback.tipo_operacao, V_CSTM);

END LOOP;

END;
/

Tabelas de movimentação

Assim como sugerido para as tabelas de cadastro recomendamos que para as tabelas de movimentação o fluxo seja o mesmo, com tabelas internas e externas: Neste caso a tabela interna para postagem dos dados oriundos da aplicação e a tabela externa para disponibilizar os dados para o cliente/ERP.

Ao criar uma tabela interna customizada orientamos inserir algumas colunas de controle para registrar algumas informações importantes sobre os registros de movimentação:

• Chave da transação (São colunas que contém a informação da origem do registro de movimentação):

CODIGO_EMPRESA, NUMERO_LOJA, COD_COMPONENTE, DATA_TRANSACAO, NSU;

• Controle de integração (São colunas que registram o status da exportação do registro):

STATUS_INTEGRACAO, ERRO_INTEGRACAO;

Para a movimentação, atualmente é utilizada uma tabela interna de controle contendo a chave do registro a ser integrado, bem como colunas de controle de status para registro de processamento, data de inclusão do registro, data do processamento e mensagens referente ao status de sua integração.

Exemplo - Tabela interna customizada de movimentação:

Importação de registros de movimentação

A recomendação é utilizar uma rotina de banco de dados para realizar o processo de exportação. Assim como informado para tabelas de cadastro, sugerimos o uso de objetos do tipo PACKAGE e que proporcione a execução mais de uma vez. Para a nomenclatura sugerimos a leitura do documento Guideline para Criação de Objetos Customizados. Para exportar os registros das tabelas internas até as externas pode-se utilizar um processo semelhante ao indicado no caso de tabelas de cadastro com a utilização de um cursor para leitura dos dados da tabela interna com o status NP. Para uso de paralelismo na execução da exportação de registros sugerimos o uso do COD_PROCESSADOR. Esta coluna está presente na tabela ESTABELECIMENTO. No cadastro do estabelecimento o código do processador é informado para cada loja podendo agrupar assim um as lojas de acordo com o COD_PROCESSADOR para execução da rotina de exportação de forma paralela.

Durante o processo de exportação salientamos que em caso de problema no processamento de algum registro a rotina marque o mesmo como erro, mas não interrompa o restante do processo, seguindo para a exportação do próximo registro.

No modelo sugerido é utilizada uma package acionada a cada três minutos (podendo ser alterado) por um JOB para processamento dos registros com status NP. Esta package lê a tabela de controle para verificar quais registros serão exportados para a tabela externa. Qualquer erro indicado por alguma tabela durante a exportação dos registros deve ser gravado na tabela de controle.

Exemplo:

No fluxo abaixo temos uma tabela customizada de controle de movimentação e duas outras tabelas filhas, que podem ser criadas baseando-se em tabelas internas do projeto BD-STANDARD ou utilizar apenas as tabelas . Uma rotina customizada deverá buscar os registros nestas tabelas para serem exportadas a uma ou mais tabelas externas customizadas. Caso algum dos registros acuse erro na exportação o mesmo ficará gravado com o status 'ER' na tabela CSTM_TRANSACAO, caso a exportação do registro seja concluída com sucesso o status 'PR' deve ser gravado na tabela CSTM_TRANSACAO.

Para a criação de tabela externa customizada de movimentação sugerimos o layout abaixo:

No modelo acima constam colunas de controle nomeadas de STATUS_PROC_X, DATA_PROC_X e MENSAGEM_X. Estas colunas são disponibilizadas para controle de registros exportados para outros sistemas e geralmente são criadas com a numeração de 1 até 10.

Exemplo:

STATUS_PROC_1

STATUS_PROC_2

STATUS_PROC_3

O objetivo dessas colunas é o controle de status das informações acessadas das tabelas externas por sistemas externos.