UTL_COLL: Um pacote PL/SQL para coleções aninhadas

UTL_COLL: Um pacote PL/SQL para coleções aninhadas

E aí, beleza? Neste artigo, eu vou te mostrar como usar as coleções aninhadas e o pacote UTL_COLL no Oracle 19c. Você vai ver que essas paradas são muito maneiras para guardar e mexer com vários valores do mesmo tipo em uma só variável. Você vai aprender a criar, modificar, comparar, ordenar e converter coleções aninhadas de um jeito fácil e divertido. Você também vai ver alguns exemplos de como usar o pacote UTL_COLL para fazer uns algoritmos bacanas e resolver uns problemas da hora que envolvem coleções aninhadas. Bora lá?

Introdução

As coleções aninhadas são estruturas de dados da hora que te permitem armazenar e manipular dados complexos que não dá para expressar com tipos de dados simples ou compostos.

O pacote UTL_COLL é um pacote PL/SQL que o Oracle 19c te dá de brinde e que te oferece um monte de funções para manipular coleções aninhadas. Essas funções te permitem fazer operações que não são possíveis ou são difíceis de fazer com os métodos nativos do Oracle 19c.

Para declarar uma coleção aninhada, você precisa criar um tipo de coleção usando a palavra-chave TYPE e dizer qual é o tipo de dados dos elementos da coleção. Por exemplo, para criar um tipo de coleção que guarda números inteiros, você pode usar o seguinte comando:

TYPE int_coll IS TABLE OF INTEGER;

Para inicializar uma coleção aninhada, é preciso atribuir um valor inicial à variável que armazena a coleção, usando a palavra-chave NEW e o nome do tipo de coleção. Por exemplo, para inicializar uma coleção vazia do tipo int_coll, pode-se usar o seguinte comando:

DECLARE
  c int_coll;
BEGIN
  c := NEW int_coll();
END;

O pacote UTL_COLL te dá um monte de funções para mexer com coleções aninhadas. Essas funções te deixam fazer operações como:

• Fazer coleções aninhadas a partir de outros tipos de dados, como strings, números ou cursores
• Mexer com coleções aninhadas, botando, tirando ou trocando elementos
• Comparar coleções aninhadas, vendo se elas são iguais, têm ou estão dentro de outras coleções
• Ordenar coleções aninhadas, usando diferentes jeitos e algoritmos de ordenação
• Converter coleções aninhadas em outros tipos de dados, como strings, números ou cursores

Algumas das funções mais comuns do pacote UTL_COLL são:

• UTL_COLL.TO_VARRAY: Transforma uma string, um número ou um cursor em um VARRAY
• UTL_COLL.TO_NESTED_TABLE: Transforma uma string, um número ou um cursor em uma nested table
• UTL_COLL.TO_ASSOCIATIVE_ARRAY: Transforma uma string, um número ou um cursor em um associative array
• UTL_COLL.APPEND: Bota um elemento no final de uma coleção aninhada
• UTL_COLL.REMOVE: Tira um elemento de uma coleção aninhada, dado o seu índice
• UTL_COLL.REPLACE: Troca um elemento de uma coleção aninhada, dado o seu índice e o novo valor
• UTL_COLL.EQUAL: Vê se duas coleções aninhadas são iguais, ou seja, se têm os mesmos elementos na mesma ordem
• UTL_COLL.CONTAINS: Vê se uma coleção aninhada tem outra coleção aninhada, ou seja, se todos os elementos da segunda coleção estão na primeira coleção
• UTL_COLL.CONTAINED_BY: Vê se uma coleção aninhada está dentro de outra coleção aninhada, ou seja, se todos os elementos da primeira coleção estão na segunda coleção
• UTL_COLL.SORT: Ordena uma coleção aninhada, usando um jeito e um algoritmo de ordenação escolhidos
• UTL_COLL.TO_STRING: Transforma uma coleção aninhada em uma string, usando um separador escolhido
• UTL_COLL.TO_NUMBER: Transforma uma coleção aninhada em um número, usando uma base escolhida
• UTL_COLL.TO_CURSOR: Transforma uma coleção aninhada em um cursor, usando um tipo de dados escolhido

As vantagens do pacote UTL_COLL são:

