(7) Composite Data Types

[ Oracle PL/SQL ]
Composite data types: COllections and Records

Composite data type:
  • Stores values that have internal components (scalar or composite).
  • Collections can be built of any SQL or PL/SQL datatype.
  • Fall into two categories: arrays(size allocated at definition) and lists.
  • Lists are called associative arrays when they can be indexed by non-sequential numbers or unique strings.
  • Multidimensional collections can be built as PL/SQL datatypes. In this case, collection elements are record structures.

  • Internal components always have the same data type (elements).
  • Access syntax: variable_name(index)
  • To create a collection variable:
    1. Define a collection type
    2. Create a variable of that type
    OR use %TYPE.

Collection Types:
  • Associative Arrays (former PL/SQL table or index-by table)
  • Variable-size Arrays (VARRAY)
  • Nested Table

Number of Elements: { Specified | Unspecified }
  • Whether you need to define the maximum size of the collection at declaration time.
  • Specified: VARRAY; Unspecified: Associative array; Nested table

Uninitialized status: { Empty | Null }
  • Empty: Exists, but has no elements. (Use EXTEND method to add).
  • Null: Does not exist. Must be initialized (Made empty or assigned a value).
  • Null: VARRAY, Nested Table. Empty: Associative Array

Scope: { Local | Public | Standalone (Schema) }
  • Local: Defined and available only within a PL/SQL block.
  • Public: Defined in a package specification.(ref: package_name.type_name)
  • Standalone: Defined at schema level.(ref: CREATE TYPE)

Collections: Associative Array (former PL/SQL TABLE)

  • Name introduced in Oracle 10g
  • Oracle 8 - 9i: index-by tables
  • Oracle 7: Pl/SQL tables
  • Can be indexed by Integer or String (no order here)
  • Can be passed as parameter and returned by function
  • Can be declared as a CONSTANT
  • The AA is only a PL/SQL datatype: can only be referred in a PL/SQL scope.
  • AAs are typically defined in package specifications when they are to be used externally from an anonymous or named block program.
  • AAs Numeric indexes do not need to be sequential.
  • These are sparsely populated structures: can have gaps in index sequences.
  • AAs are dynamically sized (like the NESTED TABLE datatype).

Who has the highest salary in each department?

(a) Define a CURSOR to extract:
[ Dept Name, Employee Name, Max Salary ]
(b) Define a RECORD TYPE to hold:
[ Employee Name, Max Salary ]
(c) Define an ASSOCIATIVE ARRAY TYPE (IS TABLE OF) indexed by String.
(d) Declares a variable of the ASSOCIATIVE ARRAY TYPE
(e) Open the CURSOR and populate the variable with data from.
(f) Print the data.

