/*-----
    THERE BE SOME CLUNKINESS HERE!!!

    procedure: new_dept: dept.dept_name%TYPE dept.dept_loc%TYPE -> void
    purpose: expects a new department's name and location, and returns
             nothing, but has the side-effect of
             inserting a new row into the dept table with a unique
             primary key (10 more than the max currently there)
             and this name and location
             (TOO trusting - doesn't look to see if there already!!)

             ...doesn't violate entity integrity - new row gets a
	           unique primary key - BUT clunky nevertheless!

    ALSO: plays uncomfortably fast-and-loose with char vs number;
    adding 10 to a char(3) that happens to contain digits...!

    example: if one runs the following right after set-up-ex-tbls.sql
             has been run,
	                     new_dept('Computer', 'Arcata');
             ...will return nothing, BUT dept will now also contain a
             row with:
	                '510', 'Computer', 'Arcata'

             and if you again ran:
	           new_dept('Computer', 'Arcata');

             now you'd ALSO have a row:
                  '520', 'Computer', 'Arcata'

-----*/

create or replace procedure new_dept(p_new_name dept.dept_name%TYPE, 
                                     p_new_loc dept.dept_loc%TYPE) as

    max_dept_num   dept.dept_num%TYPE;

begin
    select nvl(max(dept_num), '10')
    into max_dept_num
    from dept;

    /*----- 
         playing fast-and-loose with char-vs-number here,
         adding 10 to a char(3) containing digits...!
    -----*/

    insert into dept
    values
    (max_dept_num + 10, p_new_name, p_new_loc);
end;
/
show errors