

start /home/st10/set-up-ex-tbls.sql

set serveroutput on

spool lab5-out.txt


prompt +-+-+-+-+-+-+-+-+-+-+-;
prompt Drew Henegehold
prompt +-+-+-+-+-+-+-+-+-+-+-


create or replace Function num_pd_more(lower_limit number) return number as
    empl_above_sal number;
Begin   
    select count(*)
    INTO empl_above_sal
    from empl
    where salary > lower_limit;

    return empl_above_sal;
end;
/
show errors

prompt ========Testing print_test========
prompt +-+-+-+-+-+-+-+-+-+
prompt With input 4999, empls_with_salary should output 1
prompt +-+-+-+-+-+-+-+-+-+

var empls_with_salary number
exec :empls_with_salary := num_pd_more(4999)

print empls_with_salary


prompt +-+-+-+-+-+-+-+-+-+
prompt With input 1000, empls_with_salary should output 12
prompt +-+-+-+-+-+-+-+-+-+

var empls_with_salary number
exec :empls_with_salary := num_pd_more(1000)

print empls_with_salary


create or replace procedure list_managers(job_name empl.job_title%TYPE) as
    manager_name empl.empl_last_name%TYPE;
    cursor name_list is (select empl_last_name 
                        from empl
                        where job_title = job_name);

Begin        
    select count(*)
    from empl
    where job_title = job_name

    for empl_row in name_list loop
        if  then
            dbms_output.put_line('There are no employees with job title' || job_name);
        end if;
        manager_name := get_manager(empl_row.empl_last_name);
        dbms_output.put_line(empl_row.empl_last_name || 'managed by: ' ||
            manager_name);
    end loop;

end;
/
show errors


prompt ========Testing print_test========
prompt +-+-+-+-+-+-+-+-+-+
prompt With input job title Manager, list_managers output prompt With input job title Manager, list_managers output (Jonesmanaged by: King) (Blakemanaged by: King) (Raimimanaged by: King)

prompt +-+-+-+-+-+-+-+-+-+

exec list_managers('Manager')

prompt +-+-+-+-+-+-+-+-+-+
prompt With input job title Dreamer, list_managers output There are no employees with job title Dreamer
prompt +-+-+-+-+-+-+-+-+-+

exec list_managers('Dreamer')