Pular para o conteúdo

Escopo de Declaração de Variáveis no Oracle: Benefícios e Exemplos de Utilização

Escopo de declaração de variáveis no Oracle

Olá, pessoal, hoje vamos falar um pouco sobre escopo de declaração de variáveis no Oracle, e citarei alguns beneficios e exemplos de sua utilização.

Para entender sobre sintaxe e tipos de variáveis existentes no Oracle, sugiro o artigo Variáveis no PL/SQL.

Para criar um código de qualidade, estruturado,  utilizando conceitos de reutilização, é muito importante entender os tipos de declaração de escopo que as variáveis podem utilizar no Oracle. O escopo determina a visibilidade das variáveis durante a execução dos programas.

Existem 3 tipos de escopo de declaração de variáveis:

01. Variáveis Globais

Têm a visibilidade global, podem ser acessadas por qualquer programa durante a execução da mesma sessão.

Para exemplificar sua utilização, criei o código abaixo, onde utilizo uma variável global para carregar o id do usuário que foi utilizado para logar na aplicação (tomando como base que a aplicação contém um tabela própria de controle de usuários), que será utilizado por uma trigger para identificar o usuário responsável por inclusão de um registro na tabela.

Primeiramente, criarei uma especificação de package que conterá a variável global, vamos chama-lá de UTIL_PCK:

create or replace package util_pck is
 user_id number;            
end util_pck; 

Criarei uma simples tabela que contêm as quantidades em estoque de um produto, vamos chama-lá de TB_INVENTORY:

create table tb_inventory  (id_inventory    number primary key,
                           total           number,
                           id_product      number,
                           create_user_id  number,
                           create_date     date,
                           alter_user_id   number,
                           alter_date      date);

Agora criarei a trigger para popular os campos de controle do registro automaticamente, vamos cham[a-la de TRG_INVENTORY_BIU:

create or replace trigger trg_inventory_biu
 before insert or update on tb_inventory  
 for each row
 
declare

begin
  if(util_pck.user_id is null)then
    raise_application_error(-20001,'Usuário de controle não informado!');
  end if;
  
 :new.create_user_id := util_pck.user_id;
 :new.create_date    := sysdate;
 :new.alter_user_id  := util_pck.user_id;
 :new.alter_date     := sysdate;
 
end trg_inventory_biu;

Veja que a variável global, USER_ID da package UTIL_PCK, pode ser acessada por qualquer outro objeto a qualquer momento, qualquer outra trigger pode utilizar esta informação. Desta forma, podemos reutilizar esta informação. O único pré-requisito, é que no momento da autenticação do usuário a variável seja carregada:

declare

begin
util_pck.user_id := 1;

insert into tb_inventory(id_inventory,
                         total,
                         id_product)
                  values (10,
                          200,
                          14);
                          
commit;                           

end;

Veja o registro após a execução do comando acima:

SQL> select * from tb_inventory;

    ID_INVENTORY           TOTAL          ID_PRODUCT           CREATE_USER_ID      CREATE_DATE    ALTER_USER_ID  ALTER_DATE
--------------------     -----------     -----------------   ---------------    -----------------    --------------- ----------------
         10                200                14                  1        27/5/2012 1              1   27/5/2012

02. Variáveis Públicas

Têm a visibilidade pública, podem ser acessadas por qualquer objeto contido na mesma package em que foi declarada. Sua utilização pode, por exemplo, ser um contador de linhas em um processo de leitura de um arquivo, ou um record pré carregado com os dados de uma tabela.

Para exemplificar, vou criar um simples processo de verificação de quantidades de itens em estoque. 

Primeiramente criarei a package EXAMPLE_PCK:

create or replace package example_pck
is

procedure call_p;

end example_pck;

create or replace package body example_pck
is
type typ_tb_inventory is table of tb_inventory%rowtype index by pls_integer;
recTbInventory typ_tb_inventory;

procedure load_p
is
begin

 select *
   bulk collect into recTbInventory
   from tb_inventory;

end load_p;

function count_check_f(p_min in number)
return number
is
numCounter   number;
begin

for dd in 1..recTbInventory.count loop

  if(recTbInventory(dd).total > p_min)then
  
    numCounter := numCounter + 1;
  
  end if;
  
end loop;

  return numCounter;

end count_check_f;

procedure call_p
is
begin

  load_p;
  
  dbms_output.put_line('Quantidade de itens com mais de 1 em estoque:'||count_check_f(p_min => 1));
  dbms_output.put_line('Quantidade de itens com mais de 100 em estoque:'||count_check_f(p_min => 100));
  dbms_output.put_line('Quantidade de itens com mais de 1000 em estoque:'||count_check_f(p_min => 1000));      

end call_p;

end example_pck;

Veja que no momento da execução da procedure CALL_P, os dados da tabela TB_INVENTORY são carregados para a variável RECTBINVENTORY, que foi criada em nível de escopo público. Depois, a função COUNT_CHECK_F (que é responsável pela contagem de registros) é chamada 3 vezes, passando os parametros 1, 100 e 1000. Neste momento, a função utiliza as informações carregadas anteriormente na variável RECTBINVENTORY, sem a necessidade de recarrega-lás para cada uma das execuções.

03. Variáveis Locais

Têm visibilidade local, podem ser acessadas somente enquanto o programa em que foi declarado estiver em execução. É a mais comum, utilizada para qualquer finalidade, uma descrição de uma condição de pagamento, um totalizador de valores a serem pagos por um cliente ou um total de quantidades em estoque.

declare

numTotal number;

begin
 select sum(i.total)
 into numTotal
 from tb_inventory i;

dbms_output.put_line(numTotal);

end;

Existem diversas outras situações em que podemos utilizar variáveis de escopo público ou escopo global, tornando o código eficiente e reutilizável. Como citado anteriormente as variáveis de escopo local são mais comuns de serem utilizadas.

Leonardo Litz

Leonardo Litz

É formado em Análise de Sistemas pela Uniban. Possui 9 anos de experiência em análise, implementação e desenvolvimento de softwares com Oracle Forms/Reports, PL/SQL. Também possui experiência de 5 anos em WebTool Kit, HTML, JavaScript, XML, CSS e APEX, além de conhecimentos em JAVA e Delphi. Possui certificação Oracle Advanced PL/SQL Developer Certified Professional 11g. Em sua experiência profissional teve a oportunidade de participar de diversos projetos, dos quais pode-se destacar migrações de sistemas de arquivos indexados em Cobol para banco de dados Oracle; tunning em camada de aplicações e camada de banco de dados; administração de banco de dados Oracle 9i e 10g; modelagem relacional de dados utilizando Erwin; migração do Forms 6i para Forms IAS 10g; levantamento, análise e desenvolvimento de software em Delphi com Oracle, Oracle WebTool Kit e APEX 4.0.1. Atualmente trabalha em uma empresa petroquimica, na qual atua como Desenvolvedor Oracle EBS (OA 11.5.10), desenvolvendo customizações para todos os módulos, nos padrões e recursos do ERP, utilizando PL/Sql, Forms 6i, Reports 6i, Discover, WorkFlow e APEX.

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Marcações:
plugins premium WordPress