VARRAYs no Oracle PL/SQL

Introdução

Os VARRAYs (Variable-Size Arrays) são componentes fundamentais no ecossistema Oracle PL/SQL, oferecendo uma solução robusta e eficiente para o gerenciamento de coleções de dados. Este artigo técnico explora em profundidade os aspectos cruciais dos VARRAYs, desde sua implementação básica até estratégias avançadas de otimização de performance.

O que é um VARRAY?

VARRAYs, abreviação de Variable-size Arrays, representam uma estrutura de dados de coleção no Oracle PL/SQL. Estas estruturas permitem o armazenamento de conjuntos ordenados de elementos homogêneos, com um limite máximo predefinido. A importância dos VARRAYs reside em sua capacidade de:

Oferecer flexibilidade no gerenciamento de dados
Melhorar a eficiência de consultas complexas
Facilitar a manipulação de conjuntos de dados relacionados

Características técnicas dos VARRAYs:

Tipo de Coleção: Ordenada e indexada
Limite de Tamanho: Definido na declaração
Tipo de Dados: Homogêneo (todos os elementos do mesmo tipo)
Índices: Inteiros positivos, começando em 1
Armazenamento: In-line (embutido) ou out-of-line (separado)

Sintaxe Detalhada para Criar um VARRAY

TYPE nome_tipo IS {VARRAY | VARYING ARRAY} (tamanho_maximo) OF tipo_elemento [NOT NULL];
nome_varray nome_tipo;

1 2	TYPE nome_tipo IS {VARRAY \| VARYING ARRAY} (tamanho_maximo) OF tipo_elemento [NOT NULL]; nome_varray nome_tipo;

Onde:

nome_tipo: O nome que você dá ao tipo de VARRAY.
tamanho_maximo: Um número inteiro positivo que define o limite superior de elementos.
tipo_elemento: O tipo de dado que cada elemento do VARRAY irá armazenar.
[NOT NULL]: Opcional, especifica que os elementos não podem ser nulos.
nome_varray: O nome da variável que será do tipo VARRAY criado.

Exemplo Detalhado com o Schema HR

Vamos criar um VARRAY para armazenar os top 5 salários de um departamento, explicando cada passo:

DECLARE
    -- Definição do tipo VARRAY
    TYPE top_salarios_type IS VARRAY(5) OF employees.salary%TYPE;
    
    -- Declaração e inicialização da variável VARRAY
    v_top_salarios top_salarios_type := top_salarios_type();
    
    -- Cursor para buscar os top 5 salários
    CURSOR c_top_salarios IS
        SELECT salary
        FROM employees
        WHERE department_id = 80  -- Departamento de Vendas
        ORDER BY salary DESC
        FETCH FIRST 5 ROWS ONLY;
    
    -- Variável para armazenar temporariamente cada salário
    v_salario employees.salary%TYPE;
BEGIN
    -- Inicializar o VARRAY com 5 elementos
    v_top_salarios.EXTEND(5);
    
    -- Abrir o cursor
    OPEN c_top_salarios;
    
    -- Loop para preencher o VARRAY
    FOR i IN 1..5 LOOP
        FETCH c_top_salarios INTO v_salario;
        EXIT WHEN c_top_salarios%NOTFOUND;
        v_top_salarios(i) := v_salario;
    END LOOP;
    
    -- Fechar o cursor
    CLOSE c_top_salarios;
    
    -- Exibir os top 5 salários
    DBMS_OUTPUT.PUT_LINE('Top 5 salários do departamento de Vendas:');
    FOR i IN 1..v_top_salarios.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Top ' || i || ': $' || TO_CHAR(v_top_salarios(i), '999,999.99'));
    END LOOP;
    
    -- Demonstrar o uso do método LIMIT
    DBMS_OUTPUT.PUT_LINE('Tamanho máximo do VARRAY: ' || v_top_salarios.LIMIT);
    
    -- Tentar adicionar um sexto elemento (isso causará um erro)
    BEGIN
        v_top_salarios.EXTEND;
        v_top_salarios(6) := 100000;
    EXCEPTION
        WHEN SUBSCRIPT_BEYOND_COUNT THEN
            DBMS_OUTPUT.PUT_LINE('Erro: Não é possível adicionar mais elementos. Limite máximo atingido.');
    END;
END;
/

DECLARE

-- Definição do tipo VARRAY

TYPE top_salarios_type IS VARRAY(5) OF employees.salary%TYPE;

-- Declaração e inicialização da variável VARRAY

v_top_salarios top_salarios_type := top_salarios_type();

-- Cursor para buscar os top 5 salários

CURSOR c_top_salarios IS

SELECT salary

FROM employees

WHERE department_id = 80 -- Departamento de Vendas

