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.
SQL for the examples in this article: library_sql.txt.