Juggling with SQL Types in SQL and PL/SQL – fading borders between Collection and Table

While preparing for a whole different topic, I had to create a few demonstrations of using custom SQL Types and Collections in both SQL and PL/SQL. While nothing spectacular – and for anyone who attended our 7Up class pretty familiar stuff – I thought an example of these small demos of how to create and use Type and Table of Type with for example the TABLE operator (to cast the Table of Type Collection into a relationally accessible construct) and the Merge operation as well as the Collect aggregator for producing quickly a Collection from the result of a simple SQL Query, might be useful. If for no one else, than for me – as I always tend to forget the exact syntax.

Note: all scripts are in a file you can download at the end of this article.

In this example, I work with two custom SQL Type definitions. One is
BOOK and the other is SHELF (table of BOOK). These types have been
defined as follows:

create or replace type BOOK as object
( title               varchar2(200)
, author              varchar2(100)
, isbn                varchar2(20)
, year_of_publication number(4)
)
/

create type shelf is table of book
/

A PL/SQL package – LIBRARIAN – is
available that provides services on our book library. Among the
operations are procedure add_entire_shelf_to_library( p_books in shelf)
and function get_books_by_author( p_author in varchar2) return shelf.

create or replace package librarian
as
procedure add_entire_shelf_to_library
( p_books in shelf);

function get_books_by_author
( p_author in varchar2) 
return shelf;

end librarian;
/

First operation –  get_books_by_author

The function is somewhat interesting because of the use of the COLLECT operator:

function get_books_by_author
( p_author in varchar2) 
return shelf
is
  l_shelf shelf;
begin
  select cast ( collect( book( b.title, a.name, b.isbn, b.year_of_publication)) as shelf)
  into   l_shelf
  from   books b
         left outer join
         authors a
         on (b.atr_id = a.id)
  where  lower(a.name) like lower(p_author)||'%'
  ;
  return l_shelf;         
end get_books_by_author;

this operator is an aggregator – on par with MAX, MIN, COUNT and AVG – that assembles BOOK objects into a collection that is then explicitly cast to the SHELF type, to make it fit into the l_shelf variable. With this function available, we can write simple SELECT statements such as:

select *
from   table (librarian.get_books_by_author('j'))
/


TITLE
-----------------------------------------
AUTHOR
-----------------------------------------
ISBN                 YEAR_OF_PUBLICATION
-------------------- -------------------
King of Torts
John Grisham
7832762356                          2003

The Rainmaker
John Grisham
7832673246                          1995

Of course if we have access to the underlying tables, we would probably prefer to write queries directly against those. However, more and more we will live in a world of data services and APIs, whether Views or PL/SQL APIs or even WebServices, and we might as well get used to it.

The underlying tables by the way are very simple and uninteresting:

create table authors
( id number(10) not null primary key
, name varchar2(100)
)
/

create table books
( id     number(10)     not null primary key
, title  varchar2(200)
, isbn varchar2(20)
, year_of_publication number(4)
, atr_id number(10)
)
/

alter table books
add constraint lib_bok_atr_fk foreign key (atr_id) references authors (id)
/

The second part of the Librarian API – procedure add_entire_shelf_to_library

The Librarian package offered a second operation: add_entire_shelf_to_library that takes a shelf object as input parameter. This operation will add all books on the shelf to the library tables – if the book does not already exist. If however a book on the shelf is already in the library, we can update its properties from the shelf.

The PL/SQL code that performs this feat is:

procedure add_entire_shelf_to_library
( p_books in shelf
) is
begin
  merge into books b
  using ( select a.id atr_id
          ,      book.*
          from   authors a
                 right outer join
                 table( p_books) book
                 on (a.name = book.author)
        ) newbook         
  on ( b.isbn = newbook.isbn
       or
       (b.title = newbook.title and b.atr_id = newbook.atr_id)
     )
  when matched
  then update set b.year_of_publication = newbook.year_of_publication
  when not matched
  then insert ( b.id, b.title, b.isbn, b.year_of_publication, b.atr_id)
       values ( library_seq.nextval
              , newbook.title, newbook.isbn, newbook.year_of_publication, newbook.atr_id
              )
  ;
end add_entire_shelf_to_library;

Using the TABLE operator we turn the SHELF collection into a relational source for the MERGE operation.

We can invoke this operation on the Librarian like this:

declare
  l_shelf shelf:= shelf();
begin
  l_shelf.extend;
  l_shelf(l_shelf.last):= book( 'A time to kill', 'John Grisham', '36351265XX', 1988);
  l_shelf.extend;
  l_shelf(l_shelf.last):= book( 'The firm', 'John Grisham', '3123123635', 1991);
  librarian.add_entire_shelf_to_library( l_shelf);
end;

One of the benefits of using TYPES and COLLECTIONS is our ability to transfer large, heterogenous sets of data (different types of records, such as master and detail records) in a single round trip. Using this ability can be especially useful in environments with mixed technologies like PL/SQL, Java, .Net.

Resources

SQL for the examples in this article: library_sql.txt.