/*====
    328lect05-1.sql 
        *   creates and tests PL/SQL stored function job_count,
            which also has a parameter

        *   creates and tests PL/SQL stored procedure job_overview,
            which has a parameter, calls PL/SQL stored function
	    job_count, and includes an IF statement

        *   and, this also includes how to call a PL/SQL stored
            function from SQL*Plus, including how to declare a
            SQL*Plus local variable to assign to what the function returns,
            and how to print that SQL*Plus variable's value

    by: Sharon Tuttle
    last modified: 2026-02-17
====*/

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

    purpose: expects a job title, and returns the number of
       employees with that job title
===*/

create or replace function job_count(desired_job_title varchar2)
   return integer is

   num_w_job integer;

begin
   select count(*)
   into   num_w_job
   from   empl
   where  job_title = desired_job_title;

   return num_w_job;
end;
/
show errors

/*=== TESTING PL/SQL function job_count ===*/

prompt =====
prompt TESTING job_count
prompt =====

/*=== SQL*Plus local variable ===*/

var num_job number

prompt =====
prompt SHOULD see that there is 1 President:
prompt =====

exec :num_job := job_count('President')

print num_job

prompt =====
prompt SHOULD see that there are 4 with Sales:
prompt =====

exec :num_job := job_count('Sales')

print num_job

prompt =====
prompt SHOULD see that there are 0 with Kitten Wrangler:
prompt =====

exec :num_job := job_count('Kitten Wrangler')

print num_job

/*===
    procedure: job_overview: varchar2 -> void
    purpose: expects a job title. returns nothing,
        and has the side-effect, IF serveroutput is set to on,
        of printing to the screen a report including the
        job title, how many employees have that job title,
        and their average salary.
        HOWEVER, if there are NO employees with that job title,
        this simply prints a message saying that.
===*/

create or replace procedure job_overview(desired_job_title varchar2) as
    num_w_job   integer;
    job_avg_sal number(7, 2);
begin
    /* VERSION 1

    select count(*), nvl(avg(salary), 0)
    into   num_w_job, job_avg_sal
    from   empl
    where  job_title = desired_job_title;


    dbms_output.put_line('Job Title:  ' || desired_job_title);
    dbms_output.put_line('# with Job: ' || num_w_job);
    dbms_output.put_line('Avg Salary: $' || job_avg_sal);

    === END of VERSION 1 */

    num_w_job := job_count(desired_job_title);

    if (num_w_job > 0) then
    
       select avg(salary)
       into job_avg_sal
       from empl
       where job_title = desired_job_title;

       dbms_output.put_line('Job Title:  ' || desired_job_title);
       dbms_output.put_line('# with Job: ' || num_w_job);
       dbms_output.put_line('Avg Salary: $' || job_avg_sal);
    else

        dbms_output.put_line('There are NO employees with job title of '
                             || desired_job_title);
    end if; 
end;
/
show errors

/*=== TESTING PL/SQL subroutine job_overview ===*/

set serveroutput on

prompt =====
prompt TESTING job_overview
prompt =====

prompt =====
prompt Should see President, 1, and $5000
prompt =====

exec job_overview('President')

prompt =====
prompt Should see Sales, 4, and $1400
prompt =====

exec job_overview('Sales')

prompt =====
prompt Should see a message saying there ARE no Kitten Wranglers
prompt =====

exec job_overview('Kitten Wrangler')