ORDER BY salary DESC

FETCH FIRST 5 ROWS ONLY;

-- Variável para armazenar temporariamente cada salário

v_salario employees.salary%TYPE;

BEGIN

-- Inicializar o VARRAY com 5 elementos

v_top_salarios.EXTEND(5);

-- Abrir o cursor

OPEN c_top_salarios;

-- Loop para preencher o VARRAY

FOR i IN 1..5 LOOP

FETCH c_top_salarios INTO v_salario;

EXIT WHEN c_top_salarios%NOTFOUND;

v_top_salarios(i) := v_salario;

END LOOP;

-- Fechar o cursor

CLOSE c_top_salarios;

-- Exibir os top 5 salários

DBMS_OUTPUT.PUT_LINE('Top 5 salários do departamento de Vendas:');

FOR i IN 1..v_top_salarios.COUNT LOOP

DBMS_OUTPUT.PUT_LINE('Top ' || i || ': $' || TO_CHAR(v_top_salarios(i), '999,999.99'));

END LOOP;

-- Demonstrar o uso do método LIMIT

DBMS_OUTPUT.PUT_LINE('Tamanho máximo do VARRAY: ' || v_top_salarios.LIMIT);

-- Tentar adicionar um sexto elemento (isso causará um erro)

BEGIN

v_top_salarios.EXTEND;

v_top_salarios(6) := 100000;

EXCEPTION

WHEN SUBSCRIPT_BEYOND_COUNT THEN

DBMS_OUTPUT.PUT_LINE('Erro: Não é possível adicionar mais elementos. Limite máximo atingido.');

END;

Explicação detalhada:

Definimos o tipo top_salarios_type como um VARRAY que pode conter até 5 salários.
Inicializamos v_top_salarios como um VARRAY vazio.
Criamos um cursor para buscar os 5 maiores salários do departamento de Vendas.
Usamos EXTEND(5) para alocar espaço para 5 elementos no VARRAY.
Preenchemos o VARRAY com os dados do cursor.
Exibimos os salários armazenados no VARRAY.
Demonstramos o uso do método LIMIT para mostrar o tamanho máximo do VARRAY.
Tentamos adicionar um sexto elemento para mostrar como o Oracle lida com a tentativa de exceder o limite.

Métodos de Coleção para VARRAYs

VARRAYs suportam vários métodos de coleção. Vamos explorar cada um deles em detalhes:

DECLARE
    TYPE equipe_type IS VARRAY(10) OF VARCHAR2(30);
    v_equipe equipe_type := equipe_type('João', 'Maria', 'Pedro', 'Ana', 'Carlos');
BEGIN
    -- COUNT: Retorna o número atual de elementos
    DBMS_OUTPUT.PUT_LINE('Número de membros na equipe: ' || v_equipe.COUNT);
    
    -- LIMIT: Retorna o tamanho máximo do VARRAY
    DBMS_OUTPUT.PUT_LINE('Tamanho máximo da equipe: ' || v_equipe.LIMIT);
    
    -- FIRST: Retorna o índice do primeiro elemento (sempre 1 para VARRAYs)
    DBMS_OUTPUT.PUT_LINE('Índice do primeiro membro: ' || v_equipe.FIRST);
    
    -- LAST: Retorna o índice do último elemento
    DBMS_OUTPUT.PUT_LINE('Índice do último membro: ' || v_equipe.LAST);
    
    -- EXISTS: Verifica se um elemento existe em um determinado índice
    IF v_equipe.EXISTS(3) THEN
        DBMS_OUTPUT.PUT_LINE('O terceiro membro é: ' || v_equipe(3));
    END IF;
    
    -- EXTEND: Adiciona um novo elemento
    v_equipe.EXTEND;
    v_equipe(v_equipe.LAST) := 'Lúcia';
    DBMS_OUTPUT.PUT_LINE('Novo membro adicionado: ' || v_equipe(v_equipe.LAST));
    
    -- TRIM: Remove o último elemento
    v_equipe.TRIM;
    DBMS_OUTPUT.PUT_LINE('Após remover o último, o último agora é: ' || v_equipe(v_equipe.LAST));
    
    -- PRIOR e NEXT: Retornam os índices anterior e posterior a um dado índice
    IF v_equipe.PRIOR(3) IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('Membro antes do terceiro: ' || v_equipe(v_equipe.PRIOR(3)));
    END IF;
    IF v_equipe.NEXT(3) IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('Membro depois do terceiro: ' || v_equipe(v_equipe.NEXT(3)));
    END IF;
END;
/

DECLARE

TYPE equipe_type IS VARRAY(10) OF VARCHAR2(30);

v_equipe equipe_type := equipe_type('João', 'Maria', 'Pedro', 'Ana', 'Carlos');

