[Resolvido] 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 chama-lá 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.

http://imasters.com.br/artigo/24637/oracle/escopo-de-declaracao-de-variaveis-no-oracle