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:
Comando | Ação |
---|---|
Commit | Grava definitivamente os efeitos dos comandos de uma transação (insert, delete e update). |
Rollback | Desfaz os efeitos dos comandos da transação (insert, delete e update). |
Fim da sessão | Quando a sessão é encerrada sem problemas, ocorre um commit implícito, caso haja algum problema, ocorre um rollback implícito. |
Comando DDL ou DCL | Todo comando DDL (create, alter e drop) e DCL (grant e revoke) provocam o fim da transação corrente, havendo um commit implícito |
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”
Tipo | Descrição |
---|---|
INVALID_CURSOR | Referência feita sobre um cursor inexistente (ex: FETCH sobre um cursor já fechado) |
INVALID_NUMBER | Operação de atribuição ou conversão de tipos resultou em um número inválido. |
NO_DATA_FOUND | Disparada em três cenários: (i) quando um SELECT INTO que não retorna valor é executado.; (ii) tentativa de acesso de uma linha não existente em uma coleção PL/SQL; (iii) fim de um arquivo alcançado (quando estamos fazendo leitura sequencial com o uso do pacote UTL_FILE) |
PROGRAM_ERROR | A PL/SQL encontrou um problema interno. Nesse caso, pode ser preciso chamar o suporte Oracle. |
TOO_MANY_ROWS | Uma operação SELECT_INTO retornou mais de uma linha. |
VALUE_ERROR | Erro de conversão de tipos ou na atribuição de um valor a uma variável. |
ZERO_DIVIDE | Tentativa de divisão por zero. |
- 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:
-
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.
-
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;
- É 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.
Updated almost 3 years ago