/*====
    function: add_dept_2: varchar2 varchar2 -> integer

    purpose: expects a new department's name and location,
       and if the department's name already exists, it does nothing
       and returns a code of -1;
       
       if the department's name is too long,
       it does nothing and returns a code of -2;

       if the department's location is too long,
       it does nothing and returns a code of -3;
       
       otherwise, it 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,
       and returns 1

       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 function add_dept_2(p_new_name dept.dept_name%TYPE, 
                                      p_new_loc dept.dept_loc%TYPE)
    return integer as

    max_dept_num     dept.dept_num%TYPE;
    num_with_name    integer;
    name_length      integer;
    loc_length       integer;

begin
    /*---
        if a department with this name already exists, do nothing
	and return -1
    ---*/

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

    if (num_with_name != 0) then
        return -1;
    end if;

    /*---
        if get here, make sure new department's name and location
	are not too long
    ---*/

    if (length(p_new_name) > 15) then
        return -2;
    end if;

    if (length(p_new_loc) > 15) then
        return -3;
    end if;

    /*--- if get here, OK to try to insert department ---*/

    /*---
        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;

    /*--- 
         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);

    /*--- if get here, SHOULD have successfully inserted ---*/

    return 1;
end;
/
show errors