Blog do Jhonny

Olá pessoal, tudo bem?

Abaixo segue uma procedure que criei para geração de arquivos textos automaticamente, claro que baseado em uma query recebida como parâmetro.
Esta query é recebida como string, ou seja, dinamica.

Atenção:
Como a query é dinamica, observar o uso de aspas simples, pois pode dar erro ao executá-la.

Script:
CREATE OR REPLACE PACKAGE EXPORT_FILE IS

END EXPORT_FILE;
/

Exemplo de uso:
declare
–Declaracao da variavel de retorno
p_archive export_file.t_archive;
p_msg_error varchar2(500);
begin
–Chamada da procedure
export_file.pr_export_file(’select * from dual’
,null
,null
,chr(9)
,p_archive,
,p_msg_error);

–Lendo a variavel
if p_archive.count > 0 then
for i in p_archive.first..p_archive.last
loop
dbms_output.put_line(p_archive(i).line);
end loop;
end if;
–
end;

Observação: De vez enquando caso ocorra erro verificar se a tabela com inicial “TMPEXPORTFILE” foi apagada.

Bom, é isso aí pessoal, qualquer dúvida entra em contato…

Abs.

Olá pessoal, tudo bem?

No post abaixo é mencionado como executar comandos dos sistemas operacionais via banco de dados Oracle, usando o DBMS_SCHEDULER.

Primeiro, ESTA FUNÇÃO TEM QUE PASSAR POR UMA AVALIAÇÃO DO DBA, POIS QUEBRA A INTEGRIDADE DE SEGURANÇA DO BANCO COM O SERVIDOR, isso porque a função quando executa o JOB, ela usa o Owner “Oracle” no servidor, e assim, todas as permissões que este usuário tiver no S.O, a função conseguirá executar o comando passado.

Segundo, é necessário dar os seguintes grants abaixo:

grant create job to “users”;
grant create external job to “users”;
grant execute on dbms_scheduler to “users”;

A função abaixo cria um JOB do tipo EXECUTAVEL e em seguida executa tal JOB. No final apaga o JOB.

CREATE OR REPLACE FUNCTION F_EXEC_COMMAND(p_command in varchar2)
RETURN VARCHAR2 IS
/*
Data/Criacao: 27/01/2010
Autor: Joao Elias de Aguiar - Manutencao Comercial
Objetivo: Cria job no banco e executa comandos no Unix/Linux/Windows
*/
–
v_name_job varchar2(25) := ‘FEXECOM’ || dbms_session.unique_session_id;
–
BEGIN
–
dbms_scheduler.create_job(job_name => v_name_job
,job_type => ‘EXECUTABLE’
,job_action => p_command
,start_date => sysdate
,repeat_interval => ‘FREQ=DAILY’
,enabled => true
,auto_drop => false
,comments => ‘Run command in OS’);
–
dbms_scheduler.run_job (job_name => v_name_job);
dbms_scheduler.drop_job (job_name => v_name_job);
–
return(’Comando executado!’);
–
EXCEPTION
When others then
dbms_scheduler.drop_job (job_name => v_name_job);
return(’Erro ao executar comando: ‘ || sqlerrm);
END F_EXEC_COMMAND;
/

O nome do JOB é criado com a sessão concatenada no caso de várias execuções no mesmo momento.
Esta função pode ser executada em quaisquer sistemas operacionais. Basta que o comando enviado para execução seja próprio do S.O.

Bom, é isso aí pessoal.

Abs

Olá pessoal, tudo bem?

Aqui vai algumas dicas de Tuning para quem desenvolve e para analista que trabalham com essas operações…

1. Verificar as estatísticas das tabelas e dos índices.
2. Verificar também possíveis fragmentações nas tabelas.
3. Se uma determinada tabela sofre muito DML, talvez seja interessante fazer um Rebuild dos índices, pois isso acaba com a árvore do índice e se for bitmap, com o mapa binário.
4. Colocar as tabelas usadas na query na ordem da maior para a menor na cláusula From e na mesma ordem a cláusula Where.
5. Verificar a quantidade de registro que é recuperado na query, pois se for em Tela, ou seja, em uma consulta, caso retorne muitos registros, será que é necessário retornar tal quantidade de registro? Podemos usar algum filtro para recuperar registros mais novos de acordo com a regra de negócio, lembrando que é interessante haver índice para não fazer FULL na tabela.
6. Verificar colunas que são usadas em muitos processos como filtro na cláusula where para criação de índice para as mesmas.
7. Nos filtros de datas, usar trunc e between ou utilizar maior ou igual e menor que + 1 para teste de índices de acordo com a query. Em alguns casos é alterado o plano de execução e passa a utilizar índices.
8. Observar sempre ao criar uma query, o relacionamento das tabelas, seguindo sempre pela foreing keys e não pular tabelas nestas relações, pois o Oracle não utilizará índices das Fks.
9. Observar também a ordem das colunas e posições indexadas que foram criadas quando for utilizar tais colunas na cláusula where.
10. Sempre manter um bom padrão na escrita SQL, principalmente para funções analíticas e agregadoras. SUM, MIN, MAX, AVG e etc…
11. Sempre tente pegar pequenas porções de dados (Usar Tabelas e índices particionadas), pois quanto maior a quantidade, maior o tempo. Começe a pensar em SQL MODEL (Isso ajuda em performance) principalmente para report em EXCEL ou SUMARIZADO; Tente sempre criar índices que não satisfaça apenas 1 consulta, e sim 4 ou 5 sqls.
12. Em último caso se o plano de execução não há como diminuir o custom da query, poderá ser utilizado o hint PARALLEL(table), assim o processo é executado paralelamente.
13. E por último tente minimizar o uso de funções, use a NORMALIZAÇÃO AO SEU FAVOR.

