Complex Variable Types

Contact Us or call 1-877-932-8228
Complex Variable Types

Complex Variable Types

The two types of complex types are collections and records. All of the internal components of a collection are of the same PL/SQL data type. The internal components of a record (fields) may have different data types. PL/SQL has three collection types: index-by table (or associative array), nested table, and varray (or variable-size array).

Collection types have different attributes related to how they allocate and store data that can make them useful in certain circumstances to enhance performance. However, due to the tight integration of SQL with PL/SQL, many tasks that might be handled by a collection simply can be addressed by using SQL queries and other Oracle features such as temporary tables.

Records are often created to correspond with existing database tables. They provide an efficient syntax to ensure that variables used by a PL/SQL program are of the same type and precision of data stored in a table. This prevents various errors that can result when data exceeds the size of the declared type and prevents concern of data loss when passing data between PL/SQL and the database.

Code Sample:

Declare-Clause/Demos/record_type_variables.sql
DECLARE
TYPE personinfo
  IS
  RECORD
  ( pid       INT :=0,
    firstname VARCHAR2(20),
    lastname  VARCHAR2(30),
    address1  VARCHAR2(50),
    city      VARCHAR2(40),
    state     CHAR(2),
    zipcode   VARCHAR2(10) );

  person personinfo;
BEGIN
  -- Using composite data type
  person.pid       := 1;
  person.firstname := 'Sam';
  person.lastname  := 'Spade';
  person.address1  := '1 Maltese Way';
  person.city      := 'New York';
  person.state     := 'NY';
  person.zipcode   := '10020';
  DBMS_OUTPUT.PUT_LINE('ID =' || person.pid ||
                       ', First Name = ' || person.firstname);
END;
/

Code Explanation

Code Sample:

Declare-Clause/Demos/record_using_rowtype.sql
DECLARE
  Person employees%ROWTYPE;
BEGIN
  SELECT *
  INTO person
  FROM employees
  WHERE rownum <= 1;

  DBMS_OUTPUT.PUT_LINE('1st "random" employee is ' ||
                       person.first_name || '.');
END;
/

Code Explanation

Code Sample:

Declare-Clause/Demos/record_using_type.sql
The problem with using %rowtype is that it is usually used
-- in conjunction with select *, a very slow performing query.
-- So, use just the columns you need.
DECLARE
  TYPE personinfo IS record
      (firstname  employees.first_name%TYPE,
       lastname   employees.last_name%TYPE,
       empsalary  employees.salary%TYPE);

  person personinfo;

BEGIN
  SELECT first_name, last_name, salary
  INTO person
  FROM employees
  WHERE rownum <= 1;

  DBMS_OUTPUT.PUT_LINE('1st "random" person is ' ||
                       person.firstname || ' ' ||
                       person.lastname ||
                       ', with a salary of ' ||
                       person.empsalary);
END;
/


-- Here is an alternative to using a record type.
declare
  firstname  employees.first_name%type;
  lastname   employees.last_name%type;
  empsalary  employees.salary%type;

begin
  select first_name, last_name, salary
  into firstname, lastname, empsalary
  from employees
  where rownum <= 1;

  dbms_output.put_line('1st "random" person is ' ||
                       firstname || ' ' || lastname ||
                       ', with a salary of ' ||
                       empsalary);

end;

Code Explanation

Code Sample:

Declare-Clause/Demos/associative_array_index_by_varchar2.sql
-- Using a collection (in this case an associative array).
DECLARE
  TYPE population_size
     IS
     TABLE OF NUMBER INDEX BY VARCHAR2(64);

  city_population   population_size;
  state_population  population_size;

  NY_population       NUMBER;
  index_position_id   VARCHAR2(64);
BEGIN
  -- Enter the populations of ...
  city_population('Syracuse') := 300000;
  state_population('NY')      := 3000000;
  city_population('Albany')   := 400000;
  state_population('CA')      := 3500000;

  -- returns value for index position NY
  NY_population    := state_population('NY');

  -- returns name of first index position (Albany)
  index_position_id := city_population.FIRST;
  DBMS_OUTPUT.PUT_LINE( 'The name of first index position is '
                       || index_position_id);
  DBMS_OUTPUT.PUT_LINE ('The value of state(''NY'') is '
                       || NY_population);
END;
/

Code Explanation

Code Sample:

Declare-Clause/Demos/associative_array_index_by_number.sql
-- Creating a datatype of associative array with the
-- index by clause of number.
DECLARE
    TYPE salaries
    IS
      TABLE OF employees.salary%TYPE
      index BY binary_integer;

    my_salaries salaries;
BEGIN

 -- We'd really use a loop, but we cover
 -- loops in the next chapter. In the meantime...

 select salary
 into my_salaries(1)
 from employees
 where rownum <= 1;
 dbms_output.put_line('1st "random" salary is ' || my_salaries(1));

 select salary
 into my_salaries(2)
 from employees
 where rownum <= 1
 and my_salaries(1) <> salary;
 dbms_output.put_line('2nd "random" salary is ' || my_salaries(2));
END;
/

Code Explanation

Code Sample:

Declare-Clause/Demos/associative_array_without_index_by.sql
-- Creating a datatype of associative array
-- WITHOUT the index by clause.
DECLARE
    TYPE nbrs
    IS
      TABLE OF NUMBER;

    my_numbers nbrs;

BEGIN
  my_numbers := nbrs(99,98,97,96,95);

  dbms_output.put_line('my_numbers(1) is ' || my_numbers(1));
  DBMS_OUTPUT.PUT_LINE('my_numbers(3) is ' || my_numbers(3));

  my_numbers(5) := 6;
  DBMS_OUTPUT.PUT_LINE('my_numbers(5) is now ' || my_numbers(5));

  -- We can extend the array past its size of 5:
  my_numbers.extend;
  my_numbers(6) := 42;
  DBMS_OUTPUT.PUT_LINE('my_numbers(6) is ' || my_numbers(6));

END;
/

Code Explanation

Code Sample:

Declare-Clause/Demos/varray.sql
DECLARE
  TYPE state_abbrev IS varray(10) OF CHAR(2);

  states state_abbrev;

BEGIN
    states := state_abbrev('NY','CA','MD','ME','FL');

    dbms_output.put_line( 'My state is: ' || states(1));

    -- Just as with an associative array created WITHOUT an index,
    -- we're "stuck" with just 5 members in the varray even though
    -- it has a size of 10. But we can extend here as well.
    states.extend;
    states(6) := 'GA';
    dbms_output.put_line( 'Your state is: ' || states(6));
    states.extend;
    states(7) := 'IL';
END;
/

Code Explanation

Next