-- odetail-ex.sql
-- last modified: 1-30-13

-- insert_odetails: another exception-handling example

--------
-- signature: procedure: insert_odetails: 
--                         integer integer varchar2 integer -> void
-- purpose: expects the order number, line number, isbn, and
--     order quantity for an order_detail row to the inserted,
--     and returns nothing, but has the side-effect of inserting a new
--     order detail row only if the order quantity is > 0 and
--     its order number and line number aren't already there.
--     Print a complaint to the screen if the insertion cannot
--     be done.

create or replace procedure insert_odetails (onum integer, lnum integer,
                                   isbn varchar2,
                                   oqty integer) as
    invalid_quantity exception;
begin
    if (oqty < 1) then
        raise invalid_quantity;
    end if;

    insert into order_detail(ord_no, line_no, isbn, order_qty)
    values
    (onum, lnum, isbn, oqty);

exception
    when dup_val_on_index then
        dbms_output.put_line('PRIMARY KEY VIOLATION!');
        dbms_output.put_line('<' || sqlcode || '>' || '--' || sqlerrm);

    when invalid_quantity then
        dbms_output.put_line('quantity is invalid');
        dbms_output.put_line(sqlcode || '--' || sqlerrm);

    when others then
        dbms_output.put_line('other error:');
        dbms_output.put_line(sqlcode || '--' || sqlerrm);
end;
/
show errors

set serveroutput on

prompt *********************************
prompt TESTING insert_odetails
prompt *********************************

commit;

prompt current contents of order_detail:

select * 
from   order_detail;

prompt test 1: try to insert a row that is acceptable; should see 10 rows
prompt     in order_detail:

exec insert_odetails(11014, 2, '0805343024', 7)

select *
from order_detail;

prompt test 2: try to insert a row with a quantity of 0; should fail,
prompt    and order_detail should remain at 10 rows:

exec insert_odetails(11014, 3, '0201144719', 0)

select *
from   order_detail;

prompt test 3: try to insert a row with the same order number and
prompt    line number as an existing row (same primary key);
prompt    should fail, and order_detail should remain at 10 rows:

exec insert_odetails(11014, 1, '0201144719', 25)

prompt since testing is done, rolling back any changes made

rollback;

prompt order_detail should now be back to 9 rows:

select *
from   order_detail;

-- end of odetail-ex.sql