Abraços

Olá pessoal, tudo bem?

Bom, faz um tempo que não posto nada em meu blog, mas aqui vai mais um para quem interessar.
Apenas um exemplo de um Forms com um bloco usando Transactional Triggers que em alguns casos é muito útil. Este exemplo usa algumas triggers de bloco onde é executado um cursor e também faz DML.

Passo 1
Criar um bloco e informar na propriedade Query Data Source Type: Transactional Triggers

Observe que quando o bloco for realizar consulta e alteração, ou seja, query e dml, informar nos dois lugares como no exemplo. Ou, em um dos lugares conforme a necessidade da tua aplicação.

Passo 2
Criar uma Program Units do tipo Package com as seguintes procedures como apresentadas no exemplo abaixo:

Primeiro cria-se a Specification:

PACKAGE teste IS

END;

Depois cria-se a Body:

PACKAGE BODY teste IS

END;

Passo 3
Adicionar as triggers ao bloco, conforme abaixo:

Adicionar nas triggers a chamada de cada procedure da package conforme exemplo abaixo:

Pronto! Agora é só executar o forms e assim os dados serão carregados e ao realizar alguma alteração disparará as triggers.

Abraços

Olá pessoal, tudo bem?

Vai aí uma função bem simples para recuperar números ou letras randomicamente de 10 caracteres.

Código:
CREATE OR REPLACE FUNCTION F_RANDOM(p_caract boolean default true) RETURN VARCHAR2 IS
v_param varchar2(15);
v_return varchar2(256);
BEGIN
for x in 1..1000000
loop
select trunc(to_number(to_char(systimestamp, ‘SSSSS.FF6′))*1000) into v_param from dual;
dbms_random.initialize(v_param);
v_return := to_char(abs(dbms_random.random-(x/1)));
dbms_random.terminate();
if p_caract then
v_return := translate(v_return,’0123456789′,’ABCDEFGHIJ’);
end if;
return(v_return);
end loop;
END F_RANDOM;

Exemplo com resultado em letras:
declare
v_result varchar2(30);
begin
v_result := f_random;
dbms_output.put_line(v_result);
end;
BEBGEDCBBC

Exemplo com resultado em números:
declare
v_result varchar2(30);
begin
v_result := f_random(false);
dbms_output.put_line(v_result);
end;
1071673929

Abraços,

Olá pessoal, tudo bem?

Faz um tempo já que não escrevo nenhum post, é que estava muito atarefado, mas acredito que este que descrevo abaixo vai ser de grande ajuda para todos. Provavelmente muitos já conhecem e sabem como fazer, mas este código é bem simples, basta compilar e utilizar.

Tenho um processo que lê arquivos textos e insere no banco de dados os registros, portanto, são vários arquivos e então, precisava listar os arquivos que estavam no diretório e assim poder importá-los usando o Utl_File.

Aqui vai o código que usei:

1° Passo: Compilar e importar algumas classes do Java
–Criando compilacao para as classes do Java
create or replace and compile java source named exec_list as
import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class test
{
public static oracle.sql.ARRAY listFiles (String source_dir)
{
File file = new File (source_dir);
if (file.exists () && file.isDirectory ())
{
String[] filenames = file.list ();
try
{
DriverManager.registerDriver (new OracleDriver ());
Connection conn = DriverManager.getConnection
(”jdbc:default:connection:”);
ArrayDescriptor array_desc =
ArrayDescriptor.createDescriptor (”FILE_LIST”, conn);
ARRAY array = new ARRAY (array_desc, conn, filenames);
return (array);
}
catch (SQLException e)
{
return (null);
}
}
else return (null);
}
}

2° Passo: Criando um Type para gravar os nomes dos arquivos
–Criado Type para gravacao da lista de arquivos do diretorio
create or replace type file_list as table of varchar2 (300);

3° Passo: Criando uma função para leitura do diretório e gravação no Type
–Criado Funcao para execucao do Java e recuperacao dos nomes dos arquivos
create or replace function listfiles (directory_name in varchar2)
return file_list as language java
name ‘test.listFiles(java.lang.String) return java.oracle.Array’;

4° Passo: Dando permissão para o Java
–Dando permissao no diretorio para o Java
CALL DBMS_JAVA.GRANT_PERMISSION
(’USER’, ‘java.io.FilePermission’, ‘DIRETORIO’,
‘read,write,execute,delete’);
Observe que para dar a permissão, é necessário informar o User e o Diretório onde está os arquivos.

5° Passo: Teste
DECLARE
FILES FILE_LIST;
BEGIN
FILES := LISTFILES (’&Directory’);
IF (FILES IS NULL) THEN
DBMS_OUTPUT.PUT_LINE (’Result set is null’);
ELSE
FOR I IN 1 .. FILES.COUNT LOOP
DBMS_OUTPUT.PUT_LINE (FILES (I));
END LOOP;
END IF;
END;

