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

1

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<br />( title               varchar2(200)<br />, author              varchar2(100)<br />, isbn                varchar2(20)<br />, year_of_publication number(4)<br />)<br />/<br /><br />create type shelf is table of book<br />/<br />&nbsp;

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<br />as<br />procedure add_entire_shelf_to_library<br />( p_books in shelf);<br /><br />function get_books_by_author<br />( p_author in varchar2) <br />return shelf;<br /><br />end librarian;<br />/<br />&nbsp;

First operation -  get_books_by_author

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

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

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 *<br />from   table (librarian.get_books_by_author('j'))<br />/<br /><br /><br />TITLE<br />-----------------------------------------<br />AUTHOR<br />-----------------------------------------<br />ISBN                 YEAR_OF_PUBLICATION<br />-------------------- -------------------<br />King of Torts<br />John Grisham<br />7832762356                          2003<br /><br />The Rainmaker<br />John Grisham<br />7832673246                          1995<br />&nbsp;

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<br />( id number(10) not null primary key<br />, name varchar2(100)<br />)<br />/<br /><br />create table books<br />( id     number(10)     not null primary key<br />, title  varchar2(200)<br />, isbn varchar2(20)<br />, year_of_publication number(4)<br />, atr_id number(10)<br />)<br />/<br /><br />alter table books<br />add constraint lib_bok_atr_fk foreign key (atr_id) references authors (id)<br />/<br />&nbsp;

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<br />( p_books in shelf<br />) is<br />begin<br />  merge into books b<br />  using ( select a.id atr_id<br />          ,      book.*<br />          from   authors a<br />                 right outer join<br />                 table( p_books) book<br />                 on (a.name = book.author)<br />        ) newbook         <br />  on ( b.isbn = newbook.isbn<br />       or<br />       (b.title = newbook.title and b.atr_id = newbook.atr_id)<br />     )<br />  when matched<br />  then update set b.year_of_publication = newbook.year_of_publication<br />  when not matched<br />  then insert ( b.id, b.title, b.isbn, b.year_of_publication, b.atr_id)<br />       values ( library_seq.nextval<br />              , newbook.title, newbook.isbn, newbook.year_of_publication, newbook.atr_id<br />              )<br />  ;<br />end add_entire_shelf_to_library;<br />&nbsp;

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<br />  l_shelf shelf:= shelf();<br />begin<br />  l_shelf.extend;<br />  l_shelf(l_shelf.last):= book( 'A time to kill', 'John Grisham', '36351265XX', 1988);<br />  l_shelf.extend;<br />  l_shelf(l_shelf.last):= book( 'The firm', 'John Grisham', '3123123635', 1991);<br />  librarian.add_entire_shelf_to_library( l_shelf);<br />end;<br /><br />

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.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

1 Comment

  1. Chris Neumueller on

    Using the collect function should be slower than a simple “bulk collect into”. It’s nice that you wrote about a sql/plsql topic again, btw :-)