BEGIN

-- COUNT: Retorna o número atual de elementos

DBMS_OUTPUT.PUT_LINE('Número de membros na equipe: ' || v_equipe.COUNT);

-- LIMIT: Retorna o tamanho máximo do VARRAY

DBMS_OUTPUT.PUT_LINE('Tamanho máximo da equipe: ' || v_equipe.LIMIT);

-- FIRST: Retorna o índice do primeiro elemento (sempre 1 para VARRAYs)

DBMS_OUTPUT.PUT_LINE('Índice do primeiro membro: ' || v_equipe.FIRST);

-- LAST: Retorna o índice do último elemento

DBMS_OUTPUT.PUT_LINE('Índice do último membro: ' || v_equipe.LAST);

-- EXISTS: Verifica se um elemento existe em um determinado índice

IF v_equipe.EXISTS(3) THEN

DBMS_OUTPUT.PUT_LINE('O terceiro membro é: ' || v_equipe(3));

END IF;

-- EXTEND: Adiciona um novo elemento

v_equipe.EXTEND;

v_equipe(v_equipe.LAST) := 'Lúcia';

DBMS_OUTPUT.PUT_LINE('Novo membro adicionado: ' || v_equipe(v_equipe.LAST));

-- TRIM: Remove o último elemento

v_equipe.TRIM;

DBMS_OUTPUT.PUT_LINE('Após remover o último, o último agora é: ' || v_equipe(v_equipe.LAST));

-- PRIOR e NEXT: Retornam os índices anterior e posterior a um dado índice

IF v_equipe.PRIOR(3) IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE('Membro antes do terceiro: ' || v_equipe(v_equipe.PRIOR(3)));

END IF;

IF v_equipe.NEXT(3) IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE('Membro depois do terceiro: ' || v_equipe(v_equipe.NEXT(3)));

END IF;

END;

Explicação detalhada dos métodos:

COUNT: Retorna o número atual de elementos no VARRAY.
LIMIT: Retorna o tamanho máximo definido para o VARRAY.
FIRST: Para VARRAYs, sempre retorna 1, pois o primeiro índice é sempre 1.
LAST: Retorna o índice do último elemento preenchido.
EXISTS: Verifica se existe um elemento em um determinado índice.
EXTEND: Adiciona um novo elemento ao final do VARRAY.
TRIM: Remove o último elemento do VARRAY.
PRIOR: Retorna o índice anterior ao índice especificado.
NEXT: Retorna o próximo índice após o índice especificado.

Diferenças Importantes e Limitações

Inicialização: VARRAYs precisam ser inicializados antes do uso, seja com um construtor vazio ou com valores.
Tamanho Máximo: Diferente de Arrays Associativos e Nested Tables, VARRAYs têm um limite máximo que não pode ser alterado após a definição.
Densidade: VARRAYs são sempre densos, não permitindo “buracos” entre os elementos.
Método DELETE: Não pode ser usado com VARRAYs. Use TRIM para remover elementos do final.
Persistência: VARRAYs podem ser armazenados em colunas de tabelas do banco de dados, o que não é possível com Arrays Associativos.
Performance: Para coleções muito grandes ou com frequentes inserções/remoções no meio, Nested Tables ou Arrays Associativos podem ser mais eficientes.

Exemplo Avançado: Uso de VARRAY em uma Tabela do HR Schema

Vamos criar uma tabela que usa um VARRAY para armazenar as habilidades de um funcionário:

-- Criar o tipo VARRAY
CREATE OR REPLACE TYPE employee_skills_type AS VARRAY(5) OF VARCHAR2(50);
/

-- Criar a tabela que usa o VARRAY
CREATE TABLE employee_skills (
    employee_id NUMBER PRIMARY KEY,
    skills employee_skills_type
);

-- Inserir dados de exemplo
DECLARE
    v_skills employee_skills_type;
BEGIN
    -- Funcionário 1
    v_skills := employee_skills_type('Java', 'SQL', 'PL/SQL');
    INSERT INTO employee_skills VALUES (100, v_skills);
    
    -- Funcionário 2
    v_skills := employee_skills_type('Python', 'Machine Learning', 'Data Analysis', 'R');
    INSERT INTO employee_skills VALUES (101, v_skills);
    
    -- Funcionário 3
    v_skills := employee_skills_type('Project Management', 'Agile', 'Scrum');
    INSERT INTO employee_skills VALUES (102, v_skills);
END;
/

-- Consultar e exibir os dados
DECLARE
    CURSOR c_skills IS
        SELECT e.first_name, e.last_name, es.skills
        FROM employees e
        JOIN employee_skills es ON e.employee_id = es.employee_id;
    
    v_skills employee_skills_type;
