/*===
    328lect05-2.sql
    *   creates and tests PL/SQL stored procedure loopy,
        an overwrought beast that demos three different kinds
        of PL/SQL loops and some exception-handling

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

/*=====
    procedure: loopy: integer -> void

    purpose: expects an upper-limit on burps, and returns
        nothing, but has the side-effect of printing to the
        screen (if set serveroutput is on and you have
        run set-up-ex-tbls.sql):

        *   that many BURP messages
        *   GLIP messages from 13 to 16
        *   names and locations of current departments
        *   employee numbers and last names of Sales employees
        *   whether it has an employee whose salary is 1000 times
            that given upper-limit on burps
=====*/

create or replace procedure loopy(burp_quant integer) as

    counter integer;

    /*=== example of declaring a cursor ===*/
    
    CURSOR sales_curs IS select empl_num, empl_last_name
      	      	      	 from   empl
			 where  job_title = 'Sales';

    chosen_empl empl.empl_last_name%type;
begin
    counter := 0;

    /*=== demo a while loop ===*/

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

    /*=== demo of a count-controlled FOR loop ===*/
    
    dbms_output.put(chr(10)); -- kluge to get a blank line

    for i in 13 .. 16
    loop
        dbms_output.put_line('GLIP! ' || i);

        -- uncomment to demo that you cannot change
	-- a for loop's loop control variable IN the loop
	
	-- i := 20;
    end loop;

    /*=== first demo of a cursor-controlled FOR loop ===*/

    dbms_output.put(chr(10)); -- kluge to get a blank line

    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;

    /*=== second demo of a cursor-controlled FOR loop ===*/

    dbms_output.put(chr(10)); -- kluge to get a blank line
    
    for next_sales in sales_curs
    loop
        dbms_output.put_line(next_sales.empl_num || ': '
	                     || next_sales.empl_last_name);
    end loop;

    /*=== 
        to demo exception-handling:

        are there any employees whose salary is
            1000 * the burp quantity?
    ===*/

    dbms_output.put(chr(10)); -- kluge to get a blank line

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

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

    /*===
        ONLY reaches here if NO exception raised;
        printing this message below to show that.
    ===*/

    dbms_output.put(chr(10));

    dbms_output.put_line('PROC REACHED ITS NATURAL END');
    dbms_output.put_line('(NO exceptions were raised)');

exception
    /*=== 
        pre-defined exception no_data_found is raised when
        you try to project an attribute into a local variable
        but no rows are selected
    ===*/

    when no_data_found then
        dbms_output.put_line('NO employees had salary 1000 times '
	                     || burp_quant);

    /*===
        pre-defined examples too_many_rows is raised when
        you try to project an attribute value into a single-valued-type
        (scalar) local variable but more than one row is selected
    ===*/

    when too_many_rows then
       dbms_output.put_line('MORE than one employee had salary 1000 '
                            || 'times ' || burp_quant);

end;
/
show errors

/*=== TESTING LOOPY ===*/

set serveroutput on

prompt ======
prompt TESTING procedure loopy
prompt

prompt =====
prompt calling loopy(5)
prompt
prompt SHOULD SEE:
prompt ...BURP! followed by 0-4
prompt ...GLIP! followed by 13-16
prompt ...all department names and locations
prompt ...empl nums and names for the four Sales employees
prompt ...a message that King has a salary of 1000 times 5
prompt and FINALLY a note that we reached the end with NO exceptions
prompt =====

exec loopy(5)

prompt =====
prompt calling loopy(2)
prompt
prompt SHOULD SEE:
prompt ...BURP! followed by 0-1
prompt ...GLIP! followed by 13-16
prompt ...all department names and locations
prompt ...empl nums and names for the four Sales employees
prompt and then a message that NO employee has salary 1000 time 2
prompt
prompt (and NEVER REACH the last note, because WAS an exception)
prompt =====

exec loopy(2)

prompt =====
prompt calling loopy(1.25)
prompt
prompt SHOULD SEE:
prompt ...BURP! followed by 0-1
prompt ...GLIP! followed by 13-16
prompt ...all department names and locations
prompt ...empl nums and names for the four Sales employees
prompt and then a message that MORE THAN ONE employee has salary 1000 time 1.25
prompt
prompt (and NEVER REACH the last note, because WAS an exception)
prompt =====

exec loopy(1.25)