PLSQL collections can be very useful for modeling more complex or sophisticated data types such as the array, table or set types that exist in other languages and Oracle PLSQL provides us with 3 types of collections:

associative arrays (also known as index-by tables)
VARRAYs (varying size arrays)
nested tables

We can also have multi dimensional plsql collections.

But what do we if we need collections of complex data types rather than the simple data types of varchar2, char, or number?

Fortunately, PL/SQL alllows us to define our own complex data types by using the record structure and to creating PLSQL collections of them. This enables us to build

very sophisticated data structures as we'll see shortly.

Let's start with a straightforward example of creating and using a PL/SQL record and a collection of records.

Example 1: Defining and Using a PL/SQL record structure

DECLARE

TYPE book_typ IS
RECORD
(
    isbn      VARCHAR2(20)
   ,title     VARCHAR2(2000)
   ,author    VARCHAR2(2000)
   ,publisher VARCHAR2(120)
   ,published_on DATE
);

TYPE books_tab IS TABLE OF book_typ;

books    books_tab;
a_book book_typ;

BEGIN

a_book.title    :=
'Oracle Database 10g Performance Tuning Tips and Techniques';
a_book.author       := 'Richard Niemiec';
a_book.publisher    := 'McGraw-Hill Osborne Media';
a_book.isbn         := '978-0072263053';
a_book.published_on :=
to_date('30 July 2007','dd mon yyyy');

books                 := books_tab(a_book);
END;

In this example we define our type first (as always). In this case our type has multiple components so we declare a record structure (book_typ) to hold them. We can now use the record in our declarations for plsql collections. In this case the collection type (books_tab) is a nested table type of the just defined record type. This enables us to have multiple records which, as we'll see shortly, allows us to read and write plsql collections from and to our Oracle database in one operation.

Having defined our types we need to declare variables of those types to be able to use them. We define one variable books which is a plsql collection and another variable a_book which is able to hold one instance of the record type book_typ declared earlier.

Accessing the components in the plsql record is straight forward as each component has a name so it's just the variable name followed by the component name.

For example, the author field is accessed by the following statement:

a_book.author       := 'Richard Niemiec';

Having populated our record we can then use this to initialise our nested table collection as each cell in the collection has the same structure as the record variable. To instantiate nested table type Oracle plsql collections we have to use the constructor routine which is the type name (see the tutorial on nested tables for more details). In this example we populate it with just 1 element (record). To add more we would use the extend method and then populate the new elements in a similar manner.

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber you're missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.

Example 2: Defining and Using a PL/SQL record structure in a multi-dimensional collection

DECLARE

TYPE author_typ IS TABLE OF VARCHAR2(120);

TYPE book_rec IS RECORD
(
    isbn         VARCHAR2(20)
   ,title        VARCHAR2(2000)
   ,author       author_typ
   ,publisher    VARCHAR2(120)
   ,published_on DATE
);

TYPE book_tab IS TABLE OF book_rec;

TYPE book_set_typ IS TABLE OF book_tab INDEX BY VARCHAR2(200);

book     book_rec;

book_set book_set_typ; --plsql multi-level collection variable

BEGIN

book.title        :=
'OCA: Oracle Database 11g Certfied Associate Study Guide';
book.author       :=
author_typ('Biju Thomas'
,'Robert G. Freeman'
,'Charles A. Pack'
,'Doug Sturns'
);
book.publisher    := 'Sybex';
book.isbn         := '978-0470395141';
book.published_on := TO_DATE('13 April 2009','dd mon yyyy');

book_set('OCP Oracle 11g Certifcation Kit') :=
   book_tab(book,book);
-- intialise 1st book_set with 2 books (the same)

book_set('OCP Oracle 11g Certifcation Kit')(2).title :=
    'OCP: Oracle 11g Certfied Professional Study Guide';
            -- correct title of 2nd book in the set

book_set('OCP 10g Certifcation Kit') := book_tab(book,book);
-- intialise 2nd book_set with 2 books (the same)

book_set('OCP 10g Certifcation Kit')(2).author(1) :=
'Tim Buterbaugh';

book_set('OCP 10g Certifcation Kit')(1).author :=
             author_typ('Tim Buterbaugh'
,'Chip Dawes'
,'Bob Bryla'
,'Doug Stuns');

