O que é uma tabela aninhada (Nested Table)?

Este artigo faz uma abordagem sobre tabelas aninhadas (nested table) e em seguida, apresenta como criar, consultar uma tabela aninhada e também mecanismos para os DBAs poderem verificar e descrever as estruturas.

Primeiramente, o que é uma tabela aninhada?

Uma tabela aninhada, e também conhecida como (nested table) é uma tabela que é representada como uma coluna dentro de outra tabela. É um conjunto não ordenado de elementos do mesmo tipo. Tem uma única coluna e o tipo da coluna é um tipo pré-definido ou um tipo de objeto.

Exemplo:

create type  TELEFONES_NT as table of VARCHAR2(14) – (TABELA TIPO)

create table  EMPREGADOS
(Nome       VARCHAR2(25),
 CPF        NUMBER,

 Telefones  TELEFONES_NT )

 NESTED TABLE Telefones store as TELEFONES_ST;  - (Tabela aninhada)

Para melhor ilustrar a explicação vamos de criar um tipo chamado ADDRESS_TYPE. Depois criar o tipo chamado ADDRESS_TYPE criaremos uma tabela chamada ADDRESS_TABLE_TYPE. A tabela ADDRESS_TABLE_TYPE  será usada como um tipo de coluna de uma tabela aninhada.

CONFUSO? Parece bastante confuso no início, mas vale lembrar que todos os atributos de uma tabela precisam de alguma ter um tipo de dado. Estamos criando nosso próprio tipo de dados chamado ADDRESS_TABLE_TYPE que é uma tabela de tipos chamada ADDRESS_TYPE.

Abaixo estão as duas instruções de DDL para mostrar os detalhes de  ADDRESS_TYPE e ADDRESS_TABLE_TYPE realmente tem os mesmos atributos. Esta tabela poderá armazenar vários endereços para cada linha na tabela. Conforme apresentado abaixo:

SQL> CREATE TYPE address_type AS OBJECT

       (ADDR_LINE        VARCHAR2(50),

        CITY             VARCHAR2(50),

        STATE            VARCHAR2(02),

        ZIP              VARCHAR2(05)); - (TABELA TIPO)

SQL> CREATE TYPE address_table_type AS TABLE OF address_type;

SQL> DESC address_type

 Name               Type

 ------------------ --------------

 ADDR_LINE          VARCHAR2(50)

 CITY               VARCHAR2(50)

 STATE              VARCHAR2(2)

 ZIP                VARCHAR2(5)

Para verificar a criação da tabela aninhada pode-se observar a visão DBA_OBJECTS e para verificar o tipo criado deve-se observar a visão DBA_TYPES, usando os comandos abaixo:

SQL> select object_name,object_type 

       from dba_objects 

      where object_name like 'ADDRESS_%';

OBJECT_NAME          OBJECT_TYPE

-------------------- --------------

ADDRESS_TYPE          TYPE

ADDRESS_TABLE_TYPE    TYPE

SQL> select TYPE_NAME,TYPECODE,ATTRIBUTES 

       from dba_types 

      where type_name like 'ADDRESS_%';

TYPE_NAME           TYPECODE         ATTRIBUTES

------------------- ---------------- ----------

ADDRESS_TYPE        OBJECT           4

ADDRESS_TABLE_TYPE  COLLECTION       0

Para extrair o DDL do tipo criado deve-se acessar a visão DBA_SOURCE, usar o comando abaixo:

SQL> select text 

       from dba_source 

      where name = 'ADDRESS_TYPE';

TEXT

-------------------------------------------

TYPE address_type AS OBJECT

       (ADDR_LINE        VARCHAR2(50),

        CITY             VARCHAR2(50),

        STATE            VARCHAR2(02),

        ZIP              VARCHAR2(05));

7 rows selected.

SQL> select text 

       from dba_source 

      where name = 'ADDRESS_TABLE_TYPE';

TEXT

-------------------------------------------------------------------

TYPE address_table_type AS TABLE OF address_type;

