Oracle PL/SQL Tutorial (Part 13)

In part 13 of our Oracle PL/SQL tutorial, we continue our introduction to the use of PL/SQL collections by looking at multi-level collections.

Multilevel PL/SQL Collections

Oracle PL/SQL Collection Types

Just to recap, very briefly, the 3 different types of PL/SQL collections (see here for the details of the Oracle PL/SQL collection types), are:-

PL/SQL nested tables - see part 10 of this tutorial
PL/SQL varrays - see part 11
PL/SQL associative arrays - covered in Oracle PL/SQL tutorial part 12

So far we've seen how we can create tables or arrays in PL/SQL that are single dimensional but what happens if we want multi-dimensional arrays ? Or to have tables that are really nested as we can have in the database? Before Oracle9i we couldn't do it, but in Oracle 9i and above we can.

To demonstrate this, let's use the example of students in a college or university. First we need to think about what information we need to hold on these students and then translate these requirements into PL/SQL data structures.

In the real world the information we would want to know about students would most likely include :

addresses (home/holiday, term-time)
phone numbers (home, term-time, mobile)
email addresses (home, college, other)
first and last name
etc

but let's keep things simple for this example and restrict ourselves to just the first and last name and 2 addresses simply to demonstrate the principles of nesting collections. This means that we have 1 entity - student - with the following attributes : first & last name and 2 addresses. In a database, entities translate to a table, so we have our first data structure a table (or collection in PL/SQL terms). The attributes would normally translate to columns in the table but Oracle's PL/SQL doesn't have the concept of columns so we have to use the record structure.

Now we have a problem though. Our student record needs to contain 2 addresses, how do we handle this in PL/SQL ? We could just have two different address attributes (a home address and an term-time address) but we might need to add more addresses later so we'll use a PLSQL collection for the addresses.

The next problem is that each address has many lines, how do we handle that? Again, no problem. We simply define an address as a collection of address lines.

So let's take a look at our data structures.

We have addresses which are tables of address lines, but to declare this in PL/SQL we need to create the address lines type first and then create the address type, as follows:

TYPE address_lines IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

TYPE address_tab IS TABLE OF address_lines INDEX BY VARCHAR2(10);

Note that we are indexing address_lines by binary_integer and addresses by varchar2 so that we can look up addresses by name (e.g. 'home' or 'term-time') rather than by number.

Now we have nested our collections (associative arrays in this case) - we have a table of addresses and each address consists of a collection of address lines. Note that we can't use any of these data structures yet, as we've only defined the types, not declared any variables of those types.

The next thing to do is to define our record structure to hold the student information as follows:

TYPE student IS RECORD      (
       first_name VARCHAR2(50),
       last_name VARCHAR2(50),
       addresses address_tab);

So far, so good, but we only have the ability to store information about 1 student at the moment so we need to define another type - a table (collection) of students:

TYPE students_tab IS TABLE OF student INDEX BY BINARY_INTEGER;

Note that this time we're indexing by binary integer, which could be the numeric student id, as we're already storing the first and last names

The final data structure declaration is that of the variable to store the data.

students students_tab;

Now we have PL/SQL collections nested 3 deep - we have a collection of students, each of which has a collection of addresses (home, term-time, etc), each of which in turn has a collection of address lines. We could extend the nesting by having say a collection of colleges, each of which has a collection of students and so on, but just because we can do things it doesn't necessarily mean we should do them!

Let's put this all together now and write some code to store and retrieve some data.

DECLARE

TYPE address_lines IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

TYPE address_tab IS TABLE OF address_lines INDEX BY VARCHAR2(10);

TYPE student IS RECORD     (
       first_name VARCHAR2(50),
       last_name VARCHAR2(50),
       addresses address_tab);

TYPE students_tab IS TABLE OF student INDEX BY BINARY_INTEGER;

students students_tab;

BEGIN

students(1).first_name := 'Mike';
students(1).last_name := 'Johnson';
students(1).addresses('HOME')(2) := 'Bristol';
students(1)Addresses('HOME')(1) := '1 The Avenue';
students(1)Addresses('HOME')(3) := 'BS99 3SJ';

students(2)First_name := 'Mike';
students(2)Last_name := 'Bliss';
students(2)Addresses('HOME')(2) := 'Leicester';
students(2)Addresses('HOME')(1) := '11 The Street';
students(2)Addresses('HOME')(3) := 'LE64 50Q';

FOR student_no IN students.first..students.last LOOP
     FOR address_line_no IN students(student_no)Addresses('HOME')First ..students(student_no)Addresses('HOME')Last LOOP
          dbms_output.put_line(
'student('||student_no||') home address line('||address_line_no||') '|| students(student_no)Addresses('HOME')(address_line_no));
     END LOOP;
END LOOP;

END;
/
student(1) home address line(1) 1 The Avenue
student(1) home address line(2) Bristol
student(1) home address line(3) BS99 3SJ
student(2) home address line(1) 1 The Street
student(2) home address line(2) Leicester
student(2) home address line(3) LE64 50Q

Note how access to the innermost collection is achieved :
students(student_no)Addresses('HOME')(address_line_no)

The first collection is a set of records, the part of the record we are interested in, is in turn another collection each element of which is another collection. The (address_line_no)clause gives us the access to the base level collection but we can't access it by name because it doesn't have one, the type declaration is only a place holder in effect.

The next tutorial (part 14) looks at how to populate PL/SQL collections from Oracle and how to write them back to the database. The previous tutorial Oracle PL/SQL tutorial Part 12 covered PL/SQL associative arrays.

---------------------------------------

Take advantage of one of our classroom-based Oracle training courses to extend, enhance and consolidate your knowledge of PL/SQL. See the Oracle training page for details.

Looking for more Oracle tips and tricks ? For clear, concise, relevant technical information subscribe to our ezine.

Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320

Contact Us

Click here to view our privacy policy .

This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.