Padrões de banco de dados

O artigo a seguir visa demonstrar de forma básica os padrões de desenvolvimento de objetos de banco de dados que deverão ser utilizados na construção de funcionalidades personalizadas nos sistemas Sankhya e sugere boas práticas para um melhor desempenho.

🚧

Atenção

Esse artigo não tem por finalidade ensinar a programar em SQL, mas sim utilizar de forma padronizada os recursos da linguagem e normalizar as práticas durante o desenvolvimento.

Desenvolvendo TRIGGERS

Conceito: Uma trigger dispara quando um evento de INSERT, UPDATE, DELETE ocorre em uma tabela qualquer do banco de dados. Neste momento podemos incluir regras de validações ou executar tarefas mediante o disparo do evento.

Tipos de triggers
Eventos de trigger podem ocorrer de duas formas:

• FOR EACH ROW: (para cada linha)
  Um gatilho que está marcado FOR EACH ROW é chamado uma vez para cada linha que a operação modifica. Por exemplo, um comando DELETE afetando 10 linhas faz com que todos os gatilhos ON DELETE da relação de destino sejam chamados 10 vezes, uma vez para cada linha excluída.

• FOR EACH STATEMENT: (para cada comando)
  Esse tipo de disparo ocorre somente uma vez para uma determinada operação, não importando quantas linhas sejam modificadas; em particular, uma operação que não modifica nenhuma linha, ainda assim resulta na execução de todos os gatilhos FOR EACH STATEMENT aplicáveis àquela operação.

O padrão que usamos em nossas triggers é “for each row”, salvo exceções. O recurso “for each row” não existe no SQL SERVER, então as triggers em SQL SERVER devem estar preparadas para trabalhar com blocos de linhas e não
com uma linha apenas. Caso você crie uma trigger em SQL SERVER que trate apenas uma linha, então deve-se criar uma trava para evitar comandos que alterem várias linhas ao mesmo tempo.

Para processar as várias linhas (blocos de linhas) no SQL SERVER, usamos as tabelas de serviço do SQL SERVER. Que são as tabelas INSERTED e DELETED. Com elas podemos “percorrer” as linhas incluídas, alteradas ou excluídas e fazer o processamento desejado de cada linha.

Quando Usar Cursor

No caso de existir não uma, mas várias linhas que precisam ser tratadas/analisadas em uma tabela, é necessário lançar mão do recurso CURSOR.

Padrão de nomeação de CURSOR: 'CUR_' + 'nome sugestivo'. Ex: CUR_PRODUTOS.

A sintaxe utilizada para criação/manipulação de cursores é diferente entre Oracle e SQL Server. Ao fim da rotina prevista em cursores, é importante que o cursor aberto seja devidamente fechado, observando a sintaxe de fechamento dos mesmos em cada banco de dados.

Ex: CLOSE CUR_PRODUTOS.

📘

Boa prática

Muitas vezes utilizamos cursores, sendo que um UPDATE direto ou MERGE resolve o problema. Então sempre que possível prefira utilizar UPDATE ou MERGE, pois possuem melhor performance que o uso de cursores.

Otimizando operações DML com o uso de FORALL (ORACLE)

No nosso dia a dia é muito comum encontrarmos códigos para processamento de dados que seguem o seguinte formato:

SQL> declare
    y number;
 
 begin
     -- Abre um cursor loop na tabela de origem
    for r in (select x from t)
    loop
           -- Faz o processamento dos dados
      y := r.x * r.x;
     -- Insere os dados na tabela de destino
      insert into u values(y);
    end loop;
 end;
  /

Aqui usamos um cursor implícito que percorre linha a linha. O problema desta abordagem é que, ao codificar o procedimento com um loop e fazendo os INSERTs um a um, o Oracle será forçado a executar uma troca de contexto para cada comando DML em cada iteração do loop.