Neste exemplo, chamo a função passando o diretório e abaixo faço um FOR mostrando os nomes dos arquivos.

Nota: É necessário verificar se os serviços Java estão ativos no banco de dados.

Espero que ajudem.

Abraços.

Olá pessoal, tudo bem?

Saíndo um pouco dos processos de banco de dados que citei nos post anteriores, vamos agora trabalhar com um item do FORMS da Oracle que é criar um DataBlock baseado em Store Procedure.

Esta opção permite tratar os dados retornados do banco de uma forma rápida ou até mesmo, no próprio banco de dados. Esta operação nos fornece uma falicidade maior pois toda regra e inteligência do processo ficará dentro do Código PL/SQL. Também, nos fornece uma grande velocidade no tratamento das informações.

Existe dois modos de criar a procedure que retornará os dados para a tela do usuário. Uma é criar a package dentro do Forms e a outra é no banco de dados, sabendo que esta criada no banco de dados ficará com uma performance melhor, pois não teremos tráfego de dados na rede durante o tratamento das informações, ou seja, caso você precise recuperar os dados do banco e depois fazer um Loop para tratar as informações ou recuperar outros dados dentro do Loop, a package estando no banco, todo o tratamento vai ser no servidor do banco oracle e não na máquina do usuário.

Este exemplo que vamos usar, lista em um bloco do forms o texto “Arquivo 1”, depois “Arquivo 2”, e assim até o 10.

Primeiro passo: Criar uma Package com um procedure no forms ou no banco de dados conforme abaixo:

PACKAGE PCK_LIST_ARQ IS

TYPE R_LIST IS RECORD(NOME VARCHAR2(300));
TYPE T_LIST IS TABLE OF R_LIST INDEX BY BINARY_INTEGER;
PROCEDURE LIST_ARQ(P_LIST IN OUT T_LIST);

END PCK_LIST_ARQ;

PACKAGE BODY PCK_LIST_ARQ IS

PROCEDURE LIST_ARQ(P_LIST IN OUT T_LIST) IS
BEGIN
FOR i IN 1..10
LOOP
P_LIST(i).NOME := ‘ARQUIVO ‘||LPAD(i,2,’0′);
END LOOP;
END LIST_ARQ;

END PCK_LIST_ARQ;

Nota que estou usando Types do tipo Record que é os registros, e outro do tipo Table baseado no anterior, ou seja, é como se fosse uma tabela com as colunas, mas isso em memória. Criei também a Procedure onde abro um Loop e alimento a coluna da tabela (Type) como demonstrado. Repare que o type P_LIST é minha tabela, a letra i é meu indexador, sempre entre parenteses, e por fim, a coluna deste type. O i é apenas um contador que ao ser declaro no For, o valor atribuido é 1, 2, 3, 4, 5… até 10 que é onde informei de 1..10.

Segundo passo: Crie um Block no forms utilizando o Data Block Wizard e escolha o tipo Procedure conforme abaixo:

Terceiro passo: Clique na aba Query e informe o nome da Package + o nome da Procedure, clique em Refresh, escolha a coluna e mude ela de lado, informe no argumento do parâmetro de retorno da procedure uma variável.

Em seguida clique em Finish. Vai aparecer outra janela do Forms que irá solicitar o canvas, o tipo, etc.

As abas Insert, Update, Delete, Lock são usadas caso o teu bloco do forms irá realizar uma operação de DML. Mas segue o mesmo conceito.

O próprio Wizard do forms criará o bloco, o item, e a trigger de QUERY, que no nosso caso não estamos fazendo DML via procedure. Segue abaixo como ficará:

Observações:

O nome das colunas no block, aconselho colocar o mesmo nome do que foi criado no Type da Package.

Abraços,

Olá pessoal, tudo bem?

É até meio estranho este título, mas trabalhei em um projeto onde documentava e especificava os processos para COBOL, e assim, pude ver que posso usar algumas formas de como a especificação para COBOL é feita para uso no PL/SQL.

Antes de descrever tais formas, vamos conhecer um pouco do COBOL com esse simples exemplo de instrução SQL especificada abaixo, convertendo-a para arquivos sequenciais, que é a forma que o COBOL trabalha.

Instrução SQL:

select lg.descr_logradouro
, br.descr_bairro
, ci.descr_cidade
, es.descr_estado
, pa.descr_pais
from pais pa
, estado es
, cidade ci
, bairro ba
, logradouro lg
where pa.id_pais = es.id_pais
and es.id_estado = ci.id_estado
and ci.id_cidade = br.id_cidade
and br.id_bairro = lg.id_bairro;

Para especificar esta simples instrução SQL para COBOL, teremos que construir manualmente o que o banco de dados faz sozinho. Umas das vantagens do SQL sobre o COBOL é esta, a forma simples de contruir as instruções e não se preocupar com que o banco irá fazer, porém, devemos sim observar tais formas de como escrever a instrução SQL, pois da maneira correta, o banco pode nos oferecer uma melhor performance na execução.

Especificando em COBOL:

