-- auth-exists.sql
-- last modified: 1-30-13
--
-- uses table from authors.sql

set serveroutput on

--------
-- signature: function: author_exists1: varchar2 -> boolean
-- purpose: expects an author's name, and returns 
--          true if an author
--          with that name appears in the author table, 
--          and false otherwise
--
-- examples: if authors.sql has been run,
--     author_exists1('Peabody') = TRUE
--     author_exists2('Tuttle') = FALSE

create or replace function author_exists1(author_name varchar2) 
    return boolean as
        temp varchar2(10);
begin
    select name
    into temp 
    from author
    where name = author_name;

    if temp is not null then
        return TRUE;

    -- I don't THINK there can be another case here --
    --    but in case I'm wrong...

    else
        return FALSE;
    end if;

exception
    when no_data_found then
        return FALSE;
    when too_many_rows then
        return TRUE;
end;
/
show errors

--========
-- AHA!
-- this next example is being included as another OUT parameter example...
-- (I think the function is a better choice, though)
--========

--------
-- signature: procedure: author_exists2: varchar2 OUT:boolean -> void
-- purpose: expects an authors name (as an input parameter),
--     and an output parameter to hold the result of whether
--     that author's name appears in the author table or not
--     (true if it is, false if it isn't); returns nothing,
--     but does have the side-effect of setting that 2nd parameter)
--     
-- example: if authors.sql has been run, and looky is a boolean variable,
--          then after the call:
--              author_exists2('Peabody', looky);
--          looky = TRUE
--
--          and after the call:
--              author_exists2('Tuttle', looky);
--          looky = FALSE
--
-- last modified: 1-30-13
-- by: Sharon Tuttle

create or replace procedure author_exists2(author_name varchar2, 
                                           result OUT boolean) as
    temp varchar2(10);
begin
    select name
    into temp 
    from author
    where name = author_name;

    if temp is not null then
        result := TRUE;
    else
        result := FALSE;
    end if;

exception
    when no_data_found then
        result := FALSE;
    when too_many_rows then
        result := TRUE; 
end;
/
show errors

--========
-- There are alternatives to this helper-tester-procedure;
--     we could create local variables, etc. But I think this
--     helper-tester-procedure is actually simpler, in this case...
--========

--------
-- signature: procedure: call_author_exists: varchar2 -> void
-- purpose: expects an author's name, and uses the results of
--    author_exists1 and author_exists2 to print 2 messages to
--    the screen noting if an author with that name exists in the author
--    table
--
-- example: if authors.sql has been run, then
--     call_author_exists('Peabody');
--     ...should result in:
-- Peabody IS IN TABLE!
-- Peabody IS IN TABLE!
--
-- and:
--    call_author_exists('Tuttle');
--    ...should result in:
-- Tuttle is NOT in table!
-- Tuttle is NOT in table!
--
-- last modified: 1-30-13
-- by: Sharon Tuttle

create or replace procedure call_author_exists(author_name varchar2) as
    call_result boolean;
begin
    call_result := author_exists1(author_name);    

    if call_result = true then
        dbms_output.put_line(author_name || ' IS IN TABLE!');
    else
        dbms_output.put_line(author_name || ' is NOT in table!');
    end if;

    author_exists2(author_name, call_result);    

    if call_result = true then
        dbms_output.put_line(author_name || ' IS IN TABLE!');
    else
        dbms_output.put_line(author_name || ' is NOT in table!');
    end if;
end;
/
show errors

-- put in some testing!

prompt ******************************
prompt TESTING author_exists1 and author_exists2
prompt ******************************

prompt NOTE - inserting a second author Peabody for testing purposes

commit;

insert into author
values
(6, 'Peabody');

prompt there is ONE author George -- does this call work?

exec call_author_exists('George');

prompt there are NO authors Hill -- does this call work?

exec call_author_exists('Hill');

prompt there are TWO authors Peabody -- does this call work?

exec call_author_exists('Peabody');

prompt removing excess Peabody with author number 6

rollback;