SQL> Set serveroutput on

   -- Declare cursor ccur to extract data (dname, ename, maxsal)
 Cursor ccur IS
  select d.department_name dname, 
         e.first_name ||' '|| e.last_name ename, e.salary maxsal
  from employees e, 
       ( select department_id, max(salary) msal
        from employees
        group by department_id) s, 
       departments d
  where e.department_id = d.department_id
   and e.department_id = s.department_id
   and e.salary = s.msal
  order by e.department_id;
    -- Define a Record type to hold (ename, maxsal)
  TYPE EmpSalRec IS RECORD (ename varchar2(60), maxsal employees.salary%TYPE);

   -- Define an associative array indexed by department name. The array stores the
   -- the composite structure (ename, maxsal)
  TYPE MaxSalDep IS TABLE OF EmpSalRec INDEX BY departments.department_name%TYPE;
   -- Declare a variable of the associative array type.
  v_MaxSalDep MaxSalDep;
 FOR deps IN ccur LOOP
    v_MaxSalDep(deps.dname).ename := deps.ename;
    v_MaxSalDep(deps.dname).maxsal := deps.maxsal;
    dbms_output.put_line('MaxSal Dept '      || deps.dname || ': ' || 
                          ' (' || v_MaxSalDep(deps.dname).ename || 
                          ', ' || v_MaxSalDep(deps.dname).maxsal || ');'

MaxSal Dept Administration:  (Mary Fansom, 25000);
MaxSal Dept Marketing:  (Michael Hartstein, 13000);
MaxSal Dept Purchasing:  (Den Raphaely, 11000);
MaxSal Dept Human Resources:  (Kimberely Grant, 7000);
MaxSal Dept Shipping:  (Adam Fripp, 8200);
MaxSal Dept IT:  (Alexander Hunold, 9000);
MaxSal Dept Public Relations:  (John Fergunson, 26000);
MaxSal Dept Sales:  (John Russell, 14000);
MaxSal Dept Executive:  (Steven King, 24000);
MaxSal Dept Finance:  (Nancy Greenberg, 12000);
MaxSal Dept Accounting:  (Shelley Higgins, 12000);

Using Associative Array as a Constant
  • The example below loads an Associative Array Constant with the names of all departments.
  • Using the AA Constant, it then lists the number of employees in each department.
  • The AA Constant will be defined with the scope of a package.

(a) In the Package Spec, define the specification of the AA Constant TYPE and the specification of the Function that will initialize the AA Constant.
(b) In the Package Body, write the initialization function.
(c) In an anonymous block, declare constant, intialize it, and loop through the array.

SQL> Set serveroutput on

   -- Declaring an AA in a package spec makes it available for invoked and invoking 
   -- subprograms and to anonymous blocks.
  TYPE AA_FixDept IS TABLE OF departments.department_name%TYPE
                    INDEX BY pls_integer;
END My_Types;
Package created.

    -- Function Init_AA_FixDept will initialize the AA Constant.
  FUNCTION Init_AA_FixDept RETURN AA_FixDept IS    
    CURSOR ccur IS
      SELECT department_name FROM departments;
    Ret AA_FixDept;
    i PLS_INTEGER := 0;
     FOR j IN ccur LOOP
      Ret(i) := j.department_name;
       -- Use dbms_output to check the proper loading of the AA Constant.
       -- Comment or remove the line later.
      dbms_output.put_line( 'Loading dept '|| i || ': ' || j.department_name);
      i := i + 1;
     END LOOP;
   RETURN Ret;   
  END Init_AA_FixDept;
END My_Types;
Package created.

SQL> Set serveroutput on
   -- Declare the AA Constan c_AAdept. 
   -- The Constant should be initialized at declaration time.
 c_AAdept CONSTANT My_Types.AA_FixDept := My_Types.Init_AA_FixDept();
 CURSOR cnumep (namedep departments.department_name%TYPE) IS
    FROM employees e, departments d
    WHERE e.department_id = d.department_id
    and department_name = namedep;
 v_index integer;
 v_numep integer;
  -- Listing the number of employees in each department;
  -- Loop through each component of the AA Constant and pass the department name
  -- as parameter to the explicit cursor cnumep.
  v_index := c_AAdept.First;
   OPEN cnumep(c_AAdept(v_index));
   FETCH cnumep INTO v_numep;
   dbms_output.put_line('Dept ' || c_AAdept(v_index) || ' has ' || 
                        v_numep || ' employees');
   CLOSE cnumep;
   EXIT WHEN v_index = c_AAdept.Last;
   v_index := c_AAdept.next(v_index);

(Edited ouput):
Loading dept 0: Administration
Loading dept 1: Marketing
Loading dept 26: Payroll

Dept Administration has 2 employees
Dept Marketing has 2 employees
Dept Purchasing has 6 employees
Dept Payroll has 0 employees

When to use what?

Appropriate uses for AAs:
- The physical size is unknown and
- the type will not be used in tables.
- Good for standard solutions (i.e. using maps and sets).
- Good for small lookup tables.

When passing collections App <==> DB server. Use either:
- You know the maximum number of elements.
- You usually access the elements sequentially.
- The physical size of the collection is static
- The collection may be used in tables.
- Varrays are the closest thing to arrays in other programming languages (Java, C, C++, C#).

- The physical size is unknown and the type may be used in tables.
- Nested tables are like lists and bags in other programming languages.

Collections: VARRAYS
  • Introduced in Oracle 8
  • Can be used in table, record, and object definitions.
  • Fixed max size at creation.
  • Num elements: Between 0 and declared maximum size.
  • May be stored in permanent tables and accessed by SQL.
  • If VARRAY variable is less than 4 KB: it resides inside the table of which it is a column; otherwise, it resides outside the table but in the same tablespace.
  • Must be initialized. Otherwise is a null collection.
  • Densely populated structures: they do not allow gaps in the index values.
  • VARRAY and NESTED TABLE datatypes are structures indexed by sequential integers.

Using Varray: loading values from table
  • Load and print name and hire_date of the three longest employeed workers

(a) Define a Varray of STRING with max 3 rows.
(b) Declare the varray variable. Use constructor to set it to null.
(c) Declare CURSOR to return list of data from database.
(d-f) Print empty varray. Load Varray looping through cursor. Print loaded Varray.

SQL> set serveroutput on
-- Define a varray with a maximum of 3 rows.
TYPE str_varray IS VARRAY(3) OF VARCHAR2(100);

-- Declare the varray with and initialize it with null values.
-- Failure to do so at this point will produce an 
-- "ORA-06531: Reference to uninitialized collection".

-- Memory and proper index values are allocated opon initialization of EACH ELEMENT.
-- If you fail to initialize ALL elements, you need to use the EXTEND method or 
-- you'll face an "ORA-06533: Subscript beyond count" at run-time.
varray_str str_varray := str_varray(NULL,NULL,NULL);

vstr varchar2(100);
  select ename, hire_date
  from (select first_name || last_name as ename, hire_date, 
                  row_number() over (order by hire_date) r
          from employees)
  where r between 1 and 3;

-- Print initialized null values.
dbms_output.put_line('Varray initialized as nulls.');
FOR i IN 1..3 LOOP
 dbms_output.put_line ('String Varray ['||i||'] ' ||

-- Assign values to subscripted members of the varray.
FOR j in ccur LOOP
 varray_str(i) := j.ename || ' ' || to_char(j.hire_date);
 i := i + 1;
-- Print initialized null values.
dbms_output.put (CHR(10));  -- Visual line break.
dbms_output.put_line('Varray initialized as values.');

FOR j IN 1..3 LOOP
 dbms_output.put_line('String Varray ['||j||'] ' ||

anonymous block completed
Varray initialized as nulls.
String Varray [1] []
String Varray [2] []
String Varray [3] []

Varray initialized as values.
String Varray [1] [StevenKing 17-JUN-87]
String Varray [2] [JenniferWhalen 17-SEP-87]
String Varray [3] [NeenaKochhar 21-SEP-89]

                       VARRAY(3) of VARCHAR2(100);

Type created.

-- initialize without allocating space
varray_str str_varray := str_varray(); 
FOR j in ccur LOOP
 -- increment index and allocate space
 varray_str(i) := j.ename || ' ' || to_char(j.hire_date);
 i := i + 1;

  1. You could creat a user-defined object type.
  2. Benefits: it may be referenced from any programs that have permission to use it, whereas a PL/SQL varray type structure is limited to the program unit.
  3. You could allocate space for the VARRAY
    at run-time, as you increment the index:

Collections: Nested Tables
  • Introduced in Oracle 8
  • Initially defined as densely populated arrays, but may become sparsely populated as records are deleted.
  • May be stored in permanent tables and accessed by SQL.
  • May be dynamically extended: act more like bags and sets than arrays.
  • May contain a list of one or more compound datatypes (PL/SQL records) when they work exclusively in a PL/SQL scope.
  • In the database, a nested table column type stores an unspecified # of rows in no particular order.
  • When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1.

(1) Define a composite NESTED TABLE TYPE (IS TABLE OF) of the type of employees table
(2) Create variable of the defined type. Initialize empty.
(3) Read all employees records on Cursor
(4) Iteract through the cursor and
(4.1) Extend the nested table
(4.2) Load three fields of the nested table with data from cursor.

set serveroutput on
 TYPE emp_table IS TABLE OF employees%ROWTYPE; 
 emps emp_table := emp_table();
  select employee_id, first_name, salary
  from employees;
-- Print initialized null values.
dbms_output.put_line('Nested table initialized empty.');
j := 1;
dbms_output.put_line('Begin loading...');
FOR i IN ccur LOOP
 emps(j).employee_id := i.employee_id;
 emps(j).first_name := i.first_name;
 emps(j).salary := i.salary;
dbms_output.put ('Nested Table Extended ['||j||'] ');
dbms_output.put_line('['||emps(j).employee_id ||', '
      || emps(j).first_name || ', ' || emps(j).salary ||']'); 
j := j + 1;

anonymous block completed
Nested table initialized empty.
Begin loading...
Nested Table Extended [1] [100, Steven, 24000]
Nested Table Extended [2] [101, Neena, 17000]
Nested Table Extended [3] [102, Lex, 17000]
Nested Table Extended [108] [210, John, 26000]
Nested Table Extended [109] [211, Mary, 25000]

Collection Methods
Method Type Description
DELETE Procedure Deletes elements from collection.
TRIM Procedure Deletes elements from end of varray or nested table.
EXTEND Procedure Adds elements to end of varray or nested table.
EXISTS Function Returns TRUE if and only if specified element of varray or nested table exists.
FIRST Function Returns first index in collection.
LAST Function Returns last index in collection.
COUNT Function Returns number of elements in collection.
LIMIT Function Returns maximum number of elements that collection can have.
PRIOR Function Returns index that precedes specified index.
NEXT Function Returns index that succeeds specified index.

Using collection methods
set serveroutput on 
 type nbrlast is table of varchar2(10);
  n nbrlast := nbrlast('paul','john','ringo','george');
 dbms_output.put_line('Num elements held: ' || n.count);
 dbms_output.put_line('The last element is ' || n(n.last));
 n.extend;     -- append a new (null) element
 n(n.last) := 'Yoko';
 dbms_output.put_line('Added Yoko');
 dbms_output.put_line('Now, num elements held: ' || n.count);
 dbms_output.put_line('The last element now is ' || n(n.last));
 n.extend(3);  -- append two null elements to the collection
 dbms_output.put_line('appended three null elements');
 dbms_output.put_line('Now, num elements held: ' || n.count);
 dbms_output.put_line('deleted element 6');
 dbms_output.put_line('Now, num elements held (count): ' || n.count);
 dbms_output.put_line('Now, highest subscription # (last): ' || n.last);
 n(7) := 'Ghost';
 dbms_output.put_line('element 7 is now: ' || n(7));
 dbms_output.put_line('Now, num elements: ' || n.count);

 if n.limit is null then
   dbms_output.put_line('Max num elements: unlimited.');
 end if;
 for i in n.first..n.last loop
   if n.exists(i) then 
     if n(i) IS NULL then
        dbms_output.put_line('Element #  '|| i || ' is NULL');
        dbms_output.put_line('Element #  '|| i || ' is '|| n(i));
     end if;
        dbms_output.put_line('Element #  '|| i || ' does not exist');
   end if;
  end loop;

anonymous block completed
Num elements held: 4
The last element is george
Added Yoko
Now, num elements held: 5
The last element now is Yoko
appended three null elements
Now, num elements held: 8
deleted element 6
Now, num elements held (count): 7
Now, highest subscription # (last): 8
element 7 is now: Ghost
Now, num elements: 7
Max num elements: unlimited.
Element #  1 is paul
Element #  2 is john
Element #  3 is ringo
Element #  4 is george
Element #  5 is Yoko
Element #  6 does not exist
Element #  7 is Ghost
Element #  8 is NULL

No comments:

Post a Comment