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