Atenção: esse artigo tem um vídeo complementar. Clique e assista!
Modelando um Portal de Vendas para WEB no Oracle 10g - Parte 1
O artigo descreve a implementação de um portal de vendas para web em uma base de dados Oracle. Especificamente, nesta segunda parte veremos como implementar stored procedures, stored functions, views e packages em um banco de dados Oracle.
Para que serve:
Este tipo de solução apresentada serve para implementar uma aplicação utilizando os objetos no banco de dados Oracle 10g.
Em que situação o tema é útil:
Este tema pode ser utilizado como referência para implementação de objetos de banco de dados ou em utilização de definições de padrões de modelagem para sistemas implementados em banco de dados Oracle.
Neste artigo, daremos continuidade à implementação de nossa loja virtual, apresentada na primeira parte deste artigo publicada na edição 77 da SQL Magazine. Será demonstrado como utilizar stored procedures e stored functions do banco de dados para validar as regras de negocio e efetuar operações de manutenção nos dados (ex: inserções, atualizações ou exclusões), prevendo a reutilização do código. Além disso, iremos criar algumas views para possibilitar a leitura padronizada dos dados que compõem as tabelas de nosso banco de dados.
Relembrando o banco de dados de nossa loja virtual
No artigo anterior, criamos o modelo físico do banco de dados de nosso portal. Neste momento, modelamos até a 3º Forma Normal para um banco de dados Oracle 10g utilizando o Erwin 7.0 como ferramenta de modelagem, utilizando a notação IDEF1X (Integration Definition for Information Modeling).
Em nosso banco de dados, foram criadas quatro subáreas para agrupar os objetos do banco (apresentadas na Figura 1), e utilizamos prefixos para identificar cada uma. Para cada subárea, criamos uma tablespace de dados e outra de índices.
Figura 1. Relação de Tablespaces definidas em nosso banco de dados
As tabelas que compõem nosso banco de dados foram classificadas em quatro subáreas:
• Cadastro de Produtos – PRD: contém tabelas responsáveis pelas informações do cadastro dos produtos (Figura 2).
Figura 2. Relação
de tabelas da Subárea de Produtos.
• Cadastro de Clientes – CLI: contém tabelas responsáveis pelas informações do cadastro de clientes (Figura 3).
Figura 3. Relação de tabelas da Subárea de Clientes.
• Administração do Portal – ADM: contém as tabelas responsáveis pelas informações de administração do portal (Figura 4).
Figura 4. Relação de tabelas da Subárea de Administração do Portal.
• Marketing – MKT: contém tabelas responsáveis pelas informações de marketing do portal (Figura 5).
Figura 5. Relação de tabelas da Subárea de Marketing.
Detalhes sobre cada tabela podem ser obtidos na Parte 1 deste artigo.
Parâmetros e Variáveis
Como boa prática, todo nome do parâmetro receberá o prefixo “P_” seguido de um nome identificador de seu conteúdo. Por exemplo, um parâmetro referente a nome será identificado como P_NOME.
Para variáveis, a regra é a mesma mudando apenas o prefixo para “V_”. Por exemplo, uma variável de valor do produto, terá o nome V_VLR_PRODUTO.
Definindo Packages
A utilização de packages facilita na estruturação da aplicação, agrupando stored procedures, stored functions e variáveis referentes a um determinado processo.
Em nossa aplicação, as regras de negócios ficarão no banco de dados. A utilização das validações das regras de negocio no banco de dados permite alterar facilmente o Front End (interface) da aplicação sem a necessidade de reescrita de código.
Todas as operações de manipulação de dados serão efetuadas através de stored procedures (SP’s) e stored functions (SF’s) pré-determinadas. O Front End apenas invocará tais SP’s /SF’s do banco de dados. Com isso, não haverá comandos de INSERT, UPDATE ou DELETE no código fonte de nossa aplicação.
Para nomeação de packages, utilizaremos o seguinte template apresentado na Listagem 1.
Listagem 1. Template para construção das Packages
CREATE OR REPLACE PACKAGE [prefixo_tabela]_[nome_tabela]_PCK A Figura 6 apresenta a lista completa com todas as packages construídas para o nosso projeto de loja virtual. O critério adotado para definição das packages foi a definição de uma para cada tabela que compõe nosso banco de dados.
Figura 6. Relação de Packages Portal.
Com a definição das packages, cada comando de manipulação de dados deverá ser executado através de um programa contido na package respectiva à sua tabela.
Operações de Inserção dos Dados
Neste momento, precisamos criar uma stored procedure/function (para cada tabela) responsável por efetuar o comando de INSERT, uma outra para comandos de UPDATE e outra para DELETE.
Percebemos que em nosso portal, existem dois tipos de inserção. Um tipo quando a chave primária é gerada automaticamente pela sequence, contida na trigger da tabela. E o outro tipo ocorre quando a aplicação informa a composição da chave primária.
Para o primeiro tipo, deveremos receber como retorno o código da chave primária gerada. Logo, utilizaremos stored function para realizar tal operação, que por regra, sempre deve retornar algum valor.
Veja na Listagem 2 o template que iremos adotar para criar a função de inserção em uma tabela.
Observe na listagem que esta função deve receber como parâmetro o conteúdo de todas as colunas da tabela que não compõe a chave primária. O tipo dos parâmetros deve ser herdado do mesmo tipo contido na tabela. Para isso, utilizaremos o comando %type. O valor de retorno da função também deve seguir o tipo da coluna da chave primária da tabela.
Precisaremos também de uma variável para receber o valor da chave primária gerada pela sequence no momento do INSERT. Esta variável será utilizada como retorno da função. Seguindo a regra de nomeação das variáveis, esta variável terá o prefixo “V_” seguido pelo nome da coluna da chave primaria. Seu tipo também deve seguir o mesmo contido na tabela.
Listagem 2. Template para construção de stored function de INSERT.
FUNCTION INS_[nome_da_tabela](P_[coluna_dado] IN [nome_da_tabela]. [coluna_dado]%type)
RETURN [nome_da_tabela]. [coluna_da_pk]%type
IS
V_[coluna_da_pk] [nome_da_tabela]. [coluna_da_pk]%type;
BEGIN
INSERT INTO [nome_da_tabela] ([coluna_dado]) VALUES (P_[coluna])
RETURNING [coluna_da_pk] INTO V_[coluna_da_pk];
RETURN V_[coluna_da_pk];
END INS_[nome_da_tabela]; ... 
