/*----

    function: how_many: varchar2 -> integer
    purpose: expects a book's title and it returns
             that title's current quantity-on-hand
             (if there is more than one such title,
	           it returns -1)

    assumption: assumes create-bks.sql has been run

    example: if both create-bks.sql and pop-bks.sql
        have been run, then (within 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;

    -- my client has decided that if the title does 
    --    not exist, then none are on hand, either,
    --    and so return 0

    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
    when too_many_rows then
        /* what if multiple works in my store have
           the same title? the client wants a -1
           returned in this case
        */
        
        return -1;       
end;
/
show errors