-- Drew Hengehold
-- 24 Febuary 2025
--  CS 328 - Homework 5

Set serveroutput on

spool 328hw5-out.txt

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

/*
 Procedure: silly_shout
 Parameters: desired_message varchar2 and times_shouted number
 Purpose: If the number of times to shout given is less than 0, the procedure should print a message to the screen 
 saying that it cannot show the desired message that many times (and include both the desired message and the 
 "bad" number of times in that message). Otherwise, it should print an all-uppercase version of the given 
 message to the screen that many times, once per line, each time concatenating TWO exclamation point characters ('!!') 
 to the end
*/

create or replace procedure silly_shout(desired_message varchar2, times_shouted number) as

Begin
    if times_shouted < 0 then
        dbms_output.put_line('Cannot show ' || desired_message || ' ' || times_shouted || ' times');
    else
        for i in 1 .. times_shouted loop
            dbms_output.put_line(upper(desired_message) || '!!' );
        end loop;
    end if;

end;
/
show errors

prompt ========Testing silly_shout========
prompt
prompt +-+-+-+-+-+-+-+-+-+
prompt (Test 1 silly_shout) Should output: HOWDY!! 5 times
prompt +-+-+-+-+-+-+-+-+-+

exec silly_shout('howdy', 5)


prompt +-+-+-+-+-+-+-+-+-+
prompt (Test 2 silly_shout) Should output: WHY!! 2 times
prompt +-+-+-+-+-+-+-+-+-+

exec silly_shout('why', 2)

prompt +-+-+-+-+-+-+-+-+-+
prompt (Test 3 silly_shout) Should output: Nothing
prompt +-+-+-+-+-+-+-+-+-+

exec silly_shout('why', 0)

prompt +-+-+-+-+-+-+-+-+-+
prompt (Test 4 silly_shout) Should output: Cannot show me -2 times
prompt +-+-+-+-+-+-+-+-+-+

exec silly_shout('me', -2)


/*
 Function: title_total_cost
 Parameters: ISBN number
 Purpose: expects a title's ISBN, returns the total COST of all of the current quantity for that title
 example:
        title_total_cost('9780871507877')= 1137.5
*/

create or replace function title_total_cost(ISBN integer) return number as
    quantity number;
    cost number;
begin 
    select ttl_cost, ttl_qty
    INTO cost, quantity
    from title
    where ttl_isbn = isbn;

    return quantity * cost;

exception 
    when no_data_found  then
        return -1;
end;
/
show errors

prompt ========Testing========
prompt
prompt +-+-+-+-+-+-+-+-+-+
prompt title_total_cost(9780201106862) should return 97.5
prompt +-+-+-+-+-+-+-+-+-+

var total_cost number
exec :total_cost :=title_total_cost(9780201106862)
print total_cost


prompt +-+-+-+-+-+-+-+-+-+
prompt title_total_cost(9780871507877) should return 1137.5
prompt +-+-+-+-+-+-+-+-+-+

exec :total_cost :=title_total_cost(9780871507877)
print total_cost

prompt +-+-+-+-+-+-+-+-+-+
prompt title_total_cost(0780471507877) should return -1
prompt +-+-+-+-+-+-+-+-+-+
exec :total_cost :=title_total_cost(0780471507877)
print total_cost



/*
 Procedure: titles_in_range
 Parameters: high_price, low_price
 Purpose: prints to the screen, for all titles whose title price are greater than or equal to the desired low end given,
  and less than or equal to the desired high end given in $title_price - title_name: title_quantity form, ordered by price then name
ex:
    exec titles_in_range(30, 40):
        $31.5 - Financial Accounting: 10
        $31.5 - Financial Accounting: 10
        $34.95 - Computers and Data Processing: 15
        $34.95 - Computers and Data Processing: 15
        $35.95 - Operating Systems: A Systematic View: 5
        $35.95 - Operating Systems: A Systematic View: 5
        $37.95 - An Introduction to Database Systems: 10
        $37.95 - An Introduction to Database Systems: 10
        $37.95 - Data Base Management: 20
        $37.95 - Data Base Management: 20
        $37.95 - Problem Solving and Structured Programming: 12
        $37.95 - Problem Solving and Structured Programming: 12
        $39.95 - The C Programming Language: 10
        $39.95 - The C Programming Language: 10
        $40 - Software Engineering: 10
*/

create or replace procedure titles_in_range(low_price number, high_price number) as
    cursor title_print is select *
                           from title
                           where ttl_price >= low_price and ttl_price <= high_price
                           order by ttl_price, ttl_name;

begin
    for title in title_print loop
        dbms_output.put_line('$' || title.ttl_price || ' - ' || title.ttl_name || ': ' || title.ttl_qty);
    end loop;
end;
/
show errors 

prompt ========Testing========
prompt
prompt +-+-+-+-+-+-+-+-+-+
prompt titles_in_range(30, 40) should output:
prompt $31.5 - Financial Accounting: 10
prompt $34.95 - Computers and Data Processing: 15
prompt $35.95 - Operating Systems: A Systematic View: 5
prompt $37.95 - An Introduction to Database Systems: 10
prompt $37.95 - Data Base Management: 20
prompt $37.95 - Problem Solving and Structured Programming: 12
prompt $39.95 - The C Programming Language: 10
prompt $40 - Software Engineering: 10
prompt +-+-+-+-+-+-+-+-+-+

exec titles_in_range(30, 40)


prompt +-+-+-+-+-+-+-+-+-+
prompt titles_in_range(1, 3) should output: Nothing, no titles within this zone

prompt +-+-+-+-+-+-+-+-+-+
exec titles_in_range(1,3)

prompt +-+-+-+-+-+-+-+-+-+
prompt titles_in_range(1, 3) should output:
prompt  $28.95 - Management Information Sytems: 30
prompt  $28.95 - SPSS: 75
prompt  $29.95 - BASIC: A Structured Approach: 5
prompt +-+-+-+-+-+-+-+-+-+
exec titles_in_range(28, 30)


/*
 Function: get_pub
 Parameters: ISBN integer
 Purpose: returns the name of the publisher for the title with that ISBN

*/

create or replace function get_pub(ISBN number) return publisher.pub_name%TYPE as
    publisher_name publisher.pub_name%TYPE;
Begin 
    select pub_name 
    into publisher_name
    from publisher, title
    where title.pub_id = publisher.pub_id and title.ttl_isbn = ISBN;
    
    return publisher_name;

exception 
    when no_data_found then
    publisher_name := 'ISBN not found';
    return publisher_name;
    
end;
/
show errors 

prompt ========Testing========
prompt
prompt +-+-+-+-+-+-+-+-+-+
prompt get_pub('9780262534802') should return: 'The MIT Press'
prompt +-+-+-+-+-+-+-+-+-+

var name varchar2(30)
exec :name := get_pub(9780262534802)
print name

prompt ========Testing========
prompt
prompt +-+-+-+-+-+-+-+-+-+
prompt get_pub('9780073401324') should return: 'McGraw-Hill'
prompt +-+-+-+-+-+-+-+-+-+

var name varchar2(30)
exec :name := get_pub(9780073401324)
print name

prompt ========Testing========
prompt
prompt +-+-+-+-+-+-+-+-+-+
prompt get_pub('9383356033235') should return: 'ISBN not found'
prompt +-+-+-+-+-+-+-+-+-+

var name varchar2(30)
exec :name := get_pub(9383356033235)
print name


spool off