Oracle PL/SQL Tutorial Part 13 - Multi-level Collections

This PL/SQL tutorial continues the introduction to the use of collections by showing how we can build multi-dimensional arrays in PL/SQL.

Oracle PL/SQL Collection Types

To recap briefly, we've seen that there are 3 different types of collections: • nested tables - covered in part 10 of this tutorial • varrays (varying arrays) - covered in part 11  • associative arrays - covered in part 12

Multi-level PL/SQL Collections

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 we want to have tables that are really nested as we can have in the database? Before Oracle9i we couldn't do it, but in 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 :           o addresses (home/holiday, term-time)           o phone numbers (home, term-time, mobile)           o email addresses (home, college, other)           o first and last name           o etc We’ll keep things simple for this example though and restrict ourselves to just storing 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 tables, so we have our first data structure a table (collection in PL/SQL terms) of student details. The attributes would normally translate to columns in the table but 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 create a 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 address data structure - each addresses is a tables of address lines. 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 PLS_INTEGER;     TYPE address_tab IS TABLE OF address_lines     INDEX BY VARCHAR2(10); Note that we are indexing address_lines by PLS_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 PL/SQL table of addresses with each address consisting 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 PLS_INTEGER; Note that this time we're indexing by PLS_INTEGER, which could be the numeric student id, as we're already storing the first and last names. Also note that we can’t store PLS_INTEGERs in the database - they are purely a PL/SQL type. The next tutorial covers populating collections from the database. 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! 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 PLS_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 PLS_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 := 'Bill';           students(2).Last_name := 'Smith';           students(2).Addresses('TERM')(2) := 'Bristol’;           students(2).Addresses('TERM')(3) := 'BS7 8EW';           students(2).Addresses('TERM')(1) := '1 Shadwell Road”;           students(2).Addresses('HOME')(2) := 'Leicester';           FOR student_no IN students.first..students.last LOOP                dbms_output.put_line('Student('||student_no||'): '|| students(student_no).first_name||' '|| students(student_no).last_name);                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): Mike Johnson 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): Mike Smith student(2) home address line(2) Leicester Note how access to the inner most collection is achieved : students(student_no).Addresses('HOME')(address_line_no) Each element of the student collection is a record. The part of the record we are interested in, addresses, is another collection, each element of which is also a collection - address_lines. The (address_line_no) expression - which evaluates to a PLS_INTEGER - gives us the access to the innermost data structure (collection) but we can't access the collection by name because it doesn't have one - the type declaration is merely 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 associative arrays. PL/SQL tutorial Part 11- varrays                                                           PL/SQL tutorial Part 14  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor- led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Looking for more Oracle tips and tricks? For no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our newsletter. Jam-packed full of tips and tricks, it will help you make your Oracle systems faster and more reliable and save you hours searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox. Smartsoft Computing Ltd, Bristol, England Tel: 0845 003 1320 Need help with Oracle? Contact Us View our privacy policy This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd 2001-2015. All rights reserved.