-- CS 328 Lab Week 5
-- Samuel Sparenga
-- Hunter Ciarabellini
-- Drew Hengehold

-- February 21, 2025

spool lab5-out.txt

set serveroutput on

prompt === Samuel Sparenga ===
prompt === Hunter Ciarabellini
prompt === Drew Hengehold ===

/*  
    === Function num_pd_more              ===
    === Takes : p_sal_limit -> number     ===
    === Purpose : Return number of empls  === */
create or replace function num_pd_more (p_sal_limit number) return integer  as
      empl_paid_higher integer;
   begin
      select count (*)
        into empl_paid_higher
	from empl
	where salary > p_sal_limit;

      return empl_paid_higher;
   end;
   /
   show errors

prompt === TEST for salary $5000 (0) ===
var over_5000 number;
exec:over_5000 := num_pd_more (5000)
print over_5000

prompt === TEST for salary $1500 (7) ===
var over_1500 number;
exec:over_1500 := num_pd_more (1500)
print over_1500

/*=====
    adapted by: Samuel Sparenga | HunterCiarabellini | 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
      select mgr
        into   manager_num
        from   empl
        where  empl_last_name = empl_name;

       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;


/*  
    === Procedure list_managers                 ===
    === Takes : p_jt -> void                    ===
    === Purpose : Prints overview of job title, listing empl name
     and manager === */
create or replace procedure list_managers (p_jt empl.job_title%type) as
   cursor jt_overview is (select empl_last_name
		            from empl
	    	            where job_title = p_jt);
   mgr_print empl.empl_last_name%type;
   empl_count integer;

   begin
     select count (*)
       into empl_count
       from empl
       where job_title = p_jt;

     if (empl_count > 0) then
        for empl_row in jt_overview loop
           mgr_print := get_manager (empl_row.empl_last_name);
           dbms_output.put_line (empl_row.empl_last_name || ' - managed by: ' || mgr_print);
        end loop;
    else
       dbms_output.put_line ('No available employees with that job titles');
    end if;

   end;

   /
   show errors

prompt === TEST for job title Coder (0) ===
exec list_managers ('Coder')

prompt === TEST for job title Clerk (4) ===
exec list_managers ('Clerk')

spool off