BEGIN
    FOR rec IN c_skills LOOP
        DBMS_OUTPUT.PUT_LINE('Funcionário: ' || rec.first_name || ' ' || rec.last_name);
        DBMS_OUTPUT.PUT_LINE('Habilidades:');
        
        v_skills := rec.skills;
        FOR i IN 1..v_skills.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('  - ' || v_skills(i));
        END LOOP;
        
        DBMS_OUTPUT.PUT_LINE('---');
    END LOOP;
END;
/

-- Criar o tipo VARRAY

CREATE OR REPLACE TYPE employee_skills_type AS VARRAY(5) OF VARCHAR2(50);

-- Criar a tabela que usa o VARRAY

CREATE TABLE employee_skills (

employee_id NUMBER PRIMARY KEY,

skills employee_skills_type

);

-- Inserir dados de exemplo

DECLARE

v_skills employee_skills_type;

BEGIN

-- Funcionário 1

v_skills := employee_skills_type('Java', 'SQL', 'PL/SQL');

INSERT INTO employee_skills VALUES (100, v_skills);

-- Funcionário 2

v_skills := employee_skills_type('Python', 'Machine Learning', 'Data Analysis', 'R');

INSERT INTO employee_skills VALUES (101, v_skills);

-- Funcionário 3

v_skills := employee_skills_type('Project Management', 'Agile', 'Scrum');

INSERT INTO employee_skills VALUES (102, v_skills);

END;

-- Consultar e exibir os dados

DECLARE

CURSOR c_skills IS

SELECT e.first_name, e.last_name, es.skills

FROM employees e

JOIN employee_skills es ON e.employee_id = es.employee_id;

v_skills employee_skills_type;

BEGIN

FOR rec IN c_skills LOOP

DBMS_OUTPUT.PUT_LINE('Funcionário: ' || rec.first_name || ' ' || rec.last_name);

DBMS_OUTPUT.PUT_LINE('Habilidades:');

v_skills := rec.skills;

FOR i IN 1..v_skills.COUNT LOOP

DBMS_OUTPUT.PUT_LINE(' - ' || v_skills(i));

END LOOP;

DBMS_OUTPUT.PUT_LINE('---');

END LOOP;

END;

Este exemplo demonstra como VARRAYs podem ser usados em tabelas do banco de dados, oferecendo uma maneira estruturada de armazenar listas de valores relacionados a cada registro.

Melhores Práticas e Considerações de Desempenho

Escolha o Tamanho Adequado: Defina o tamanho máximo do VARRAY cuidadosamente. Muito pequeno pode limitar o uso, muito grande pode desperdiçar recursos.
Inicialização: Sempre inicialize o VARRAY antes de usá-lo para evitar erros de “coleção não inicializada”.
Uso de Memória: VARRAYs são armazenados inline (dentro da linha da tabela) até um certo tamanho. Grandes VARRAYs podem impactar o desempenho de I/O.
Atualizações: Atualizações em VARRAYs armazenados em tabelas podem ser menos eficientes do que em Nested Tables, especialmente para coleções grandes.
Índices: Não é possível criar índices em elementos individuais de um VARRAY armazenado em uma coluna de tabela.

Conclusão

VARRAYs são uma ferramenta poderosa no arsenal do desenvolvedor Oracle PL/SQL, oferecendo um equilíbrio entre flexibilidade e controle estruturado. Eles são particularmente úteis quando:

Você precisa de uma coleção com um número máximo conhecido de elementos.
A ordem dos elementos é importante.
Você deseja armazenar a coleção diretamente em uma coluna de tabela.

Ao escolher entre diferentes tipos de coleções (Arrays Associativos, Nested Tables e VARRAYs), considere cuidadosamente os requisitos específicos do seu problema, incluindo tamanho da coleção, frequência de atualizações, necessidade de persistência e padrões de acesso aos dados.

Pratique usando VARRAYs em diferentes cenários para entender completamente suas capacidades e limitações. Com o tempo, você desenvolverá um instinto para quando e como usar VARRAYs de maneira mais eficaz em seus projetos Oracle PL/SQL.

Lembre-se, a escolha certa da estrutura de dados pode fazer uma grande diferença na eficiência e manutenibilidade do seu código. VARRAYs são uma excelente opção em muitos casos, mas sempre considere o contexto completo do seu projeto ao tomar decisões de design.

Introdução

O que é um VARRAY?

Características técnicas dos VARRAYs:

Sintaxe Detalhada para Criar um VARRAY

Exemplo Detalhado com o Schema HR

Métodos de Coleção para VARRAYs

Diferenças Importantes e Limitações

Exemplo Avançado: Uso de VARRAY em uma Tabela do HR Schema

Melhores Práticas e Considerações de Desempenho

Conclusão

Related Posts