/*====
    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: 2025-02-18
====*/

/*====
   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, then:

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

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

    /* like a PL/SQL stored procedure, can't have keyword DECLARE before a
       PL/SQL stored function's declaration part, either
    */

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

    return num_w_job;
end;
/
show errors

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

prompt
prompt ========
prompt testing job_count
prompt ========

/*==== sqlplus local variable to hold job_count's returned result ====*/
/*===
    (note: SQL*Plus local variables have fewer available types
    than those available in SQL, PL/SQL)

    ah, this is a bind variable?

    https://docs.oracle.com/cd/A81042_01/DOC/sqlplus.816/a75664/ch849.htm#1003221

    available types include (this is pulled from nrs-projects SQL*Plus error
    message when I tried a "bad" type):
    
    Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
		    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
		    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
		    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
====*/

var num_job number

/*====
    you can assign to a sqlplus local variable (bind variable)
    using the exec command

    (note the : before its name, and the := for assignment)
====*/

exec :num_job := -1

/*==== here's my first testing call ====*/

exec :num_job := job_count('President')

prompt should be 1:

/*====
    you can print the value of a SQL*Plus local variable (bind variable)
    using the print command
====*/

print num_job

/*==== here are my other two testing calls ====*/

exec :num_job := job_count('Sales')

prompt should be 4:

print num_job

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

prompt should be 0:

print num_job

/*====
    procedure: job_overview: varchar2 -> void
  
    purpose: expects a job title, and has the side effect of 
        printing to the screen (if serveroutput is on)
        a small report including that job_title,
        how many employees currently have that job title, 
        and their average salary

    uses: job_count 

    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(p_job_title
                                         empl.job_title%type) as
    num_w_job   integer;
    job_avg_sal number(7, 2);
begin
    /*====
        note how PL/SQL stored function job_count is called here
    ====*/

    num_w_job := job_count(p_job_title);

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

    dbms_output.put_line('Job Title : ' || p_job_title);
    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 should see: President, 1, $5000:
prompt ========

exec job_overview('President')

prompt should see: Sales, 4, $1400:
prompt ========

exec job_overview('Sales')

prompt should see: Kitten Wrangler, 0, and $0:
prompt ========

exec job_overview('Kitten Wrangler')