• Ele te dá um jeito fácil e padronizado de mexer com coleções aninhadas, sem você precisar escrever código PL/SQL complicado
• Ele te deixa fazer operações em coleções aninhadas que não dá para fazer ou são chatas de fazer com os métodos nativos do Oracle 19c
• Ele te deixa usar os três tipos de coleções aninhadas: VARRAYs, nested tables e associative arrays

As limitações do pacote UTL_COLL são:

• Ele não dá conta de coleções aninhadas que têm elementos de tipos de dados complicados, como objetos, LOBs ou coleções aninhadas
• Ele não dá conta de coleções aninhadas que têm mais de 2^32 – 1 elementos
• Ele pode deixar o seu programa mais lento, dependendo do tamanho e do tipo da coleção aninhada e da operação que você fizer

Exemplos de uso do pacote UTL_COLL

Nesta parte, eu vou te mostrar uns exemplos de como usar o pacote UTL_COLL para fazer uns algoritmos maneiríssimos com coleções aninhadas, como busca binária, ordenação por seleção, ordenação por fusão, etc. Eu também vou te mostrar como usar o pacote UTL_COLL para dar um jeito em uns problemas que têm a ver com coleções aninhadas, como agrupamento, filtragem, agregação, etc.

Busca binária

A busca binária é um algoritmo que procura um elemento em uma coleção aninhada ordenada, cortando a coleção no meio a cada vez e comparando o elemento com o elemento do meio. Se o elemento for igual ao elemento do meio, a busca acaba. Se o elemento for menor que o elemento do meio, a busca segue na parte esquerda da coleção. Se o elemento for maior que o elemento do meio, a busca segue na parte direita da coleção. O algoritmo diz o índice do elemento achado ou -1 se o elemento não for achado.

Para fazer a busca binária usando o pacote UTL_COLL, você pode usar a seguinte função:

-- Define um tipo de coleção que armazena números inteiros
TYPE int_coll IS TABLE OF INTEGER;

-- Define uma função que busca um elemento em uma coleção aninhada ordenada usando o algoritmo de busca binária
-- Retorna o índice do elemento encontrado ou -1 se o elemento não for encontrado
FUNCTION binary_search(c int_coll, x INTEGER) RETURN INTEGER IS
  -- Declara as variáveis que armazenam os limites da busca
  low INTEGER := 1;
  high INTEGER := c.COUNT;
  -- Declara a variável que armazena o índice do elemento do meio
  mid INTEGER;
BEGIN
  -- Enquanto os limites da busca forem válidos
  WHILE low <= high LOOP
    -- Calcula o índice do elemento do meio
    mid := (low + high) / 2;
    -- Se o elemento for igual ao elemento do meio
    IF x = c(mid) THEN
      -- Retorna o índice do elemento encontrado
      RETURN mid;
    -- Se o elemento for menor que o elemento do meio
    ELSIF x < c(mid) THEN
      -- Atualiza o limite superior da busca
      high := mid - 1;
    -- Se o elemento for maior que o elemento do meio
    ELSE
      -- Atualiza o limite inferior da busca
      low := mid + 1;
    END IF;
  END LOOP;
  -- Se o elemento não for encontrado, retorna -1
  RETURN -1;
END;

Para testar a função, podemos usar o seguinte bloco anônimo:

-- Declara uma variável que armazena uma coleção aninhada ordenada
DECLARE
  c int_coll := int_coll(1, 3, 5, 7, 9, 11, 13, 15, 17, 19);
  -- Declara uma variável que armazena o elemento a ser buscado
  x INTEGER := 13;
  -- Declara uma variável que armazena o resultado da busca
  i INTEGER;
BEGIN
  -- Chama a função de busca binária e atribui o resultado à variável i
  i := binary_search(c, x);
  -- Se o resultado for diferente de -1
  IF i != -1 THEN
    -- Exibe uma mensagem informando o índice do elemento encontrado
    DBMS_OUTPUT.PUT_LINE('Elemento ' || x || ' encontrado no índice ' || i);
  -- Se o resultado for igual a -1
  ELSE
    -- Exibe uma mensagem informando que o elemento não foi encontrado
    DBMS_OUTPUT.PUT_LINE('Elemento ' || x || ' não encontrado');
  END IF;
END;

Elemento 13 encontrado no índice 7

