Oracle Records (Parte 03)

4 min readSep 19, 2023

Recors, tipos de collections e métodos de controle.

PL/SQL Records

Com Records podemos criar uma struct que recebe vários membros que poderemos chama-los referenciando-os, dizemos que os membros são do mesmo tipo (%type) das colunas originais da tabela, exemplo:

p_nome    employees.first_name%type,

agora a “struct” ou “record” tem os membros, e podemos chamar instanciando “employee.p_nome”.

SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT pemployee_id PROMPT 'Digite o Id do empregado: '
DECLARE
TYPE  employee_record_type IS RECORD 
          (employee_id   employees.employee_id%type,
           p_nome    employees.first_name%type,
           u_nome     employees.last_name%type,
           email         employees.email%type,
           cel  employees.phone_number%type);
employee_record  employee_record_type; 

BEGIN
SELECT  employee_id, first_name, last_name, email, phone_number
INTO    employee_record
FROM    employees
WHERE   employee_id = &pemployee_id;
DBMS_OUTPUT.PUT_LINE(employee_record.employee_id || ' - ' || 
                     employee_record.p_nome || ' - ' || 
                     employee_record.u_nome || ' - ' || 
                     employee_record.cel);
END;

Resultado, basta digitar o ID do empregado que toda informação será trazido usando o Records:

COLLECTIONS

Collections são estruturas utilizadas para gerenciamento de múltiplas linhas em memoria.

os tipos de de collection podem ser:

ASSOCIATIVE ARRAY(Primeiro mais usado)

É um array usado para armazenar dados, e podem ser somente de um tipo declarado, números, char… vejamos:

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  TYPE Numero_Table_Type IS TABLE OF NUMBER(2) --2 numeros
  INDEX BY BINARY_INTEGER; --tipo de index inteiro
  Numero_table  Numero_Table_Type;
BEGIN

  FOR i IN 1..10 -- i declarado automaticamente e vai valer de 1 a 10
  LOOP
    Numero_Table(i) := i; -- adiciona dados no array
  END LOOP;
  --agora vamos ler os dados dorray
  FOR i IN 1..10
  LOOP
    DBMS_OUTPUT.PUT_LINE('Associative Array: Indice = ' 
    || TO_CHAR(i) || ', Valor = ' || TO_CHAR(Numero_Table(I)));
  END LOOP;
END;

ASSOCIATIVE ARRAY RECORS (BULK COLLECT)

Associa valores de uma tabela ao array, assim podemos ter a leitura de varias linhas, pra isso nós temos que declarar um array como do tipo %rowtype. Vejamos um exemplo:


SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  TYPE tabela_table_type IS TABLE OF employees%rowtype --definimos tabela_table
  INDEX BY BINARY_INTEGER;  -- index do tipo inteiro
  tabela_table  tabela_table_type;--instaciamos
BEGIN
  SELECT *
    BULK COLLECT INTO tabela_table --associa toda a tabela à tabela_table
  FROM employees;
  FOR i IN tabela_table.first..tabela_table.last  --do primeiro ao ultimo
  LOOP
    DBMS_OUTPUT.PUT_LINE(tabela_table(i).employee_id || ' - ' || 
                         tabela_table(i).first_name || ' - ' || 
                         tabela_table(i).last_name || ' - ' ||
                         tabela_table(i).phone_number || ' - ' ||
                         tabela_table(i).job_id || ' - ' ||
                         TO_CHAR(tabela_table(i).salary,'99G999G999D99'));   
  END LOOP;
END;

O BULK COLLECT INTO associa toda a tabela à assocative array tabela_table, muito cuidado com isso pois se a tabela associada for muito grande pode consumir muita memoria e podemos ter problemas com o servidor e problema de performance.

Get Daniel Amorim’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

As clausulas .first e .last pega do primeiro ao ultimo elemento de uma collection (tabela principal).

NESTED TABLE (segundo mais usado)