Quero lembrar, para os que não sabem, o COBOL trabalha com arquivos textos sequenciais, mesmo que a informação esteja armazenada em um banco de dados, é necessário a extração destes dados para pode trabalhar.

Abaixo um fluxograma bem comum para um programador COBOL:

Compreendendo o fluxograma e obtendo informações importantes para o SQL.

No fluxograma acima, os cículos representam os arquivos textos, e aqueles que iniciam com “BD”, representa as tabelas, sendo: BD01 = PAIS, BD02 = ESTADO, BD03 = CIDADE, BD04 = BAIRRO e BD05 = LOGRADOURO, conforme a instrução SQL citada acima. Os quadrados representam programas em COBOL.

Note que para obter o resultado da instrução SQL citada acima, em COBOL, é necessário ter 9 programas. Portanto, é mais complicado trabalhar em COBOL, mas mesmo assim, se tornam rápidos os processos.

Entendendo programa a programa COBOL:

Nome: P100 – Finalidade: Ordenar o arquivo BD01 pela chave que neste caso é a coluna ID_PAIS e grava a saída no arquivo A100.
Nome: P200 – Finalidade: Ordenar o arquivo BD02 pela chave que neste caso é a coluna ID_PAIS e grava a saída no arquivo A200.
Nome: P300 – Finalidade: Faz o JOIN entre os arquivos de saída A100 e A200, grava no arquivo de saída A300 as colunas DESCR_PAIS do arquivo A100, DESCR_ESTADO e ID_ESTADO do arquivo A200, ordena o arquivo de saída A300 pela chave do próximo arquivo que irá fazer o JOIN com este, que neste caso é a coluna ID_ESTADO.
Nome: P400 – Finalidade: Ordenar o arquivo BD03 pela chave que neste caso é a coluna ID_ESTADO e grava a saída no arquivo A400.
Nome: P500 – Finalidade: Faz o JOIN entre os arquivos de saída A300 e A400, grava no arquivo de saída A500 as colunas DESCR_PAIS, DESCR_ESTADO do arquivo A300, DESCR_CIDADE e ID_CIDADE do arquivo A400, ordena o arquivo de saída A500 pela chave do próximo arquivo que irá fazer o JOIN com este, que neste caso é a coluna ID_CIDADE.
Nome: P600 – Finalidade: Ordenar o arquivo BD04 pela chave que neste caso é a coluna ID_CIDADE e grava a saída no arquivo A600.
Nome: P700 – Finalidade: Faz o JOIN entre os arquivos de saída A500 e A600, grava no arquivo de saída A700 as colunas DESCR_PAIS, DESCR_ESTADO, DESCR_CIDADE do arquivo A500, DESCR_BAIRRO e ID_BAIRRO do arquivo A600, ordena o arquivo de saída A700 pela chave do próximo arquivo que irá fazer o JOIN com este, que neste caso é a coluna ID_BAIRRO.
Nome: P800 – Finalidade: Ordenar o arquivo BD05 pela chave que neste caso é a coluna ID_BAIRRO e grava a saída no arquivo A800.
Nome: P900 – Finalidade: Faz o JOIN entre os arquivos de saída A700 e A800, grava no arquivo de saída A900 as colunas DESCR_PAIS, DESCR_ESTADO, DESCR_CIDADE, DESCR_BAIRRO do arquivo A700, DESCR_LOGRADOURO do arquivo A800.

Os JOINs entre os arquivos são feitos pela colunas que são PK nas tabelas, mas porque a ordenação nos arquivos? No COBOL, o modo de fazer o JOIN entre dois arquivos, só é possível se as chaves estiverem ordenadas iguais, ou seja, o COBOL lê o arquivo texto somente uma única vez e compara a coluna especificada que é chave entre os dois arquivos, a saber, num arquivo de configuração onde são especificados os tamanhos e tipo de dados, como se fosse a estrutura da tabela no banco.

Repare que as colunas que são necessárias no resultado final, e como está em outro arquivo(tabela), após recuperar esta coluna, ela vem sendo transportada de arquivo para arquivo até sair no arquivo final. Exemplo: Coluna DESCR_PAIS, ela está nos arquivos A100, A300, A500, A700 e no arquivo final A900.

Conclusões:

O banco de dados faz todo esse processo que está desenhado para COBOL, mas sem que o usuário veja, mas é importante, como disse entender essa estrutura, que lembra o plano de execução do SQL.

Baseado nessa afirmação, vou citar algumas considerações:

- Importância da ordem das tabelas na cláussula FROM: Se o banco de dados prepara a mesma estrutura de planejamento do COBOL, podemos afimar que é importante informar nesta cláussula, as tabelas maiores primeiramente, ou seja, “FROM tabela grande, tabela média, tabela pequena”. Para quem não sabe, o banco de dados lê as tabelas na ordem inversa, “tabela pequena, tabela média, tabela grande”.

- As colunas na cláussula WHERE: Informar na mesma ordem das tabelas, considerando que, se houver condições que não seja JOIN, colocá-las abaixo do JOIN da tabela em questão. Neste caso, estamos utilizando PKs que contém apenas uma única coluna, mas no caso onde têm várias colunas, informar nesta cláussula, na mesma ordem das colunas da PK, sempre lembrando que o banco lê a instrução de modo inverso, ou seja, de trás para frente ou de baixo para cima.