Quando se tratam de poucos registros na tabela de origem é possível que o impacto das trocas de contexto seja insignificante. Mas, a medida que a massa de dados aumenta, o efeito se torna cumulativo e podemos incorrer em problemas seríssimos de performance. Aliás, é muito comum problemas deste tipo aparecerem apenas nos ambientes de produção, especialmente quando no ambiente de teste a validação da rotina é feita com um conjunto de dados muito menor do que o esperado para o processo real. Por este motivo é importante ter a performance como uma questão de design desde os primeiros momentos de um projeto que irá manipular uma grande quantidade de dados, evitando surpresas para quando o sistema entrar em produção.

A maneira mais adequada de endereçar este tipo de procedimento é sempre que possível executar o processamento em um único passo, como por exemplo, utilizando um INSERT SELECT com a hint APPEND. Quando isso não é possível e precisamos fazer uso do PL/SQL para quebrar o processamento em passos menores, o uso do FORALL é altamente indicado. Veja abaixo como funciona este comando:

SQL> declare
    y dbms_utility.number_array;

 begin
  -- Abre um cursor loop na tabela de origem
  for r in (select x from t)
  loop
    -- Processa os dados e guarda o resultado no array
    y(y.count() + 1) := r.x * r.x;
    end loop;
    -- Insere os dados processados todos de uma vez, em um único comando DML
    forall i in y.first .. y.last
    insert into u values(y(i));
 end;
/

Controle de Transação

Uma transação é um conjunto de operações, delimitadas por um início e um fim. Iniciando quando se executa o primeiro comando SQL e terminando de acordo com as seguintes situações:

📘

Boa prática

É boa prática sempre manter uma transação aberta o menor tempo possível (quanto menos comandos dentro de uma transação, melhor será). Isso evita contenções em tabelas e menor risco de Dead Lock no banco de dados.

• Exemplo de controle de transação Oracle:

BEGIN
	BEGIN
      DELETE FROM TGFPRO WHERE CODPROD = 1;
    EXCEPTION
      WHEN OTHERS THEN
      ROLLBACK;
  	END;
 COMMIT;
END;

• Exemplo de controle de transação em SQL Server:

BEGIN TRANSACTION
	BEGIN TRY
    DELETE FROM TGFPRO WHERE CODPROD = 1
    COMMIT
	END TRY
BEGIN CATCH
    ROLLBACK
END CATCH

• Pragma AUTONOMOUS_TRANSACTION (ORACLE)

Sempre que precisamos fazer um select na própria tabela de disparo de uma trigger, precisamos usar o recurso deste pragma. Isso é muito útil, mas deve ser usado com cautela e sabendo que tudo que for feito neste objeto terá uma transação separada da transação principal do sistema.

❗️

Atenção

Seu uso indiscriminado pode causar contenção nas tabelas envolvidas e Dead Lock caso tenham tarefas que alteram várias tabelas e ou transações muito longas (demoradas).

📘

Boa prática

Uma alternativa útil para evitar o uso do AUTONOMOUS _TRANSACTION diretamente em triggers é sempre que possível criar uma função que faça a consulta na tabela mãe da trigger. Assim o Autonomous_Transaction pode ser colocado apenas nesta função que faz o select na tabela mãe da trigger. Como na função não terá nenhum comando que modifica dados, ela não causará contenção e possibilidades de Dead Lock.

Tratando Exceções

Na linguagem PL/SQL qualquer tipo de erro é tratado como uma exceção (exception), o que significa uma situação que, a princípio, não deveria ocorrer durante a execução do programa. No oracle existem dois tipos de exceção:

• Exceção de sistemas (system exception): um erro definido pelo Oracle que é disparado automaticamente pela runtime engine da PL/SQL assim que ela detecta o problema. Exemplos: “duplicate value in index” e “no data found”

• Exemplo de tratamento de exceção, usando ZERO_DIVIDE:

