Atenção: esse artigo tem um vídeo complementar. Clique e assista!

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];  ...