- No caso de condição, por exemplo, recuperar todos os endereços do estado de São Paulo, informar assim: ‘SP’ = tabela.coluna, pois se nesta coluna haver índice, o banco irá fazer esta condição utilizando o índice.

- Na cláussula ORDER BY: Informar na cláussula SELECT a mesma ordem desta cláussula, ORDER BY.

- Quando usar condições que envolvem campos do tipo DATE, usar o mesmo formato que está parametrizado no banco de dados, na maioria dos casos, ‘DD-MON-YYYY’.

- Lembre-se, USEM comentários necessários dentro de seu código, alinhe a estrutura de seu código, IF com END IF, LOOP com END LOOP, se escrever em minúscula, siga sempre escrevendo assim, para o banco, ele diferencia, posições de colunas e tabelas no select, entre outros. Isso é importante, pois toda vez que executar uma instrução SQL, o banco de dados monta um plano de execução, e se você sempre usa as mesmas regras, vai ser mais rápido o retorno.

Espero que tenho esclarecido a forma de como banco de dados trabalha, e comparando ao COBOL, esse é o motivo do COBOL processar mais rápido, pois é sempre assim, não se pode mudar tais regras.

Abraços,

Mastersaf
Olá pessoal, tudo bem com vocês?
Estou trabalhando num projeto do Sped Fiscal, fazendo integração do ERP próprio com o Mastersaf, e gostaria de abordar algumas definições que tomei na integração destes dados.

Layouts da Mastersaf

Para melhor controle e também para evitar erros na programação, criei algumas tabelas para suporte ao meu processo de banco.

1. Tabela LAYOUTS
Esta tabela contém todos os layouts do Mastersaf, com as seguintes colunas:
- Nome do Layout
- Descrição do Layout
- Nome do Layout Pai (Auto-relacionamento com a mesma tabela)

2. Tabela LAYOUTS_COLUNAS
Esta tabela contém as informações de cada Layout, contendo as seguintes colunas:
- Nome do Layout
- Nome do Campo
- Descrição do Campo
- Comentário
- Posição Inicial no Arquivo Texto
- Tamanho do Campo
- Quantidade de Casas Decimais
- Tipo de Dado

Package/Procedures

Criei várias procedures dentro de um package para geração dos arquivos para o Mastersaf. As querys de recuperação dos dados do ERP, construí de forma dinâmica, ou seja, como string, e então usei o execute immediate com bulck collect para inserção destes dados em memória. Porém, o bulck collect só funciona a partir da versão 9i do banco Oracle. Em versões anteriores, pode se usar tabelas globais temporárias em memória. Para ambos os casos, é necessário discutir com a equipe de DBA se o banco fornece recursos suficientes para alocação destes dados em memória.

Exemplo com bulk collect:
g_sql := ’select coluna1
, coluna2
, coluna3
from tabelas
where condicoes’;

execute immediate g_sql
bulk collect into gt_coluna1
, gt_coluna2
, gt_coluna3;

Exemplo com global temporary:
g_sql := ‘insert into tabela_temporaria(coluna1,coluna2,coluna3)
select coluna1, coluna2, coluna3 from tabelas’;

No uso de GLOBAL TEMPORARY, é necessário analisar se ao criar estas tabelas, tem que criar com PRESERVE ROWS ON COMMIT, pois se não criar desta maneira, ao realizar um commit dentro do processo, os registros que estão em memória serão apagados.

Em todas as procedures que geram os arquivos, faço a chamada de uma determinada procedure, que recupera todas as informações daquele Layout em questão que irei processar e carrega em memória para melhor performance.
Nesta procedure, também crio uma string com os caracteres em branco no tamanho de cada campo daquele layout, separados por um caractere especial, pois depois, eu só preciso substituir os espaços em branco pelo valor correto daquele campo.

Para substituição dos espaços por valores, obedecendo o tipo de dados se é caractere ou numérico, com casas decimais ou não, tamanho do campo, criei uma outra procedure onde recebe o nome do campo no layout em questão, e assim faz todo tratamento necessário.

No final do meu processo, apenas jogos essa string para o arquivo texto usando o UTL_FILE.

Achei mais fácil fazer assim, pois para todos os outros layouts, quase não tenho trabalho de informar tamanho de campo, tratar caracteres especiais e outros, apenas construa a query que irá recuperar os dados e chamo esta procedure para gerar o arquivo texto. Assim, não há informações fixas dentro do código.

Abaixo as deixo estas as procedures para usar como exemplo.
http://profissionaloracle.com.br/blogs/jelias/files/2009/07/procedures_mastersaf2.pdf

Abraços,

Olá pessoal, tudo bem com vocês?
Este é meu primeiro post em meu blog e gostaria que comentassem sobre o assunto, porque por mais que sabemos sobre determinados assuntos, sempre temos que ter a mente aberta para novas sugestões e dicas, e somente assim poderemos crescer profissionalmente, e mesmo em nossa vida pessoal, isso também irá nos ajudar bastante nas decisões que teremos que tomarmos.

Quero abordar sobre esse assunto, pois vejo como parte importantíssima no processo de criação de um sistema que utiliza banco de dados, e quase tudo que iremos ter no sistema, dependerá de como a modelagem de dados foi desenhada.