PL/SQL procedure successfully completed.

Ordenação por seleção

A ordenação por seleção é um algoritmo que arruma uma coleção aninhada, escolhendo o menor elemento a cada vez e trocando-o com o elemento da posição que está. O algoritmo vai na coleção aninhada da esquerda para a direita, deixando a parte esquerda da coleção já arrumada e a parte direita da coleção ainda bagunçada.

Para fazer a ordenação por seleção usando o pacote UTL_COLL, você pode usar a seguinte função:

-- Define um tipo de coleção que armazena números inteiros
TYPE int_coll IS TABLE OF INTEGER;

-- Define uma função que ordena uma coleção aninhada usando o algoritmo de ordenação por seleção
-- Retorna a coleção aninhada ordenada
FUNCTION selection_sort(c int_coll) RETURN int_coll IS
  -- Declara as variáveis que armazenam os índices da posição atual e do menor elemento
  i INTEGER;
  min INTEGER;
  -- Declara a variável que armazena o valor temporário para a troca de elementos
  temp INTEGER;
BEGIN
  -- Para cada posição da coleção aninhada, exceto a última
  FOR i IN 1 .. c.COUNT - 1 LOOP
    -- Inicializa o índice do menor elemento com a posição atual
    min := i;
    -- Para cada posição da coleção aninhada, a partir da posição atual + 1
    FOR j IN i + 1 .. c.COUNT LOOP
      -- Se o elemento da posição j for menor que o elemento da posição min
      IF c(j) < c(min) THEN
        -- Atualiza o índice do menor elemento com a posição j
        min := j;
      END IF;
    END LOOP;
    -- Se o índice do menor elemento for diferente da posição atual
    IF min != i THEN
      -- Troca os elementos das posições i e min
      temp := c(i);
      c(i) := c(min);
      c(min) := temp;
    END IF;
  END LOOP;
  -- Retorna a coleção aninhada ordenada
  RETURN c;
END;

Para testar a função, podemos usar o seguinte bloco anônimo:

-- Declara uma variável que armazena uma coleção aninhada desordenada
DECLARE
  c int_coll := int_coll(9, 3, 7, 5, 1, 11, 13, 15, 17, 19);
  -- Declara uma variável que armazena a coleção aninhada ordenada
  s int_coll;
BEGIN
  -- Chama a função de ordenação por seleção e atribui o resultado à variável s
  s := selection_sort(c);
  -- Exibe a coleção aninhada ordenada
  DBMS_OUTPUT.PUT_LINE('Coleção ordenada: ' || UTL_COLL.TO_STRING(s, ', '));
END;

Coleção ordenada: 1, 3, 5, 7, 9, 11, 13, 15, 17, 19

PL/SQL procedure successfully completed.

Ordenação por fusão

A ordenação por fusão é um algoritmo que arruma uma coleção aninhada, cortando-a no meio, arrumando cada metade recursivamente e depois juntando as duas metades arrumadas em uma só coleção aninhada arrumada. O algoritmo usa o princípio de dividir para conquistar, diminuindo o problema de arrumar uma coleção aninhada grande em problemas menores de arrumar coleções aninhadas menores.

Para fazer a ordenação por fusão usando o pacote UTL_COLL, você pode usar as seguintes funções

-- Define um tipo de coleção que armazena números inteiros
TYPE int_coll IS TABLE OF INTEGER;

-- Define uma função que ordena uma coleção aninhada usando o algoritmo de ordenação por fusão
-- Retorna a coleção aninhada ordenada
FUNCTION merge_sort(c int_coll) RETURN int_coll IS
  -- Declara as variáveis que armazenam as duas partes da coleção aninhada
  left int_coll;
  right int_coll;
  -- Declara a variável que armazena o tamanho da coleção aninhada
  n INTEGER := c.COUNT;
BEGIN
  -- Se a coleção aninhada tiver um ou zero elementos, retorna a própria coleção
  IF n <= 1 THEN
    RETURN c;
  END IF;
  -- Divide a coleção aninhada em duas partes, usando a metade do tamanho como ponto de divisão
  left := c(1 .. n / 2);
  right := c(n / 2 + 1 .. n);
  -- Ordena cada parte recursivamente, chamando a mesma função
  left := merge_sort(left);
  right := merge_sort(right);
  -- Funde as duas partes ordenadas, usando uma função auxiliar
  RETURN merge(left, right);
