Composite Datatypes.

Records

The scalar types are defined in package STANDARD. To use them we just need to declare variables of required types. Composite types, on the other hand, are user defined. In order to use a composite type, we first must define the type, and declare a variable of the type. PL/SQL records are similar to C structures. The general syntax for defining a record is: 
type record_type is record (
  field_name type [not null] [:= expression],
  field_name type [not null] [:= expression],
  ...  
  field_name type [not null] [:= expression]
);
Example: 
declare
  type TStudent is record (
    StudID char(5),
    FName  varchar(16),
    LName  varChar(24)  
  );
  StudentInfo TStudent; 
begin
  select id, firstname, lastname
    into StudentInfo 
    from Students
   where id = 'IT345';	
  ... 
end;

To access a particular field inside a record we use operator dot:

record_name.field_name
The following example show how to add a line printing student's last name to the previous example: 
  dbms_output.put_line('Last name: ' || StudentInfo.LName);

In order to one record to be assign to another, both records must be of the same type. For example, if we have two variables of the type TStudent we can have an assignment like this: 

  StudentInfo_1 := StudentInfo_2;

Using %rowtype

It's common in PL/SQL to declare a record with the same types as a database row. PL/SQL provides the %rowtype operator to facilitate this. Similar to %type, %rowtype will return s type based on the table definition. For example, if we need to read a whole row from the Students table into a PL/SQL variable, we can create a composite variable: 
declare
  StudInfo Students%rowtype;
begin
  select *
    into StudInfo
    from Students
   where id = 'IT3456';
  dbms_output.put_line('Last name: ' || StudInfo.firstname);	
end;
The names of the fields in the composite variable (record) StdInfo are the same as field names in the table Students. As with %type, any not null constraint defined on the column is not included.

Arrays

Another composite type PL/SQL allows to use is varray. This is varying array type that allows to store a number of elements of the same type. The general syntax for varray types is: 
type type_name is varray(size_limit) 
   of element_type [not null];
where type_name is the name of the new type, element_type is any of standard or used defined types, and size_limit is the maximum number of elements in the array. When defining a varray type, you must specify its maximum size.

In the following example we create an array of 25 elements of a record type and use this array to insert data in a previously created table: 

declare
  type TAreaArray is varray(25) of Areas%rowtype;
  my_areas TAreaArray := TAreaArray();
In the last line we initialized the new array. After this initialization our array contains zero elements. Each collection object has several methods available we'll discuss only some of them:

To access a particular element of an array we need to use parenthesis with the index of the element inside. For example, to access the third element of the array my_areas we need to use: 

 my_areas(3)

Now we know enough to understand the following example: 

declare
  type TAreaArray is varray(25) of Areas%rowtype;
  my_areas TAreaArray := TAreaArray(); 
  pi constant number(9,8) := 3.14159265;
begin
  my_areas.extend(25);
  for i in 1..my_areas.count loop
    my_areas(i).radius := 2 * i;
    my_areas(i).area   := pi * my_areas(i).radius**2; 
  end loop;
  for i in 1..my_areas.count loop
    insert into Areas values (my_areas(i).radius, my_areas(i).area);
  end loop;
end;

For more information about arrays and other composite types please read section Collections and Records from the Oracle documentation.