Neste post irei exemplificar como criar uma arquitetura, que a meu ver, boa para se trabalhar e outra ruim, mostrando as vantagens e desvantagens de cada uma.

Escopo

Após as primeiras etapas antes da criação de uma modelagem de dados, como por exemplo, levantamento de requisitos, casos de uso, especificações funcionais, estudo de banco, entre outras, é iniciar baseado nos documentos levantados por uma equipe de analistas de negócios, administração de bancos, infra-estrutura, a modelagem de dados.
Podemos usar para modelar, vários programas, como exemplo, o próprio Designer da Oracle, que já é integrado com o banco, poupando a necessidade de criarmos os scripts manualmente. Assim, depois de abordadas tais observações, vamos ao que interessa.

Análise e Criação

As tabelas que iremos modelar é de uso comum em muitos sistemas, é um controle do cadastro de uma pessoa.
Nota-se que na simples análise de definir quais tabelas iremos ter, muitas modelagens deste módulo são desenhas de forma incorreta a meu ver, abaixo exemplos que pude presenciar:

Exemplo 1
CREATE TABLE PESSOA
(NOME VARCHAR2(50) NOT NULL CONSTRAINT PESSOA_PK PRIMARY KEY
,ENDERECO VARCHAR2(100)
,BAIRRO VARCHAR2(50)
,CEP NUMBER(8)
,CIDADE VARCHAR2(50)
,PAIS VARCHAR2(50));

Repare que existe uma única tabela que contempla todos dados referentes àquela pessoa, mas vamos analisar melhor este exemplo e quero colocar minhas opiniões.

1. O uso da coluna NOME como PRIMARY KEY da tabela não está correto, pois como é um campo VARCHAR2(50), faço as seguintes observações:
a. Irá ocupar muito espaço em disco para armazenar os dados.
b. Piorará o item ‘a’ quando esta PK for relacionada com outras tabelas.
c. Mesmo que tenha índice nesta coluna, a recuperação dos dados será mais lenta, pois se trata de um campo caractere, com um tamanho grande.
d. Este campo sendo PK, não será possível, depois do registro estiver relacionado com outras tabelas, a atualização deste campo.
2. Apenas uma coluna para gravar o endereço da pessoa, ou seja, no campo ENDERECO irá ser gravado o nome da rua, o numero e o complemento, deste modo, quando for preciso realizar uma checagem das informações com outra base de dados, por exemplo, receita federal ou correios, terá o problema seriíssimo para poder separar o numero e complemento do nome da rua, pois este cadastro estará na mão do usuário, e sem culpa, irá informar como quiser o endereço da pessoa.
3. Os campos BAIRRO, CIDADE e PAIS, não deveriam estar na mesma tabela, pois eles assim, o usuário final poderá cadastrar a mesma cidade, por exemplo, de diversas maneiras diferentes, como também para os outros campos. E também, levando em conta os tipos de campos e tamanhos, e a quantidade de vezes que irão repetir para os registros que coincidem ser igual nestes campos, a armazenagem de dados em disco será totalmente desnecessária.
4. Sempre analise quais as colunas que podem ser NOT NULL, pois ganhará tempo em performance e segurança dos dados gravados.

Exemplo 2
Tabelas:
• ESTADO – Estados
CREATE TABLE ESTADO
(NOME VARCHAR2(50) NOT NULL
,PAIS VARCHAR2(50) NOT NULL);

ALTER TABLE ESTADO ADD CONSTRAINT PK_ESTADO PRIMARY KEY (NOME, PAIS);
• CIDADE - Cidades
CREATE TABLE CIDADE
(NOME VARCHAR2(50) NOT NULL
,ESTADO VARCHAR2(50) NOT NULL
,PAIS VARCHAR2(50) NOT NULL);

ALTER TABLE CIDADE ADD CONSTRAINT PK_CIDADE PRIMARY KEY (NOME, ESTADO, PAIS);
ALTER TABLE CIDADE ADD CONSTRAINT FK_CIDADEESTADO
FOREIGN KEY (ESTADO, PAIS) REFERENCES ESTADO (NOME, PAIS);
• PESSOA – Pessoas
CREATE TABLE PESSOA
(NOME VARCHAR2(50) NOT NULL CONSTRAINT PESSOA_PK PRIMARY KEY
,ENDERECO VARCHAR2(100)
,BAIRRO VARCHAR2(50)
,CEP NUMBER(8)
,CIDADE VARCHAR2(50) NOT NULL
,ESTADO VARCHAR2(50) NOT NULL
,PAIS VARCHAR2(50) NOT NULL);

ALTER TABLE PESSOA ADD CONSTRAINT PK_PESSOA PRIMARY KEY (NOME);
ALTER TABLE PESSOA ADD CONSTRAINT FK_PESSOACIDADE
FOREIGN KEY (CIDADE, ESTADO, PAIS) REFERENCES CIDADE (NOME, ESTADO, PAIS);

Neste exemplo, quero colocar meu ponto de vista, citando abaixo algumas observações, além das que se repetem do primeiro exemplo:

1. Repare que é usado PRIMARY KEY COMPOSTAS, e este é um problema grave quando a tabela começa a ter tabelas filhas, e filhas das filhas, quanto mais descer, pior fica.
2. Quando se cria o relacionamento entre as tabelas, obrigatoriamente tem que trazer a PK da tabela pai para a tabela filha, e assim, a quantidade de colunas só vai aumentando, consumindo mais espaço em disco, e derrubando a performance das QUERYS na recuperação dos dados, pois são mais colunas que o banco irá ter que verificar, e neste exemplo fica pior porque as colunas são VARCHAR2.

Uma boa modelagem

Vamos agora para uma boa modelagem deste módulo de cadastro de pessoas. As tabelas que iremos criar demonstram as mesmas informações do exemplo anterior, mas com uma grande vantagem na performance e armazenagem dos dados no disco.

Tabelas:
• PAIS – Países
CREATE TABLE PAIS
(ID_PAIS NUMBER(10) NOT NULL
,CODIGO VARCHAR2(3) NOT NULL
,DESCR VARCHAR2(50) NOT NULL);

ALTER TABLE PAIS ADD CONSTRAINT PK_PAIS PRIMARY KEY (ID_PAIS);
ALTER TABLE PAIS ADD CONSTRAINT UK_PAISCD UNIQUE (CODIGO);

• ESTADO – Estado do País
CREATE TABLE ESTADO
(ID_ESTADO NUMBER(10) NOT NULL
,ID_PAIS NUMBER(10) NOT NULL
,CODIGO VARCHAR2(3) NOT NULL
,DESCR VARCHAR2(20) NOT NULL);

ALTER TABLE ESTADO ADD CONSTRAINT PK_ESTADO PRIMARY KEY (ID_ESTADO);
ALTER TABLE ESTADO ADD CONSTRAINT UK_ESTADOCD UNIQUE (CODIGO);
ALTER TABLE ESTADO ADD CONSTRAINT FK_ESTADOPAIS
FOREIGN KEY (ID_PAIS) REFERENCES PAIS (ID_PAIS);

• CIDADE – Cidade do Estado
CREATE TABLE CIDADE
(ID_CIDADE NUMBER(10) NOT NULL
,ID_ESTADO NUMBER(10) NOT NULL
,CODIGO VARCHAR2(3) NOT NULL
,DESCR VARCHAR2(50) NOT NULL
,CEP_INI NUMBER(8) NOT NULL
,CEP_FIM NUMBER(8) NOT NULL);

ALTER TABLE CIDADE ADD CONSTRAINT PK_CIDADE PRIMARY KEY (ID_CIDADE);
ALTER TABLE CIDADE ADD CONSTRAINT UK_CIDADECD UNIQUE (CODIGO);
ALTER TABLE CIDADE ADD CONSTRAINT FK_CIDADEESTADO
FOREIGN KEY (ID_ESTADO) REFERENCES ESTADO (ID_ESTADO);

• BAIRRO – Bairro da Cidade
CREATE TABLE BAIRRO
(ID_BAIRRO NUMBER(10) NOT NULL
,ID_CIDADE NUMBER(10) NOT NULL
,DESCR VARCHAR2(30) NOT NULL
,CEP_INI NUMBER(8) NOT NULL
,CEP_FIM NUMBER(8) NOT NULL);

ALTER TABLE BAIRRO ADD CONSTRAINT PK_BAIRRO PRIMARY KEY (ID_BAIRRO);
ALTER TABLE BAIRRO ADD CONSTRAINT FK_BAIRROCIDADE
FOREIGN KEY (ID_CIDADE) REFERENCES CIDADE (ID_CIDADE);

• LOGRADOURO – Logradouro do Bairro
CREATE TABLE LOGRADOURO
(ID_LOGRADOURO NUMBER(10) NOT NULL
,ID_BAIRRO NUMBER(10) NOT NULL
,DESCR VARCHAR2(60) NOT NULL);

ALTER TABLE LOGRADOURO ADD CONSTRAINT PK_LOGRADOURO PRIMARY KEY (ID_LOGRADOURO);
ALTER TABLE LOGRADOURO ADD CONSTRAINT FK_LOGRADBAIRRO
FOREIGN KEY (ID_BAIRRO) REFERENCES BAIRRO (ID_BAIRRO);

• TIPO_PESSOA – Tipo de Pessoas
CREATE TABLE TIPO_PESSOA
(ID_TPPESSOA NUMBER(3) NOT NULL
,CODIGO VARCHAR2(3) NOT NULL
,DESCR VARCHAR2(15) NOT NULL);

ALTER TABLE TIPO_PESSOA ADD CONSTRAINT PK_TPPESSOA PRIMARY KEY (ID_TPPESSOA);

• PESSOA – Pessoas (Cadastro geral, incluindo, usuários)
CREATE TABLE PESSOA
(ID_PESSOA NUMBER(10) NOT NULL
,NOME VARCHAR2(60) NOT NULL
,ID_TPPESSOA NUMBER(3) NOT NULL);

ALTER TABLE PESSOA ADD CONSTRAINT PK_PESSOA PRIMARY KEY (ID_PESSOA);
ALTER TABLE PESSOA ADD CONSTRAINT FK_TPPESSOAPESSOA
FOREIGN KEY (ID_TPPESSOA) REFERENCES TIPO_PESSOA (ID_TPPESSOA);

