/*-----

    function: how_many: varchar2 -> integer
    purpose: expects a book's title, and returns
        that title's current quantity on hand

    assumption: that you have the tables from
        create-bks.sql

    example: IF create-bks.sql and pop-bks.sql have been
        run, then:
	 (in a PL/SQL subroutine):
	 how_many('The C Programming Language') = 10
-----*/

create or replace function how_many(p_title varchar2)
                  return integer as
    /* declarations */
    num_title_rows integer;
    num_on_hand    integer;
begin
    select count(*)
    into num_title_rows
    from title
    where title_name = p_title;

    if (num_title_rows = 0) then
        num_on_hand := 0;
    else
        select qty_on_hand
        into num_on_hand
        from title
        where title_name = p_title;
    end if;

    return num_on_hand;

exception
    /*   if I have a two or more books with the same title,
         what should I do? 
         this is probably a bad idea,
	 but I'm going to return the sum of
	 the quantity of all of those titles */
    
    when too_many_rows then
        select sum(qty_on_hand)
	into num_on_hand
        from title
	where title_name = p_title;

        return num_on_hand;
end;
/
show errors

/*
insert into title 
values
('0070790529', 234, 'SPSS', 'Palin', 
 123.95, 22.95, 5, 70, 10, 'F');
*/