/*=====
procedure: loopy: integer -> void
purpose: expects an desired quantity of burps,
and has the side effects of printing to the screen
(if serveroutput is set to on)
* that many BURP messages
* GLIP messages from 13 to 16
* names and locations of current departments
* employee numbers and names of employees
with a job_title of 'Sales'
* printing the names of any employees
whose salary is 1000 times the given
burp quantity
* prints a message if NO exception raised
=====*/
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 of 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 ===*/
for i
in 13 .. 16
loop
dbms_output
.put_line(
'GLIP! ' || i);
-- uncomment the statement below to see you CANNOT
-- change the loop control variable in a for loop
-- within the loop:
--
-- i := 20;
end loop;
/*=== first demo of a cursor-controlled for loop ===*/
/* (note: select statement MUST be in parentheses) */
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;
/*=== here's one using an explicitly-declared cursor ===*/
for next_sales_empl
in sales_curs
loop
dbms_output
.put_line(next_sales_empl
.empl_num
|| ': '
|| next_sales_empl
.empl_last_name);
end loop;
/*===
to demo exception handling:
are there any employees whose salary is
1000 * the burp quantity?
(but SELECT statements can be persnickety about
what can be assigned to a local variable,
especially a scalar local variable,
in an INTO clause)
===*/
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 to demo that
===*/
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 value into a local variable
but no rows are selected
===*/
when no_data_found
then
dbms_output
.put_line(
'MOO, NO employees had salary 1000 times '
|| burp_quant);
/*===
pre-defined exception 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 loopy
prompt
prompt
============
prompt calling loopy(
5)
prompt
prompt should see:
prompt
======
prompt BURP
! followed
by 0-4,
prompt GLIP
! followed
by 13-16,
prompt
all department
names and locations,
prompt empl nums
and names of the four Sales employees,
prompt a message that King has a salary
1000 times
5,
prompt
and finally a note that reached
end with NO exceptions:
prompt
============
exec loopy(
5)
prompt
============
prompt calling loopy(
2)
prompt
prompt should see:
prompt
======
prompt BURP
! followed
by 0-1,
prompt GLIP
! followed
by 13-16,
prompt
all department
names and locations,
prompt empl nums
and names of the four Sales employees,
prompt a message that
NO employee has a salary
1000 times
2,
prompt (
and NEVER REACH the final dbms_output
.put_line
prompt because
of the no_data_found
exception)
prompt
============
exec loopy(
2)
prompt
============
prompt calling loopy(
1.25)
prompt
prompt should see:
prompt
======
prompt BURP
! followed
by 0-1,
prompt GLIP
! followed
by 13-16,
prompt
all department
names and locations,
prompt empl nums
and names of the four Sales employees,
prompt a message that MORE THAN ONE employee has a salary
1000 times
1.25,
prompt (
and NEVER REACH the final dbms_output
.put_line
prompt because
of the too_many_rows
exception)
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
-- kluge to get a blank line output!
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()