prompt
prompt ***************************************
prompt demo sell_book 
prompt ***************************************
prompt

prompt ===================
prompt TEST 1
prompt ===================
prompt

prompt there is an order_needed row for 025602796X:

select *
from   order_needed
where isbn = '025602796X';

prompt This call should succeed with a code of 0:

var results_code number;
exec :results_code := sell_book('025602796X', 5)
print results_code

prompt Are there now 5 copies of 025602796X?

select isbn, title_name, qty_on_hand, order_point, on_order
from   title
where  ISBN = '025602796X';

prompt there better be the same row (and just one) in order_needed:

select *
from   order_needed
where isbn = '025602796X';

prompt ===================
prompt TEST 2
prompt ===================
prompt

prompt This call should fail with a code of -1:

exec :results_code := sell_book('0130355488', 10)
print results_code

prompt ===================
prompt TEST 3
prompt ===================
prompt

prompt there should NOT be an order_needed row for '0805367829':

select *
from   order_needed
where isbn = '0805367829';

prompt This call should succeed with a code of 0:

exec :results_code := sell_book('0805367829', 11)
print results_code

prompt are there now 39 copies of 0805367829, with on_order still F?
prompt    (it needs an order, has not been ordered yet)

select isbn, title_name, qty_on_hand, order_point, on_order
from   title
where  ISBN = '0805367829';

prompt there SHOULD now be an order_needed row for 0805367829:

select *
from   order_needed
where isbn = '0805367829';

prompt ===================
prompt TEST 4
prompt ===================
prompt

prompt there should NOT be an order_needed row for '087150331X':

select *
from   order_needed
where isbn = '087150331X';

prompt This call should succeed with a code of 0:

exec :results_code := sell_book('087150331X', 1)
print results_code

prompt are there now 2 copies of 087150331X, with order_needed still F?
prompt    (it needs an order, has not been ordered yet)

select isbn, title_name, qty_on_hand, order_point, on_order
from   title
where  ISBN = '087150331X';

prompt there SHOULD now be an order_needed row for 087150331X:

select *
from   order_needed
where isbn = '087150331X';

prompt ===================
prompt TEST 5
prompt ===================
prompt

prompt This call should succeed with a code of 0:

exec :results_code := sell_book('087150331X', 1)
print results_code

prompt is there now 1 copy of 087150331X, with order_needed still F?

select isbn, title_name, qty_on_hand, order_point, on_order
from   title
where  ISBN = '087150331X';

prompt BUT there SHOULD still be ONLY ONE order_needed row for 087150331X:

select *
from   order_needed
where isbn = '087150331X';

prompt ===================
prompt TEST 6
prompt ===================
prompt

prompt this sale should fail with a code of -2:

exec :results_code := sell_book('0574214180', -5)
print results_code

prompt ===================
prompt TEST 7
prompt ===================
prompt

prompt there should NOT be an order_needed row for '0070790523':

select *
from   order_needed
where isbn = '0070790523';

prompt This call should succeed with a code of 0:

exec :results_code := sell_book('0070790523', 4)
print results_code

prompt are there now 71 copies of 0070790523, with order_needed still F?

select isbn, title_name, qty_on_hand, order_point, on_order
from   title
where  ISBN = '0070790523';

prompt there should NOT be an order_needed for this title:

select *
from   order_needed
where isbn = '0070790523';

prompt ===================
prompt TEST 8
prompt ===================
prompt

prompt this sale should fail with a code of -3:

exec :results_code := sell_book('0805367802', 21)
print results_code

prompt had better still have 20 of this title (sale not permitted
prompt    for more than on-hand...!)

select isbn, title_name, qty_on_hand, order_point, on_order
from   title
where  ISBN = '0805367802';

prompt there should NOT be an order_needed for this title:

select *
from   order_needed
where isbn = '0805367802';

-- required post-test_sell_book-calls' queries

prompt =======================================
prompt title after tests but before rollback:
prompt =======================================
prompt

select isbn, qty_on_hand, order_point, on_order
from title
order by isbn;

prompt ==============================================
prompt order_needed after tests but before rollback:
prompt ==============================================
prompt

select *
from order_needed
order by on_key;

rollback;