-- CS 328 - Homework 4 - Problems 6-8
-- Last modified 19 Febuary 2025
-- By Drew Hengehold

set serveroutput on

spool 328hw4-out.txt

prompt +-+-+-+-+-+-+-+-+-+
prompt By Drew Hengehold
prompt +-+-+-+-+-+-+-+-+-+

/*
    Procedure: num_pub_titles: publisher_name -> varchar2
    Purpose: For a publisher name given, num_pub_titles prints the publisher
    name as well as the number of titles that publisher carries.

*/

create or replace procedure num_pub_titles(publisher_name varchar2) as
    num_titles integer;
Begin

    select count(TTL_ISBN)
    INTO num_titles
    from title, publisher
    where title.pub_id = publisher.pub_id and publisher.pub_name = publisher_name;

    dbms_output.put_line('The name of the publisher is '|| publisher_name);
    dbms_output.put_line('The number of titles they carry is '|| num_titles);

End;
/
show errors 


prompt +-+-+-+-+-+-+-+-+-+
prompt ========Testing num_pub_titles========
prompt
prompt Should output the publisher name, McLeod
prompt as well at their title count, 0
prompt +-+-+-+-+-+-+-+-+-+

exec num_pub_titles('McLeod');

prompt +-+-+-+-+-+-+-+-+-+
prompt Should output the publisher name, Irwin
prompt as well at their title count, 1
prompt +-+-+-+-+-+-+-+-+-+

exec num_pub_titles('Irwin');

/*
    Procedure: print_test: message -> varchar2 and expression -> boolean
    Purpose: Print test recieves two arguemnts message and expression and 
    should print the message, then : True or : False depending on if the expression
    is true or false

*/

create or replace procedure print_test(message varchar2, expression boolean) as
Begin
    If expression then
        dbms_output.put_line(message || ': TRUE');
    else
        dbms_output.put_line(message || ': FALSE');
    end if;
end;
/
show errors


prompt +-+-+-+-+-+-+-+-+-+
prompt ========Testing print_test========
prompt
prompt Should output the message "This is true", with : True
prompt +-+-+-+-+-+-+-+-+-+

exec print_test('This is true', true);

prompt +-+-+-+-+-+-+-+-+-+
prompt Should output the message "This is false", with : False
prompt +-+-+-+-+-+-+-+-+-+

exec print_test('This is false', false);


/*
    procedure: title_info: ISBN -> integer
    purpose: takes an input ISBN and, if the ISBN is present in the table,
    outputs the Name, order point, auto order quantity, and quantity of that title.
*/

create or replace procedure title_info(ISBN integer) as
    select_quantity integer;
    isbn_name_match title.ttl_name%TYPE;
    order_point title.ttl_order_point%TYPE;
    auto_order title.ttl_auto_order_qty%TYPE;
    quantity title.ttl_qty%TYPE;

Begin

    select count(*)
    into select_quantity
    from title
    where ttl_isbn = ISBN;

    if select_quantity = 1 then

        select ttl_name, ttl_qty, ttl_order_point, ttl_auto_order_qty
        INTO isbn_name_match, quantity, order_point, auto_order
        from title
        where TTL_ISBN = ISBN;

        dbms_output.put_line('There was a match to your ISBN number ' || ISBN || '!');
        dbms_output.put_line('The title name is: ' || isbn_name_match);
        dbms_output.put_line('The quantity  is: ' || quantity);
        dbms_output.put_line('Order more when quantity is less than: ' || order_point);
        dbms_output.put_line('The amount to auto order is:' || auto_order);

    else
        dbms_output.put_line('There was no match to your ISBN number ' || ISBN);
    end if;

End;
/
show errors

prompt +-+-+-+-+-+-+-+-+-+
prompt ========Testing title_info========
prompt
prompt Should output "There is no match for that ISBN 123"
prompt +-+-+-+-+-+-+-+-+-+

exec title_info('123');

prompt +-+-+-+-+-+-+-+-+-+
prompt Should output "There was a match for that ISBN Number 9780070465329!", 
prompt then it should list the title name as SPSS, quantity at 75, order point as 70
prompt and the auto order quantity as 10
prompt +-+-+-+-+-+-+-+-+-+

exec title_info('9780070465329');


spool off