-- how-many.sql
-- last modified: 1-30-13
--
-- uses a table from create-bks.sql and pop-bks.sql

--------
-- signature: function: how_many: varchar2 -> integer
-- purpose: expects a book's title, and returns that 
--          title's current quantity on hand
--
-- example: assuming create-bks.sql and pop-bks.sql have been
--          run,
--          how_many('The C Programming Language') = 10
--
-- by: Sharon Tuttle
-- last modified: 1-30-13

create or replace function how_many(p_title varchar2) 
                           return integer is
    num_title_rows integer;
    num_on_hand    integer;
begin
    -- nvl is a function that returns the value
    --     of its first argument IF it is non-null;
    --     otherwise, it returns the value of its 2nd
    --     argument

    select nvl(count(*), 0)
    into num_title_rows
    from title
    where title_name = p_title;

    if (num_title_rows = 0) then
        num_on_hand := 0;   -- if title doesn't exist, 
                            --    then can't have
                            --    any copies of it...!
    else
        select qty_on_hand
        into num_on_hand
        from title
        where title_name = p_title;
    end if;

    return num_on_hand;
end;
/
show errors

set serveroutput on
 
prompt
prompt TESTING how_many: should return 10 (there are
prompt     10 copies of The C Programming Language book)
prompt ===========================================================

var num_c number;
exec :num_c := how_many('The C Programming Language')
print num_c

-- end of how-many.sql