Com os tipos criados agora para criar uma tabela, apenas é necessário especificar o nome de uma coluna (para este exemplo, se chamara ADDRESSES) e o tipo recém-criado (ADDRESS_TABLE_TYPE. Abaixo está o DDL para criar a tabela aninhada, alem dos outros comandos SQL  para verificar a criação, deve-se acessar a visão DBA_TAB_COLS, para verificar as colunas do aninhamento deve-se verificar as visões DBA_NESTED_TABLES e DBA_NESTED_TABLE_COLS.

CREATE TABLE employee (

Name          VARCHAR2(20),

addresses     ADDRESS_TABLE_TYPE) 

NESTED TABLE addresses STORE AS addresses_table;

SQL> DESC employee

Name                 Type

-------------------- -----------------

NAME                 VARCHAR2(20)

ADDRESSES            ADDRESS_TABLE_TYPE

SQL> DESC addresses_table

Name                Type

------------------- -------------

ADDR_LINE           VARCHAR2(50)

CITY                VARCHAR2(50)

STATE               VARCHAR2(2)

ZIP                 VARCHAR2(5)

SQL> SELECT table_name, column_name, data_type, data_length

       FROM dba_tab_cols

      WHERE table_name = 'EMPLOYEE';

TABLE_NAME COLUMN_NAME         DATA_TYPE           DATA_LENGTH

---------- ------------------- ------------------- -----------

EMPLOYEE   ADDRESSES           ADDRESS_TABLE_TYPE  16

EMPLOYEE   NAME                VARCHAR2            20

EMPLOYEE   SYS_NC0000200003$   RAW                 16

SQL> SELECT table_name, table_type_name,parent_table_column

       FROM dba_nested_tables

      WHERE table_name = 'ADDRESSES_TABLE';

TABLE_NAME       TABLE_TYPE_NAME            PARENT_TABLE_COLUMN

---------------- -------------------------- -----------------------

ADDRESSES_TABLE  ADDRESS_TABLE_TYPE         ADDRESSES 

SQL> select table_name,column_name,data_type

       from dba_nested_table_cols

      where table_name = 'ADDRESSES_TABLE';

TABLE_NAME           COLUMN_NAME                    DATA_TYPE

-------------------- ------------------------------ ------------

ADDRESSES_TABLE      NESTED_TABLE_ID                RAW

ADDRESSES_TABLE      STATE                          VARCHAR2

ADDRESSES_TABLE      CITY                           VARCHAR2

ADDRESSES_TABLE      SYS_NC_ROWINFO$                ADDRESS_TYPE

ADDRESSES_TABLE      ADDR_LINE                      VARCHAR2

ADDRESSES_TABLE      ZIP                            VARCHAR2

Se quisermos realmente usar esta tabela e a tabela aninhada podemos executar  alguns comandos DML. 

Abaixo estaremos inserindo informações na tabela, deixando a tabela aninhada vazia e em seguida inserindo dados na tabela aninhada. Para gerar um conjunto de resultados que você pode fazer uso da função tabela.

SQL> INSERT INTO employee (name, addresses)

     VALUES ('Dude', address_table_type());

1 row created.

SQL> INSERT INTO TABLE(SELECT addresses FROM employee WHERE name = 'Dude')

      VALUES ('addr1', 'city-1', '01', '11111');

1 row created.

SQL> INSERT INTO TABLE(SELECT addresses FROM employee WHERE name = 'Dude')

     VALUES ('addr2', 'city-2', '02', '22222');

1 row created.

SQL> INSERT INTO TABLE(SELECT addresses FROM employee WHERE name = 'Dude')

     VALUES ('addr3', 'city-3', '03', '33333');

1 row created.

SQL> select t1.name, t2.* from employee t1, table(t1.addresses) t2;

NAME  ADDR_L CITY    ST ZIP

----- ------ ------- -- -----

Dude  addr1  city-1  01 11111

Dude  addr2  city-2  02 22222

Dude  addr3  city-3  03 33333

Tabelas aninhadas, embora pareçam confusas, possuem uma característica interessante. As tabelas aninhadas podem dar algumas opções interessantes para definir objetos, especialmente quando se pode reutilizar  atributos comuns entre tabelas.

Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com