I released a new video course for Pluralsight: Working with Composite Data Types.

The course is included in the path for the Oracle certification for PL/SQL programming and it will teach you how to use collections and records.

The first module is free, for the rest of the modules a subscription is needed. Anyone interested in a one month free subscription at Pluralsight please contact me privately and I will share a code.

Main Topics

The main topics covered by this course are:

• What are composite data types, their advantages over scalar ones and their two main types: collections and records;

• What is a collection, and the types of collections: variable arrays, nested tables and associative arrays;

• What is a record, and the types of records: the user defined records and the records based on tables or cursors using the attribute ROWTYPE;

• And how to define collections of records using the clause BULK COLLECT.

I will present in the following sections the main topics in the course and a selection of examples and code for some topics.

Composite Data Types

Oracle database and PL/SQL language have two categories of data types: Scalar data types can store one value. It can be number, character, date or large objects. Composite data types can store multiple values of the same type or of different types.

A composite data type stores values that have internal components. Internal components can be either scalar or composite. You can pass the entire composite variable as a parameter to a subprogram and you can also access the internal components individually inside the subprogram.

Collections and Records

Oracle has two composite data types: collections and records. Collections are sets of components with the same data type. Records are structures with components of different data types.

A collection can be represented graphically as a horizontal set with all the elements having the same data type. The collection in the left has all the elements numbers with the values 1, 2 and 3.

A record, on the other hand, can be represented as a table with one row and more fields of different data types. The record in the right has three fields: first number, second string with one character and third string with three characters. The record has value 1 for first field, value A for second field and value ABC for third field.

Collections

A collection is a set of components of the same data type arranged in a certain order named elements.

Oracle supports three types of collections:

• Variable arrays or Varrays are collections with a fixed maximum number of elements indexed by number. A varray can have any number of elements between 0 and the maximum number declared in its specifications. They are useful when you know ahead the maximum number of items that will be stored in the collection.

• Nested tables are lists with a variable number of elements indexed by number. You should use nested tables when you do not know the number of elements that will be stored in the collection before declaring the collection.

• Associative arrays, also known as index by tables, are arrays with variable number of key-value pairs. You should use associative arrays when you do not know ahead the number of elements, just like nested tables. They are more flexible because they can be indexed by number or string.

This is an example of a nested table: a list of employees. This nested table has 4 elements with no gaps between them: first element has index 1 and value Kent, second element has index 2 and value Wayne, third element has index 3 and value Allen and fourth element has index 4 and value Prince. We can delete any element in the nested table even if it’s an intermediate one. For instance, we can delete the element on index 3 and leave a gap there and then add new elements on index 5 and so on because the nested table is unbounded.

Let’s see a code example of defining and using a nested table. Out of all three types of collections, we use a nested table to process employees because the list of employees is variable, as some employees may leave and some new employees can be hired. Note how we parse all the emelents in the collection using the methods first and last.

declare
  type employees is table of varchar2(30);
  employee employees;
  first integer;
  last integer;
begin
  employee := employees('Kent', 'Wayne', 'Allen', 'Prince');
  first := employee.first;
  last := employee.last;
  for i in first..last loop
    dbms_output.put_line('Element ' || i || ': ' || employee(i));
  end loop;
end;

Records

A record is a structure with components of different data types named fields.

Oracle supports three types of PL/SQL records:

• User defined records, with a custom explicit structure in which we specify the number of fields and the name and data type for each field;

• Table based records, which copy implicitly the number, names and data types of fields from a table using the attribute ROWTYPE;

• Cursor based records, which copy implicitly the number, names and data types of fields from a cursor.

This is an example of a table based record: a record employee based on the table employees in the database with the same fields and data types: name, department and salary. In a variable of type table based record we can store only one row from the source table. In this example, the value for the field name is Wayne, the value for the field department is DC and the value for the field salary is 84 hundred.

And a code example with the table based record employee based on the database table employees. Not how we populate the record by selecting all the columns from the database table and how we update the row in the database table with the whole content of the table based record.

declare
  employee employees%rowtype;
begin
  select * into employee from employees where name = 'Wayne'; 
  dbms_output.put_line(employee.name);
  dbms_output.put_line(employee.department);
  dbms_output.put_line(employee.salary);
  employee.salary := 8800;
  update employees set row = employee where name = employee.name;
end;

Collections of Records

A collection of records is a collection in which each element is a record.

Let’s see an example of collection of records: a variable employee set that holds multiple records from the employees table in the database. The difference between a collection of records and a record is that the record can store only one row from the source table and the collection of records can store multiple rows. In this example, the collection of records stores two records: first record has name Prince, department DC and salary 56 hundred; second record has name Quinn, department DC and salary 25 hundred.

And a code example of creating and using a collection of records for processing two employees. Note how the collection of records is populated by selecting in it with the clause BULK COLLECT from the table employees the rows with names Prince and Quinn.

declare
  type t_employees is table of employees%rowtype;
  employee_set t_employees;
begin
  select * bulk collect into employee_set from employees where name in ('Prince', 'Quinn'); 
  for i in employee_set.first..employee_set.last loop
    dbms_output.put_line(employee_set(i).name);
  end loop;
end;

Final Thoughts

This article contains the main topics in the course and a selection of examples and code for some topics. For understanding all the concepts please watch the entire video course: Working with Composite Data Types. If you do not have a subscription, please contact me privately and I will share a code for a one month free subscription at Pluralsight.

By the end of watching the Working with Composite Data Types course, you will know how to create and use collections and records and how to integrate them efficiently in PL/SQL subprograms.

Check here a list of all the video courses created by me for Pluralsight.