Juggling met 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<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 />
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 />
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 />
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 />
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 />
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 />
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.
SQL for the examples in this article: library_sql.txt.
- Returning a Ref Cursor based on a PL/SQL Collection
- On returning a collection of user defined objects from PL/SQL to Java – shortcut using a sys_refcursor
- Table FUNctions: select a graph in SQL
- Courtesy of Tom Kyte: Generating rows in SQL with the CUBE statement – no dummy table or table function required
- Dummy data without dummy tables – just a little SQL trick with the TABLE operator
- Steven Feuerstein Master Class. Anti-Pattern PL/SQL Programming + 12c New PL/SQL Features, December 12 and 13 2013
- The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
- On the integrity of data in Java applications – presentation from JFall 2013
- The road ahead for WebLogic 12c
- Enriching XMLType data using relational data – XQuery and fn:collection in action
- Java 8 – Collection enhancements leveraging Lambda Expressions – or: How Java emulates SQL
- You consolidated your applications in one database, but now one application needs recovery…
- Quick & Easy migrate VM from Oracle VM 2.x to 3.x
- Oracle Database SQL – Recursive Subquery to inspect events in football matches – find the MVP
- Oracle Database 12c: Find most valuable player using MATCH_RECOGNIZE in SQL