• TIPO_ENDERECO – Tipo de Endereços
CREATE TABLE TIPO_ENDER
(ID_TPENDER NUMBER(3) NOT NULL
,CODIGO VARCHAR2(1) NOT NULL
,DESCR VARCHAR2(15) NOT NULL);

ALTER TABLE TIPO_ENDER ADD CONSTRAINT PK_TPENDER PRIMARY KEY (ID_TPENDER);

• ENDERECO – Endereço da Pessoa
CREATE TABLE ENDERECO
(ID_ENDERECO NUMBER(10) NOT NULL
,ID_PESSOA NUMBER(10) NOT NULL
,ID_TPENDER NUMBER(3) NOT NULL
,ID_LOGRADOURO NUMBER(10) NOT NULL
,NUMERO VARCHAR2(8) NOT NULL
,COMPLEMENTO VARCHAR2(20)
,CEP NUMBER(8) NOT NULL);

ALTER TABLE ENDERECO ADD CONSTRAINT PK_ENDERECO PRIMARY KEY (ID_ENDERECO);
ALTER TABLE ENDERECO ADD CONSTRAINT FK_ENDERPESSOA
FOREIGN KEY (ID_PESSOA) REFERENCES PESSOA (ID_PESSOA);
ALTER TABLE ENDERECO ADD CONSTRAINT FK_ENDERTPENDER
FOREIGN KEY (ID_TPENDER) REFERENCES TIPO_ENDER (ID_TPENDER);
ALTER TABLE ENDERECO ADD CONSTRAINT FK_ENDERLOGRAD
FOREIGN KEY (ID_LOGRADOURO) REFERENCES LOGRADOURO (ID_LOGRADOURO);

Analisando este no exemplo, a princípio pode ser mais trabalhoso e/ou mais difícil de modelar deste modo, mas como disse, a modelagem de dados é muito importante, então nem que perca um bom tempo, acredite você vai ganhar muito depois.
Vou citar algumas observações minhas deste exemplo citado acima:

1. Repare que todas as tabelas existem apenas uma coluna na PRIMARY KEY, isso ganha tempo quando for relacionar as tabelas, e também principalmente, os campos são numéricos e os tamanhos das colunas não necessitam ter um tamanho grande, poupando assim, espaço em disco e ganho de performance.
2. Considerando que o nome da coluna na PK das tabelas é idêntico, você pode ganhar em tempo quando for criar uma instrução de SELECT, pois fica mais fácil identificar uma relação sem a necessidade de verificar a FK.

SELECT PE.NOME
, LG.DESCR ENDERECO
, EN.NUMERO
, EN.COMPLEMENTO
, EN.CEP
, BR.DESCR BAIRRO
, CI.DESCR CIDADE
, ES.DESCR ESTADO
, PA.DESCR PAIS
FROM PAIS PA
, ESTADO ES
, CIDADE CI
, BAIRRO BR
, LOGRADOURO LG
, ENDERECO EN
, TIPO_ENDER TE
, PESSOA PE
, TIPO_PESSOA TP
WHERE PA.ID_PAIS = ES.ID_PAIS
AND ES.ID_ESTADO = CI.ID_ESTADO
AND CI.ID_CIDADE = BR.ID_CIDADE
AND BR.ID_BAIRRO = LG.ID_BAIRRO
AND LG.ID_LOGRADOURO = EN.ID_LOGRADOURO
AND EN.ID_TPENDER = TE.ID_TPENDER
AND TE.CODIGO = 1 –RESIDENCIAL
AND EN.ID_PESSOA = PE.ID_PESSOA
AND PE.ID_TPPESSOA = TP.ID_TPPESSOA
AND TP.CODIGO = 1 –USUARIOS
AND EN.ID_PESSOA = 325; –CODIGO DA PESSOA

Observe o plano de execução desta QUERY, está utilizando todos os índices corretamente.

3. Os dados do endereço estão separados, a saber, o endereço em si, o número e o complemento, assim quando for desenvolver na aplicação, pode-se associar esta modelagem com uma base de dados do correios, ou seja, o usuário informaria apenas o numero do CEP e o sistema buscaria todas as demais informações.
4. Nas tabelas CIDADE e BAIRRO existem as duas colunas de CEP, estas colunas seriam usadas para validar se para aquele bairro ou cidade, o CEP digitado confere com o cadastrado no sistema, como é na base dos correios.
5. Observe que todas as PRIMARY KEYS iniciam com a sigla ID, esta sigla você pode utilizar para identificar as colunas que são alimentadas pelo sistema e não pelo usuário, no nosso caso, estas colunas são alimentadas por SEQUENCE de banco.
6. Para as tabelas que necessitam por regras de negócio o usuário informar um código para cada registro, criou a coluna CODIGO. Por exemplo, na tabela CIDADE pode-se informar nesta coluna, o Código do Município no IBGE.
7. Enfim, pode-se observar que não há informação repetida, e isso para armazenagem de dados no disco é muito bom.

Gráfico

Conclusão
Analise muito bem as regras de negócios, as documentações de caso de uso e especificações, procure sempre prever todas as maneiras possíveis de entradas de dados e relação entre as informações, somente depois desta análise completa, modele sua base de dados, pois no futuro para se alterar, com certeza vai ser mais complicado, e às vezes, quase impossível de corrigir.

Um abraço,