Use of PL/SQL tables of types : Table of Type « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. Table of Type

SQL>
SQL>
SQL> create table employee (
  2  id                             number                         not null,
  3  employee_type_id               number                         not null,
  4  external_id                    varchar2(30)                   not null,
  5  first_name                     varchar2(30)                   not null,
  6  middle_name                    varchar2(30),
  7  last_name                      varchar2(30)                   not null,
  8  name                           varchar2(100)                  not null,
  9  birth_date                     date                           not null,
 10  gender_id                      number                         not null );

Table created.

SQL>
SQL> declare
  2
  3
  4      TYPE name_table IS TABLE OF employee.name%TYPE INDEX BY BINARY_INTEGER;
  5
  6      t_name name_table;
  7
  8      n_name binary_integer;
  9
 10  begin
 11    t_name(1)  := 'AAA';
 12    t_name(10) := 'BBB';
 13
 14    DBMS_OUTPUT.PUT_LINE(t_name(1));
 15    DBMS_OUTPUT.PUT_LINE(t_name(10));
 16
 17    DBMS_OUTPUT.PUT_LINE('There are '||t_name.count()||' elements.');
 18    n_name := t_name.first();
 19
 20    DBMS_OUTPUT.PUT_LINE('The first element is '||n_name||'.');
 21    n_name := t_name.next(n_name);
 22
 23    DBMS_OUTPUT.PUT_LINE('The next element is '||n_name||'.');
 24    n_name := t_name.last();
 25
 26    DBMS_OUTPUT.PUT_LINE('The last element is '||n_name||'.');
 27    n_name := t_name.prior(n_name);
 28
 29    DBMS_OUTPUT.PUT_LINE('The prior element is '||n_name||'.');
 30
 31    if t_name.exists(1) then
 32      DBMS_OUTPUT.PUT_LINE('Element 1 exists.');
 33    end if;
 34
 35    DBMS_OUTPUT.PUT_LINE('I''m deleting element 10');
 36
 37    t_name.delete(10);
 38
 39    DBMS_OUTPUT.PUT_LINE('There are '||t_name.count()||' elements.');
 40
 41    if not t_name.exists(10) then
 42      DBMS_OUTPUT.PUT_LINE('Element 10 no longer exists.');
 43    end if;
 44
 45    DBMS_OUTPUT.PUT_LINE('There are '||t_name.count()||' elements.');
 46
 47    DBMS_OUTPUT.PUT_LINE('I''m deleting all elements');
 48
 49    t_name.delete();
 50
 51    DBMS_OUTPUT.PUT_LINE('There are '||t_name.count()||' elements.');
 52
 53  end;
 54  /
AAA
BBB
There are 2 elements.
The first element is 1.
The next element is 10.
The last element is 10.
The prior element is 1.
Element 1 exists.
I'm deleting element 10
There are 1 elements.
Element 10 no longer exists.
There are 1 elements.
I'm deleting all elements
There are 0 elements.

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table employee;

Table dropped.

SQL>
SQL>
SQL>