Bem parecido com ASSOCIATIVE ARRAY, sem a declaração do INDEX, mas com INICIALIZAÇÂO.

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  TYPE Numero_Table_Type IS TABLE OF INTEGER(2);
  Numero_table numero_table_type := numero_table_type();
BEGIN

  FOR i IN 1..10 --armazena valor de 1 a 10 no array
  LOOP
    Numero_Table.extend; --aloca espaço em memoria (inicializa)
    Numero_Table(i) := i;
  END LOOP;

  FOR I IN 1..10
  LOOP
    DBMS_OUTPUT.PUT_LINE('Nested Table: Indice = ' || TO_CHAR(i) || ', 
    Valor = ' || TO_CHAR(Numero_Table(i)));
  END LOOP;
END;

NESTED TABLE RECORDS (BULK COLLECT)

Associa valores de uma tabela ao array, assim podemos ter a leitura de varias linhas, pra isso nós temos que declarar um array como do tipo %rowtype. Vejamos um exemplo:

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  TYPE employees_table_type IS TABLE OF employees%rowtype;
  tabela_table  employees_table_type := employees_table_type();
BEGIN
  SELECT *
    BULK COLLECT INTO tabela_table --associa toda a tabela à tabela_table
  FROM employees;
  FOR i IN tabela_table.first..tabela_table.last  
  LOOP
    DBMS_OUTPUT.PUT_LINE(tabela_table(i).employee_id || ' - ' || 
                         tabela_table(i).first_name || ' - ' || 
                         tabela_table(i).last_name || ' - ' ||
                         tabela_table(i).phone_number || ' - ' ||
                         tabela_table(i).job_id || ' - ' ||
                         TO_CHAR(tabela_table(i).salary,'99G999G999D99'));   
  END LOOP;
END;

Veja a saída:

COLLECTIONS DO TIPO VARRAY

Muito parecido com o NESTED TABLE, o Varray precisa ser inicializado e a diferença é que devemos definir um tamanho máximo evitando assim estouro de memoria. Vamos ao exemplo de sintaxe:

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  TYPE Numero_Table_Type IS VARRAY (10) OF INTEGER(2);--limita o array
  Numero_table numero_table_type := numero_table_type();
BEGIN

  FOR i IN 1..10
  LOOP
    Numero_Table.extend;
    Numero_Table(i) := i;
  END LOOP;

  FOR i IN 1..10
  LOOP
    DBMS_OUTPUT.PUT_LINE('Varray: Indice = ' || TO_CHAR(i) || ', 
    Valor = ' || TO_CHAR(Numero_Table(i)));
  END LOOP;
END;

VARRAY RECORDS (BULK COLLECT)

Associa valores de uma tabela ao array, assim podemos ter a leitura de varias linhas, pra isso nós temos que declarar um varray LIMITADO, como do tipo %rowtype. Vejamos um exemplo:

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  TYPE employees_table_type IS VARRAY (200) OF employees%rowtype;
  tabela_table  employees_table_type := employees_table_type();
BEGIN
  SELECT *
    BULK COLLECT INTO tabela_table 
  FROM employees;
  FOR i IN tabela_table.first..tabela_table.last  
  LOOP
    DBMS_OUTPUT.PUT_LINE(tabela_table(i).employee_id || ' - ' || 
                         tabela_table(i).first_name || ' - ' || 
                         tabela_table(i).last_name || ' - ' ||
                         tabela_table(i).phone_number || ' - ' ||
                         tabela_table(i).job_id || ' - ' ||
                         TO_CHAR(tabela_table(i).salary,'99G999G999D99'));   
  END LOOP;
END;

MÉTODOS PARA CONTROLE DE COLLECTIONS

Depois de definidos os collections podemos usar os métodos de controles abaixo, assim como fizemos em .first ou .last no exemplo acima, e ira nos retorna os dados explicados na tabela: