/*====
    procedure: add_dept: varchar2 varchar2 -> void

    purpose: expects a new department's name and location,
       and has the side-effect of SLEAZILY determining a department
       number for the new department, and inserting a new department
       with that department number and the given name and location;
       IF the new department name already exists, it cheesily 
       adds a ' 2' and uses that as the inserted department name

       Assumes it MIGHT be used as a step in a larger transaction,
       and so does NOT commit this change here.

    by: Sharon Tuttle
    last modified: 2025-04-17
====*/

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

    max_dept_num     dept.dept_num%TYPE;
    num_with_name    integer;
    name_to_insert   dept.dept_name%TYPE;

begin
    /*---
        nvl is a function that returns its 1st argument if it
        is not NULL, and its 2nd argument otherwise
    ---*/

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

    /*---
        sleazily just adding ' 2' if this department name
        already exists
    ---*/

    select count(*)
    into num_with_name
    from dept
    where dept_name = p_new_name;

    if (num_with_name != 0) then
        name_to_insert := p_new_name || ' 2';
    else
        name_to_insert := p_new_name;
    end if;

    /*--- 
         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, name_to_insert, p_new_loc);
end;
/
show errors