
/*=====
    adapted by: Drew Hengehold
    last modified: 2025-02-21

    function: get_manager: varchar2 -> varchar2

    purpose: expects an employee's last name, and tries to
        return the last name of that employee's manager.
        *   IF there is no employee with that name, it returns
            'Not an employee';
        *   IF there is more than one employee with that last name,
            it returns 'Name not unique'
        *   IF the employee has no manager, it returns 'No manager'

    examples:
        if set-up-ex-tbls.sql has been run,
        and another employee named Jones inserted:
   
        get_manager('Ward') = 'Blake'
        get_manager('Raimi') = 'King'
        get_manager('King') = 'No manager'
        get_manager('Wilbon') = 'Not an employee'
        get_manager('Jones') = 'Name not unique'
=====*/

create or replace function get_manager(empl_name varchar2) 
    return varchar2 as

    manager_num  empl.empl_num%type;
    manager_name empl.empl_last_name%type;

begin
    -- try to ask for name of manager of an employee with this name

    select mgr
    into   manager_num
    from   empl
    where  empl_last_name = empl_name;

    -- now determine the name (or desired alternate response)
    --    for this manager

    if (manager_num is null) then
        manager_name := 'No manager';
    else
        select empl_last_name
        into   manager_name
        from   empl
        where  empl_num = manager_num;
    end if;

    return manager_name;

exception
    when NO_DATA_FOUND then
    manager_name := 'Not an employee';
    return manager_name;

    when TOO_MANY_ROWS then
    manager_name := 'Name not unique';
    return manager_name;



end;
/
show errors

/*===== TESTING get_manager =====*/

prompt
prompt ============
prompt TESTING get_manager
prompt

/* save the state of the database BEFORE these tests */

commit; 

/*=====
   inserting another employee who happens to have the
       same name as an existing employee
=====*/

insert into empl
values
('8001', 'Jones', 'Salesman', '7698', sysdate, 1000, 0, '300');

prompt ============
prompt testing show_manager: should see Blake (as manager of Ward)

var manager_string varchar2(20)
exec :manager_string := get_manager('Ward')
print manager_string

prompt ============
prompt testing show_manager: should see King (as manager of Raimi)

exec :manager_string := ''
exec :manager_string := get_manager('Raimi')
print manager_string

prompt ============
prompt testing show_manager: should see No manager (for King)

exec :manager_string := ''
exec :manager_string := get_manager('King')
print manager_string

prompt ============
prompt testing show_manager: should see Not an employee (for Wilbon)

exec :manager_string := ''
exec :manager_string := get_manager('Wilbon')
print manager_string

prompt ============
prompt testing show_manager: should see Name not unique (for Jones)

exec :manager_string := ''
exec :manager_string := get_manager('Jones')
print manager_string

/* restore the database state after these tests */

rollback;