book_set('OCP 10g Certifcation Kit')(2).title :=
    'OCP: Oracle 10g Certfied Professional Study Guide';
-- change title of 2nd book in the set
END;

In this example, as always, we define our types first. As in the previous example, our type has multiple components so we declare a record structure (book_rec) to hold them. However this time each book can have multiple authors by vrtue of defining the author field as a nested table collection (author_type). We could also have used a VARRAY collection if we wished to limit the maximum number of authors a book can have.

The next declaration (books_tab) is again an example of nested table Oracle plsql collections. In this case it is a collection of the just defined record type (book_rec). This would allow us to store details of multiple books. However in this example we take it a step further by declaring an associative array type collection which enables to store sets (collections) of books.

With the types (data structures) defined we can declare variables of those types so that we can use them. We define one variable book which holds one instance of the record type (book_rec) we declared earlier and the variable book_set to hold the details of our sets of books

The next step is to populate the fields (attributes) of the book (the PL/SQL record) and then use this to initialise our collection. The following statement

book_set('OCP Oracle 11g Certifcation Kit') :=
book_tab(book,book);

creates one element in our top level associative array and creates and initialises two elements in the nested table plsql collection. This has the effect of creating one set of two books, although both books are initially the same. Next we change the title of the second book in our first set and creating a second set of books (initially both the same).

The next statement:

book_set('OCP 10g Certifcation Kit')(2).author(1) :=
'Tim Buterbaugh';

changes the name of the first author of the 2nd book in our second set. Note that all the other author names remain unchanged.

The next statement uses the nested table constructor to re-initialise the (set of) authors' names of the first book in the second set.
book_set('OCP 10g Certifcation Kit')(1).author :=
             author_typ('Tim Buterbaugh'
,'Chip Dawes'
,'Bob Bryla'
,'Doug Stuns');

Finally we change the title of the second book in the second set of books.

book_set('OCP 10g Certifcation Kit')(2).title :=
    'OCP: Oracle 10g Certfied Professional Study Guide';

Example 3: Reading/Writing PL/SQL collections From/To Oracle

In this example we define a record type the components of which are anchored to the definitions of columns in the database by use of %TYPE, plus a collection of records defined impilicitly and anchored to the definition of the employees table in our Oracle database by use of %ROWTYPE.
DECLARE

   TYPE dept_rec IS RECORD
   (
     dept_id departments.department_id%TYPE
    ,dept_name departments.department_name%TYPE
   ); -- anchor definitions to reduce maintenance

   TYPE numtab IS TABLE OF employees%ROWTYPE;
-- each element is same type as row in employees table

   dept dept_rec; -- single record variable

   emps numtab; -- nested table pl/sql collection

BEGIN

/* retrieve name and id of just 1 department */

   SELECT department_id,department_name
   INTO dept.dept_id, dept.dept_name
   FROM departments
   WHERE ROWNUM < 2;

   /*
      use of bulk collect and forall with collections can
      significately improve performance of Oracle pl/sql code
   */

   SELECT * BULK COLLECT INTO emps FROM employees;
-- retrieve all employee details from Oracle in one hit

   FOR i IN emps.FIRST .. emps.LAST LOOP
      dbms_output.put_line(
'emps('||i||').employee_id = '||emps(i).employee_id
);
   END LOOP;

   FORALL J IN emps.FIRST .. emps.LAST
       INSERT INTO emp2 values emps(j);
           -- insert all employee details into emp2 in one hit

END;

The variable dept (which is a record type) is populated by reading columns of one row of the departments table and assigning them to the components of the record.

For PLSQL collections, Oracle provides the bulk collect option to populate the plsql collection using just one statement, however we can still access individual elements of our collection using the index. Writing the collection to the database can be done either one element at a time (using a loop) or all in one go by use of the FORALL statment.

Looking for Oracle PL/SQL training courses? Learn PL/SQL from the experts and sky-rocket your career with Oracle PL/SQL training in the UK from Smartsoft.

PLSQL Collections of Records

Example 1: Defining and Using a PL/SQL record structure

Oracle Tips & Tricks to SKYROCKET Your Career!

Example 2: Defining and Using a PL/SQL record structure in a multi-dimensional collection

Example 3: Reading/Writing PL/SQL collections From/To Oracle