/*===
    328lect05-2.sql
    *   creates and tests REFACTORED version of 328lect05-1.sql's
        PL/SQL stored procedure job_overview, having it call
        328lect05-1's PL/SQL stored function job_count
 
    *   creates and tests PL/SQL stored procedure loopy,
        an overwrought beast that demos three different kinds
        of PL/SQL loops and some exception-handling

    *   creates and tests PL/SQL stored procedure reverse_play,
        that prints the results of trying out several classic
        for loops, two with reverse and two without

    by: Sharon Tuttle
    last modified: 2024-02-15
===*/

/*===
   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

    uses: function job_count (from W5-1 examples)

    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?
    
    num_w_job := job_count(job_ttl);

    -- what is their salary?

    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 TESTING job_overview
prompt
prompt ============
prompt testing job_overview('President'):
prompt should see output including President, 1, and $5000:
prompt ============

exec job_overview('President')

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

exec job_overview('Sales')

prompt ============
prompt testing job_overview('Kitten Wrangler'):
prompt should see output including Kitten Wranger, 0, and $0:
prompt ============

exec job_overview('Kitten Wrangler')
/*=====
    procedure: loopy: integer -> void
    
    purpose: expects a desired quantity of burps,
       and has various odd side effects,
       such as: 
       *   printing the given quantity of BURPs 
           to the screen,
       *   printing a GLIP message with 13-16 after it,
       *   printing the names and locations of all current
           departments, 
       *   printing the names of any employees whose
           salary is 1000 times the given burp quantity,
       *   if no exception has been raised before this,
           printing a message giving an auto-declared i
           variable's value in a loop  
=====*/

create or replace procedure loopy(burp_quant integer) as
    counter     integer;
    chosen_empl empl.empl_last_name%type;
begin
    counter := 0;

    -- using a WHILE loop to print BURP! a number
    --    of times based on the burp_quant parameter

    while (counter < burp_quant) loop
        dbms_output.put_line('BURP! ' || counter);
        counter := counter + 1;
    end loop;

    -- demonstrating a "classic" FOR loop 

    dbms_output.put(chr(10));
    
    for counter in 13 .. 16 loop
        dbms_output.put_line('GLIP! ' || counter);

        -- uncomment the statement below to see you CANNOT
	--    change the loop control variable in a for loop
	--    within the loop:
	--
	-- counter := 20;
    end loop;

    -- demonstrating a cursor-controlled FOR loop

    dbms_output.put(chr(10)); 

    for next_dept in (select *
                      from     dept
                      order by dept_name) loop
		      
        dbms_output.put_line(next_dept.dept_name || ' is in '
	                     || next_dept.dept_loc);
    end loop;

    -- to demo exception handling, try to get the name
    --    of an empl whose salary is 1000 times the
    --    burp_quant parameter

    select empl_last_name
    into   chosen_empl
    from   empl
    where  salary = burp_quant * 1000;

    dbms_output.put(chr(10));

    dbms_output.put_line('Look! ' || chosen_empl || ' has salary '
        || ' that is 1000 times ' || burp_quant); 

    /*=== AFTER-CLASS EXPERIMENT
        *   I wanted to double-check that the loop-control
            variable for a "classic" for-loop would be
            automatically declared if not already declared
            as a local variable (as we saw above for a
            cursor-controlled for loop)

        *   loop below verifies this

        *   then I wondered: is PL/SQL like C++, in which
            a variable declared (or, for PL/SQL, auto-declared)
            in a for-loop's first line has as its scope JUST 
            that for-loop's body?
            
            COMMENTED-OUT put_line below verifies that this
            IS also the case in PL/SQL -- uncomment it to
            see the I-is-undeclared error message!
    ====*/

    dbms_output.put(chr(10));
    
    for i in 100 .. 103 loop
        dbms_output.put_line('i has value: ' || i);
    end loop;

    -- uncomment to see that auto-declared i's scope is
    --     JUST the for loop's body
    --
    --dbms_output.put_line('is i in scope after loop? ' || i);

exception
    -- no_data_found raised when try to project an attribute
    --    value into a local variable but no rows are selected

    when no_data_found then

        dbms_output.put(chr(10));
        dbms_output.put_line('No employee has salary ' ||
	    (burp_quant * 1000));

    -- too_many_rows raised when try to project an attribute
    --    value into a single-valued-type local variable
    --    but more than one row is selected

    when too_many_rows then
        dbms_output.put(chr(10));

        -- my choice for this exception: print the message for
	--    each such employee

        for next_empl in (select empl_last_name
	                  from empl
			  where salary = burp_quant * 1000)
	loop
            dbms_output.put_line('Look! ' || next_empl.empl_last_name
	        || ' has salary that is 1000 times ' || burp_quant);
	end loop;
end;
/
show errors

/*=== TESTING loopy ===*/

prompt ============
prompt TESTING loopy
prompt
prompt ============
prompt testing loopy(5):
prompt should see:
prompt ======
prompt BURP! followed by 0-4,
prompt GLIP! followed by 13-16,
prompt all department names and locations,
prompt a message that King has a salary 1000 times 5,
prompt then i has value: followed by 100-103
prompt ============

exec loopy(5)

prompt ============
prompt testing loopy(2):
prompt should see:
prompt ======
prompt BURP! followed by 0-1,
prompt GLIP! followed by 13-16,
prompt all department names and locations,
prompt a message that no employee has a salary 1000 times 2
prompt (i loop is never reached because exception raised)
prompt ============

exec loopy(2)

prompt ============
prompt testing loopy(1.25):
prompt should see:
prompt ======
prompt BURP! followed by 0-1,
prompt GLIP! followed by 13-16,
prompt all department names and locations,
prompt messages that Ward and Martin have a salary 1000 times 1.25
prompt (i loop is never reached because exception raised)
prompt ============

exec loopy(1.25)
/*=====
    procedure: reverse_play: void -> void
    purpose: expects nothing, and prints to the screen
        the results from running 4 for-loops,
        two with reverse, two without, and flipping
        the order of the start and end values

    example:
        when run, this SHOULD print:
i: 2
i: 3
i: 4
i: 5

j: 5
j: 4
j: 3
j: 2

====*/

create or replace procedure reverse_play as
begin
    -- example 1: typical classic for-loop in PL/SQL

    for i in 2 .. 5 loop
        dbms_output.put_line('i: ' || i);
    end loop;

    -- example 2: REVERSE with ascending start and end values

    dbms_output.put(chr(10));

    for j in REVERSE 2 .. 5 loop
        dbms_output.put_line('j: ' || j);
    end loop;

    -- example 3: REVERSE with descending start and end values
    --     (turns out, this simply never enters the loop,
    --     because 2 is not greater than 5?)

    dbms_output.put(chr(10));

    for k in REVERSE 5 .. 2 loop
        dbms_output.put_line('k: ' || k);
    end loop;

    -- example 4: what if NO reverse, and descending start and end
    --     values?
    --     (turns out, this simply never enters the loop,
    --     because 2 is not greater than 5?)    

    dbms_output.put(chr(10));

    for m in 5 .. 2 loop
        dbms_output.put_line('m: ' || m);
    end loop;
end;
/
show errors

/*=== TESTING reverse_play ===*/

prompt ============
prompt TESTING reverse_play
prompt
prompt ============
prompt testing reverse_play():
prompt should see i from 2 to 5,
prompt            j from 5 to 2,
prompt            and that is all:
prompt ============

exec reverse_play()