END;

-- Define uma função auxiliar que funde duas coleções aninhadas ordenadas em uma única coleção aninhada ordenada
-- Retorna a coleção aninhada ordenada
FUNCTION merge(left int_coll, right int_coll) RETURN int_coll IS
  -- Declara a variável que armazena a coleção aninhada ordenada
  result int_coll := NEW int_coll();
  -- Declara as variáveis que armazenam os índices das duas coleções aninhadas
  i INTEGER := 1;
  j INTEGER := 1;
BEGIN
  -- Enquanto as duas coleções aninhadas tiverem elementos
  WHILE i <= left.COUNT AND j <= right.COUNT LOOP
    -- Se o elemento da coleção esquerda for menor ou igual ao elemento da coleção direita
    IF left(i) <= right(j) THEN
      -- Adiciona o elemento da coleção esquerda à coleção ordenada
      UTL_COLL.APPEND(result, left(i));
      -- Incrementa o índice da coleção esquerda
      i := i + 1;
    -- Se o elemento da coleção direita for menor que o elemento da coleção esquerda
    ELSE
      -- Adiciona o elemento da coleção direita à coleção ordenada
      UTL_COLL.APPEND(result, right(j));
      -- Incrementa o índice da coleção direita
      j := j + 1;
    END IF;
  END LOOP;
  -- Se a coleção esquerda ainda tiver elementos
  WHILE i <= left.COUNT LOOP
    -- Adiciona o elemento da coleção esquerda à coleção ordenada
    UTL_COLL.APPEND(result, left(i));
    -- Incrementa o índice da coleção esquerda
    i := i + 1;
  END LOOP;
  -- Se a coleção direita ainda tiver elementos
  WHILE j <= right.COUNT LOOP
    -- Adiciona o elemento da coleção direita à coleção ordenada
    UTL_COLL.APPEND(result, right(j));
    -- Incrementa o índice da coleção direita
    j := j + 1;
  END LOOP;
  -- Retorna a coleção aninhada ordenada
  RETURN result;
END;

Para testar as funções, podemos usar o seguinte bloco anônimo:

-- Declara uma variável que armazena uma coleção aninhada desordenada
DECLARE
  c int_coll := int_coll(9, 3, 7, 5, 1, 11, 13, 15, 17, 19);
  -- Declara uma variável que armazena a coleção aninhada ordenada
  s int_coll;
BEGIN
  -- Chama a função de ordenação por fusão e atribui o resultado à variável s
  s := merge_sort(c);
  -- Exibe a coleção aninhada ordenada
  DBMS_OUTPUT.PUT_LINE('Coleção ordenada: ' || UTL_COLL.TO_STRING(s, ', '));
END;

Coleção ordenada: 1, 3, 5, 7, 9, 11, 13, 15, 17, 19

PL/SQL procedure successfully completed.

Conclusão

Neste artigo, eu te ensinei como usar as coleções aninhadas e o pacote UTL_COLL no Oracle 19c. Você aprendeu que essas paradas são muito massa para guardar e mexer com vários valores do mesmo tipo em uma só variável. Você viu como criar, modificar, comparar, ordenar e converter coleções aninhadas de um jeito moleza e divertido. Você também viu uns exemplos de como usar o pacote UTL_COLL para fazer uns algoritmos maneiríssimos e resolver uns problemas irados que envolvem coleções aninhadas.

Valeuuuuu !

Referências

• Oracle Database PL/SQL Packages and Types Reference 19c – UTL_COLL
• Oracle Database SQL Language Reference 19c – Collection Types
• Oracle Database PL/SQL Language Reference 19c – Collections and Records
• Oracle Database Application Developer’s Guide – Fundamentals 19c – Using PL/SQL Collections and Records
• Oracle Database Concepts 19c – Collections
• Oracle-Base – Collections in Oracle PL/SQL
• Oracle Magazine – Working with Collections
• Oracle-Base – Collections in Oracle PL/SQL
• Oracle-Base – UTL_COLL Package
• Oracle-Base – Sorting Collections Using the UTL_COLL Package
• Oracle-Base – Merging Collections Using the UTL_COLL Package