CREATE OR REPLACE FUNCTION f_inverso(x IN NUMBER) RETURN NUMBER 
IS
vFx NUMBER;
BEGIN
  vFx := 1/x;
  RETURN vFx;
 EXCEPTION
    WHEN ZERO_DIVIDE THEN -- este é o “rótulo” dado pelo Oracle
                -- para o tipo de erro disparado
                -- quando ocorre a divisão por zero.
    DBMS_OUTPUT.PUT_LINE('----------------------------------');
    DBMS_OUTPUT.PUT_LINE('Erro: divisão por ZERO.');
    DBMS_OUTPUT.PUT_LINE('Não é possível obter o f(x) = 1/x.');
    DBMS_OUTPUT.PUT_LINE('----------------------------------');
    RETURN NULL; -- sempre é preciso retornar um valor
            -- em uma função!
END f_inverso;

• Exemplo usando OTHERS (que prevê qualquer erro ocorrido)

CREATE OR REPLACE FUNCTION f_inverso(x IN NUMBER) RETURN 
NUMBER IS
vFx NUMBER;
BEGIN
    vFx := 1/x;
    RETURN vFx;
 EXCEPTION
    WHEN OTHERS THEN -- se qualquer erro ocorrer...
    DBMS_OUTPUT.PUT_LINE('----------------------------------');
    DBMS_OUTPUT.PUT_LINE('Erro na execução da função.');
    DBMS_OUTPUT.PUT_LINE('Entre em contato com o 
administrador.');
    DBMS_OUTPUT.PUT_LINE('Código Oracle: ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('Mensagem Oracle: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('----------------------------------');
    RETURN NULL; 
END f_inverso;

• Exceção Programada

Exceção definida pelo programador e, portanto, específica da aplicação. Este tipo de exceção não é disparada automaticamente, mas apenas para situações indicadas pelo programador em seu código.

• Exemplo de uma exceção criada pelo programador (conversao_numero):

DECLARE
        v_teste NUMBER;
        conversao_numero EXCEPTION;
        PRAGMA EXCEPTION_INIT (conversao_numero, -06502);
    BEGIN
     v_teste := TO_NUMBER ('A'); -- converter A para número vai dar 
    erro!
EXCEPTION
    WHEN conversao_numero
    THEN
      DBMS_OUTPUT.put_line ('Erro de conversao: ' || SQLERRM);
    WHEN OTHERS
    THEN
      DBMS_OUTPUT.put_line ('Erro: ' || SQLERRM);
      RAISE; -- propaga, por exemplo, se não for nenhum dos erros 
esperados...
END;

• Exemplo de tratamento SQL Server:

CREATE PROCEDURE usp_GetErrorInfo AS 
    SELECT ERROR_NUMBER() AS ErrorNumber ,
        ERROR_SEVERITY() AS ErrorSeverity ,
        ERROR_STATE() AS ErrorState ,
        ERROR_PROCEDURE() AS ErrorProcedure ,
        ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS 
ErrorMessage; 
GO
BEGIN TRY 
    SELECT 1/0; 
END TRY 
BEGIN CATCH 
    EXECUTE usp_GetErrorInfo; 
END CATCH;

❗️

Atenção

Verificar se no cabeçalho da trigger não está sendo referenciado o schema antes do nome da tabela.

Exemplo: CREATE OR REPLACE TRIGGER TRG_FULANO BEFORE INSERT ON SANKHYA.TGFCAB.

O owner "SANKHYA." antes da TGFCAB pode causar um transtorno quando restauram o backup com essa trigger em outro schema, pois a trigger do outro schema é disparada quando se faz alterações no schema SANKHYA, gerando os famosos "At end of table" ou "dados não encontrados"

Desenvolvendo PROCEDURES

Stored Procedure, que significa Procedimento Armazenado, é um conjunto de comandos em SQL que podem ser executados de uma só vez, como em uma função. Ele armazena tarefas repetitivas e aceita parâmetros de entrada para que a tarefa seja efetuada de acordo com a necessidade individual. Um Stored Procedure pode reduzir o tráfego na rede, melhorar a performance de um banco de dados, criar tarefas agendadas, diminuir riscos, criar rotinas de processamento, evitar código duplicado, etc. A maioria das regras e boas práticas aplicadas as triggers, também se aplicam as procedures.

• Nomeação de Procedures
  Usamos: STP - identifica que trata-se de uma stored procedure;
  Exemplo de nome de uma procedure fictícia: STP_AJUSTA_TABELA.

Desenvolvendo FUNÇÕES

Quando você cria um procedimento ou função, você pode definir parâmetros. Existem três tipos de parâmetros que podem ser declarados:

• IN - O parâmetro pode ser referenciado pelo procedimento ou função. O valor do parâmetro não pode ser substituído pelo procedimento ou função.

• OUT - O parâmetro não pode ser referenciado pelo procedimento ou função, mas o valor do parâmetro pode ser substituído pelo procedimento ou função.

• IN OUT - O parâmetro pode ser referenciado pelo procedimento ou função e o valor do parâmetro pode ser substituído pelo procedimento ou função.

Em SQL Server as funções são muito limitadas em recursos, no entanto. São usadas para rotinas mínimas, como ler uma tabela e devolver determinado(s) dados(s).

• Nomeação do Objeto
  Usamos: SNK – identifica que trata-se de uma função;
  Exemplo de nome de uma procedure fictícia: SNK_PROCURA_DADO.

Observação aplicável para triggers, procedures e funções:

1. A opção MERGE tem melhor performance em relação ao CURSOR. Existe um ganho de performance acentuado utilizando-se o MERGE. No entanto, o MERGE no SQL Server não é previsto nas versões até 2005.

2. No ORACLE, ao usar o cursor, a linguagem nos dá uma opção mais sucinta em sua sintaxe, facilitando seu uso e entendimento, assim como garantindo o FETCH automático e destruição do cursor ao seu final. O nome da opção é conhecida como FOR LOOP.

Exemplo de código:

cursor c1 is
    SELECT monthly_income
    FROM employees
    WHERE name = name_in;
FOR employee_rec in c1
 LOOP
    total_val := total_val + 
employee_rec.monthly_income;
 END LOOP;

3. É boa prática de programação comentar o código para facilitar a manutenção futura do mesmo. Para tanto, utilizamos no início do objeto um HEADER de comentários de identificação. No corpo do código, para facilitar a compreensão do que foi analisado e programado em cada bloco de funcionalidade, deve-se colocar um comentário de linha ou bloco. Veja abaixo em cinza um exemplo de HEADER e comentários por blocos de comandos:

CREATE OR REPLACE PROCEDURE SNK_ENVIAR_EMAIL( P_DESTINATARIO IN 
VARCHAR2, P_ASSUNTO IN VARCHAR2, P_MESSAGEM IN VARCHAR2 ) AS
/********************************************************************
NOME AUTOR: Sankhya Gestão de Negócios
DATA......: 24/03/2017
OBJETIVO..: Procedure que envia email utilizando o servidor SMTP configurado no 
cadastro do usuário logado.
PRE-REQUISITOS: O Servidor de banco de dados precisa ter acesso ao servidor SMTP 
para que funcione o envio de email, se houver regras de segurança de rede que 
impeçam esta comunicação, o envio falhará.
 
PARAMETROS DE ENTRADA:
    P_DESTINATARIO: informar aqui o email do destinatário da mensagem 
    P_ASSUNTO: informar aqui o assunto tratado no email
    P_MESSAGEM: Mensagem que será enviada no corpo do email 
 
EXEMPLO DE USO: EXEC SNK_ENVIAR_EMAIL( P_DESTINATARIO ,P_ASSUNTO 
,P_MESSAGEM );
********************************************************************/
    --VARIAVEIS
    V_CONNECTION UTL_SMTP.CONNECTION;
    V_CRLF VARCHAR2( 2 ) := CHR( 13 ) || CHR( 10 );
    V_SERVIDORSMTP VARCHAR2( 100 );
    V_PORTASMTP INT;
    V_REMETENTE VARCHAR2( 100 );
    V_SMTP_USER VARCHAR2( 100 );
    V_SMTP_USER_PASS VARCHAR2( 100 );
    V_TIPOSEGURANCA VARCHAR2( 1 );
    V_MENSAGEM_ERRO VARCHAR2( 400 );
BEGIN
    BEGIN
     BEGIN
      --OBTENDO DADOS DE SMTP DO USUÁRIO LOGADO
      SELECT SERVIDORSMTP
        , PORTASMTP
        , EMAIL
        , TIPOSMTP
        , USUARIOSMTP
        , SENHASMTP
      INTO V_SERVIDORSMTP
        , V_PORTASMTP
        , V_REMETENTE
        , V_TIPOSEGURANCA
        , V_SMTP_USER
        , V_SMTP_USER_PASS
      FROM TSIUSU
      WHERE CODUSU = SNK_GET_USUARIO_LOGADO;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR( -20101, 'Erro obtendo configuração de SMTP 
do usuário logado ao tentar enviar email pelo Banco de Dados' );
    END;

    --SE USUÁRIO LOGADO NÃO TEM CONFIGURAÇÃO DE SMTP, LANÇA ERRO
    IF V_SERVIDORSMTP IS NULL
       OR V_PORTASMTP IS NULL
       OR V_REMETENTE IS NULL THEN
       RAISE_APPLICATION_ERROR( -20101, 'Erro obtendo configuração de SMTP do 
usuário logado ao tentar enviar email pelo Banco de Dados' );
    END IF;

    --EFETUANDO CONEXÃO COM O SERVIDOR SMTP
    V_CONNECTION := UTL_SMTP.OPEN_CONNECTION( V_SERVIDORSMTP, 
V_PORTASMTP );
    UTL_SMTP.HELO( V_CONNECTION, V_SERVIDORSMTP );

    --SE CONFIGURADO PARA CONEXÃO SEGURA
    IF NVL( V_TIPOSEGURANCA, 'N' ) <> 'N' THEN
      --AUTENTICAR USUÁRIO NO SERVIDOR SMTP
      UTL_SMTP.COMMAND( V_CONNECTION, 'AUTH LOGIN' );
      UTL_SMTP.COMMAND( V_CONNECTION, UTL_RAW.CAST_TO_VARCHAR2( 
UTL_ENCODE.BASE64_ENCODE( UTL_RAW.CAST_TO_RAW( ( V_SMTP_USER ) ) ) ) );
      UTL_SMTP.COMMAND( V_CONNECTION, UTL_RAW.CAST_TO_VARCHAR2( 
UTL_ENCODE.BASE64_ENCODE( UTL_RAW.CAST_TO_RAW( ( V_SMTP_USER_PASS ) ) 
) ) );
    END IF;

    --PREENCHENDO DADOS DE CABEÇALHO
     UTL_SMTP.MAIL( V_CONNECTION, V_REMETENTE );
    UTL_SMTP.RCPT( V_CONNECTION, P_DESTINATARIO );

    --PREENCHENDO DADOS DO EMAIL
    UTL_SMTP.OPEN_DATA( V_CONNECTION );
    UTL_SMTP.WRITE_DATA( V_CONNECTION, 'From: ' || V_REMETENTE || V_CRLF 
);
    UTL_SMTP.WRITE_DATA( V_CONNECTION, 'To: ' || P_DESTINATARIO || V_CRLF 
);
    UTL_SMTP.WRITE_DATA( V_CONNECTION, 'Subject: ' || P_ASSUNTO || V_CRLF 
);
    UTL_SMTP.WRITE_DATA( V_CONNECTION, P_MESSAGEM );
    UTL_SMTP.CLOSE_DATA( V_CONNECTION );

    --ENCERRANDO CONEXÃO
    UTL_SMTP.QUIT( V_CONNECTION );
 EXCEPTION
    WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
     BEGIN
      V_MENSAGEM_ERRO := SQLERRM;
      UTL_SMTP.QUIT( V_CONNECTION );
     EXCEPTION
       WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR 
THEN
        NULL; -- QUANDO O SERVIDOR SMTP ESTÁ INDISPONÍVEL, NÃO TEMOS 
UMA CONEXÃO COM O SERVER, ENTÃO A CHAMADA A QUIT GERA UMA EXCEPTION, 
QUE AQUI VAMOS IGNORAR
     END;
  WHEN OTHERS THEN
     V_MENSAGEM_ERRO := SQLERRM;

  RAISE_APPLICATION_ERROR( -20000, 'Falha ao enviar email devido ao erro: ' || 
V_MENSAGEM_ERRO );
 END;
END;
/

📘

Boa prática

Veja que com os comentários, no bloco de código acima, fica muito mais fácil saber o que o objeto faz e qual ponto é responsável por qual regra de negócio. Isso é uma boa prática de programação.

Criando Views

VIEW, em essência, é uma tabela virtual que não existe fisicamente. Em vez disso, é criado por uma consulta que junta uma ou mais tabelas . View é uma tabela lógica e não armazena dados, utilizando os mesmos dados das tabelas usadas no código da view.

Exemplo:

SELECT VIEW VGFCAB_NUNOTA AS SELECT * FROM TGFCAB WHERE  NUNOTA < 100;

📘

Boa prática

Sempre que possível, crie suas views colocando apenas os campos que realmente precisa nas consultas que fará na view, evite usar o SELECT *, pois, quanto menos campos melhor será a performance da view.

Criando ÍNDICES de colunas

Um índice acelera buscas em tabelas, pois ao procurar uma informação não é necessário pesquisar todas as linhas de uma tabela, mas sim apenas parte delas. No entanto, devemos observar também que existe um 'preço' ao criar um índice no input de dados da tabela do índice. Isso ocorre porque o índice é reorganizado automaticamente mediante a entrada de novos dados na tabela. Então é recomendado NÃO criar índices em demasia e que não tenham um propósito claro. Quando o índice realmente se faz necessário então usamos o seguinte padrão de nomeação:

NOMETABELA_IXX

Onde:

• NOMETABELA → é a tabela a qual pertence o índice.
• I → é a identificação do objeto, no caso Índice.
• XX → Números sequenciais de índices desta tabela.

O que sugere que caso tenha a necessidade de se criar um índice específico, utilizar o mesmo padrão Sankhya, mas com o nome do cliente como sufixo. Assim fica fácil saber quais são os índices padrões Sankhya e quais são
específicos do cliente.

Exemplos: TGFCAB_I01 (Índice padrão Sankhya )
Exemplos: TGFCAB_I01_NOMECLIENTE (Índice específico de Cliente)

📘

Boa prática

Um índice composto por vários campos, deve sempre começar pelo campo que tem menor recorrência.

Exemplo: Para criar um índice com os campos CODEMP, CODPARC, NUNOTA.

O ideal é que NUNOTA venha primeiro que CODEMP e CODPARC, pois NUNOTA repete menos (ou não repete) do que os outros, logo em seguida o melhor candidato é CODPARC, pois tem chance de repetir menos que CODEMP.

Assim o índice correto neste exemplo seria: NUNOTA, CODPARC e CODEMP

🚧

Importante

Se uma coluna possui apenas dois valores, por exemplo “S” ou “N”, ela não é boa candidata a ter índice próprio ou participar de um índice composto, pois os valores repetem muito e o custo de atualização do índice seria mais caro que o benefício durante as consultas.

Como tirar dúvidas?

Para tirar dúvidas e compartilhar informações, use a sala Banco de dados da comunidade Sankhya Developer.