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
/


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)
/

create sequence library_seq
/




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;
/


create or replace package body librarian
as


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;

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;

end librarian;
/



insert into authors
( id, name)
values
(1 , 'John Grisham')
/

insert into authors
( id, name)
values
(2 , 'Ben Elton')
/

insert into authors
( id, name)
values
(3 , 'Tom Wolfe')
/

insert into authors
( id, name)
values
(4 , 'Multatuli')
/

insert into books
(id, title, isbn, year_of_publication, atr_id)
values
( 1, 'A man in full', '8987655X65', 1998, 3)
/


insert into books
(id, title, isbn, year_of_publication, atr_id)
values
( 2, 'Max Havelaar', '645362512',1860, 4)
/


insert into books
(id, title, isbn, year_of_publication, atr_id)
values
( 3, 'Post Mortem', '73253267325X', 2004, 2)
/


insert into books
(id, title, isbn, year_of_publication, atr_id)
values
( 4, 'King of Torts', '7832762356',2003, 1)
/

insert into books
(id, title, isbn, year_of_publication, atr_id)
values
( 5, 'The Rainmaker', '7832673246' ,1995, 1)
/


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

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;

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