/*===
    328lect05-1.sql 
    *   creates and tests PL/SQL stored procedure job_overview,
        which has a parameter and includes an IF statement

    *   creates and tests PL/SQL stored function job_count,
        which also has a parameter

        *   so, 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: 2024-02-14
===*/

/*===
   procedure: job_overview: varchar2 -> void

   purpose: expects a job title, has the side-effect
      (if serveroutput is on) of printing to the screen
      a report including that job title, how many employees
      have that job title, and their average salary

    examples: 
        if set-up-ex-tbls.sql has been run, and serveroutput is on,:

        exec job_overview('President')
        ...should print to the screen:
Job Title : President
# with Job: 1
Avg Salary: $5000

        exec job_overview('Sales')
        ...should print to the screen:
Job Title : Sales
# with Job: 4
Avg Salary: $1400

        exec job_overview('Kitten Wrangler')
        ...should print to the screen:
Job Title : Kitten Wrangler
# with Job: 0
Avg Salary: $0

===*/

create or replace procedure job_overview(job_ttl varchar2) is
    num_w_job integer;
    job_avg_sal number(7, 2);
begin
    -- how many have this job title, and what is their
    --     salary

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

    if (num_w_job > 0) then
    
        select avg(salary)  
        into job_avg_sal
        from empl
        where job_title = job_ttl;
	
    else
        job_avg_sal := 0;
    end if;

    dbms_output.put_line('Job Title : ' || job_ttl);
    dbms_output.put_line('# with Job: ' || num_w_job);
    dbms_output.put_line('Avg Salary: $' || job_avg_sal);
end;
/
show errors

set serveroutput on

/*=== TESTING job_overview ===*/

prompt
prompt ============
prompt TESTING job_overview
prompt
prompt ============
prompt testing job_overview:
prompt should see output including President, 1, and $5000:
prompt ============

exec job_overview('President')

prompt ============
prompt testing job_overview:
prompt should see output including Sales, 4, and $1400:
prompt ============

exec job_overview('Sales')

prompt ============
prompt testing job_overview:
prompt should see output including Kitten Wranger, 0, and $0:
prompt ============

exec job_overview('Kitten Wrangler')

/*====
    function: job_count: varchar2 -> integer
   
    purpose: expects a job title, and returns the number of
        employees with that job title

    examples:
        if set-up-ex-tbls.sql has been run,

	job_count('President') = 1
        job_count('Sales') = 4
        job_count('Kitten Wrangler') = 0
===*/

create or replace function job_count(desired_job varchar2) return integer is
    num_w_job integer;
begin
    select count(*)
    into num_w_job
    from empl
    where job_title = desired_job;

    return num_w_job;
end;
/
show errors 

/*==== TESTING job_count ====*/

/*=== 
    neeeeeed a SQL*PLus level local variable to hold
        what the function call returns
===*/

var num_job number

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

/*=== here's how you can then call a stored function ===*/

exec :num_job := job_count('President')

/*=== here's now you can print the value of a SQL*Plus variable ===*/

prompt ============
prompt testing job_count: should see 1

print num_job

exec :num_job := job_count('Sales')

prompt ============
prompt testing job_count: should see 4

print num_job

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

prompt ============
prompt testing job_count: should see 0

print num_job