=====
CS 328 - Week 5 Lecture 2 - 2025-02-19
=====
=====
TODAY WE WILL:
=====
* announcements
* DATA TIER: continuing intro to PL/SQ:
* a selection of PL/SQL loops
* intro to PL/SQL exceptions
* prep for next class
====
* https://csclubhumboldt.org/hackathon
* to sign up for this year's CS Club Hackathon
* sign up by March 6
====
* should be working on Homework 4!
* at-least-1st-attempts due by 11:59 pm on Friday, Feb 21
* and be sure to complete the zyBooks chapters 1 and 2 activities
by 11:59 pm on Friday, February 28 to receive credit for those
=====
several flavors of PL/SQL repetition!
=====
* the LOOP statement implements repetition
and, yes, it is terminated with:
END LOOP;
* BUT there is syntax that PRECEDES the LOOP keyword
that determines what kind of loop it is;
* we will cover 3 versions here --
there are more in the Oracle documentation,
BUT note that I am deliberately skipping some UGLY loop forms;
* so to be safe ASK ME before you use a loop form that's
NOT one of these three...
* CLASS STYLE: no GOTO or EXIT WHEN statements are allowed
in CS 328 -- they can be a sign of bad logic structure,
and when they aren't, exception-handling is probably a better
choice
=====
* classic while loop:
=====
WHILE bool_expr LOOP
desired_statememt;
...
desired_statememt;
END LOOP;
* it works like I hope you expect
* leading decision loop that initially checks bool_expr
and only enters loop body if bool_expr is true
* then, after completing body, sees if bool_expr still true
and repeats body if so and exits if not
* ( ) are NOT required around its bool_expr, but it is
fine to put them if you'd like!
* keyword LOOP can go at the end of the loop's first line,
or on the second line:
while bool_expr
loop
...
end loop;
* might be easier not to forget the keywork LOOP this way,
and "brackets" the END LOOP; nicely...
=====
* an interesting variant of a count-controlled FOR loop:
=====
FOR desired_index IN lowest_num .. highest_num LOOP
desired_statement;
...
desired_statement;
END LOOP;
* those two periods .. in lowest_num .. highest_num
*ARE* part of the statement's syntax!!
* sets desired_index to EACH value in lowest_num .. highest_num
in turn, and inclusive
* FUN FACT: you CANNOT change the desired_index
loop control variable within the body of this loop!
* FUN FACT: the variable you use for desired_index
CAN be a local variable you have declared --
it can also be a NEW variable, which will be
automatically declared;
BUT in that case, as in C++, the scope of the
automatically-declared variable is JUST the body
of the loop!
******* CORRECTION AFTER CLASS!!!!!!!! *********
* you can have an optional REVERSE --
FOR desired_index IN REVERSE lowest_num .. highest_num LOOP
* BUT I was WRONG when I said you put the largest
number first -- you SHOULD still put
lowest_num .. highest_num...!
* (but it DOES set desired_index to highest_num first,
and progresses through lowest_num)
==== RABBIT HOLE from AFTER CLASS ====
* YIKES -- found out after class that REVERSE is
a little DIFFERENT than I expected!
AS EXPECTED,
* for i in 2 .. 5
loop
dbms_output.put_line('i: ' || i);
end loop;
prints:
i: 2
i: 3
i: 4
i: 5
SURPRISING (to me):
* but REVERSE starts from the ***end_num*** and goes to
the ***start_num*** --
(that is,
you STILL put smaller value and then larger value,
it just starts at the larger value)
for j in REVERSE 2 .. 5
loop
dbms_output.put_line('j: ' || j);
end loop;
prints:
j: 5
j: 4
j: 3
j: 2
* and -- what if you put:
for k in REVERSE 5 .. 2
loop
dbms_output.put_line('k: ' || k);
end loop;
...! it prints NOTHING;
(no error, guess it just doesn't enter loop
because 2 < 5?)
* wait -- then what happens if you put:
for m in 5 .. 2
loop
dbms_output.put_line('m: ' || m);
end loop;
also prints nothing
(no error, guess it just doesn't enter loop
because 5 > 2...)
SO: I *think* this is true:
* if no REVERSE,
starts desired_index at start_num
and adds 1 to desired_index each time
until it is greater than end_num;
* if have REVERSE,
starts desired_index at ***end_num***
and subtracts 1 from desired_index each time
until it is less than ***start_num***
==== END of after-class RABBIT HOLE ====
* see procedure reverse_play added to 328lect05-2.sql
after class, demonstrating the above!
=====
* and: a cursor-controlled for loop,
that can loop through the results of a SQL SELECT statement!
=====
FOR desired_row_name IN [(desired_select) | cursor_name] LOOP
desired_statement;
... and you can refer to an attribute in the current row
using desired_row_name.attrib_name ...
desired_statement;
END LOOP;
* fun fact: if you put a SELECT statement in the cursor-controlled
for loop first line, it MUST be in parentheses!
* desired_row_name is set to EACH cursor row/projected
select_stmt row in turn -- note that its type is one
able to hold an entire ROW!
* (it is automatically declared if you haven't previously
declred it)
* within the loop, you can get the value for a particular
attribute within the current row with the syntax:
desired_row_name.desired_attrib_name
* for example:
for next in (select *
from dept)
loop
dbms_output.put_line('Name: ' || next.dept_name);
end loop;
* you can also declare a cursor in the declaration part -- for example,
/*=== example of declaring a cursor ===*/
CURSOR sales_curs IS select empl_num, empl_last_name
from empl
where job_title = 'Sales';
* then, in that block's body, you could have:
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;
=====
PL/SQL exception handling
=====
* certain run-time bad things can happen at run-time, of course
* exception - a "bad thing" that happens at run time,
that causes an error of some sort;
PL/SQL, like many other languages, provides a way of
HANDLING exceptions
* EXCEPTIONS of various types might be RAISED,
and you can write your PL/SQL to handle these;
* you can declare your own exceptions,
or you can use predefined exceptions
* you put your exception-handling code
within the EXCEPTION BLOCK of a PL/SQL block
* must be right before the END; of a block
BEGIN
....
EXCEPTION
WHEN desired_exc1 THEN
statements;
WHEN desired_exc2 THEN
statements;
...
WHEN OTHERS THEN
statements;
END;
* if an exception is RAISED in a block,
control passes immediately to the EXCEPTION block,
and to the first WHEN whose type matches the type of
of the raised exception.
* That WHEN's statements are done, and that
block is now EXITED <-- !!!!!
you don't do another WHEN's actions,
you don't return to where the exception occurred!
* how is an exception raised?
it might be a predefined "bad" action,
or you CAN raise an exception within code;
=====
Examples of PL/SQL built-in exceptions
=====
NO_DATA_FOUND CURSOR_ALREADY_OPEN
TOO_MANY_ROWS INVALID_CURSOR
ZERO_DIVIDE LOGIN_DENIED
DUP_VALUE_ON_INDEX NOT_LOGGED_ON
VALUE_ERROR PROGRAM_ERROR
INVALID_NUMBER STORAGE_ERROR
TIMEOUT_ON_RESOURCE
...and several more!
* see Table 12-3 at:
https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/predefined-exceptions.html
=====
* you CAN define your own exception,
and raise it with a raise statement:
create or replace ...
past_due exception;
...
begin
....
if pd_yet = false then
raise past_due;
end if;
=====
WEIRD PL/SQL thing discovered after class
...it's hard to print a BLANK line to the
screen in PL/SQL!
=====
* evidently it tries reeeeeallly hard to suppress
blanks...?!
* dbms_output *package* really is intended to debugging
messages...!
* what FINALLY worked: using another of DBMS_OUTPUT's procedures,
PUT, with PL/SQL's way of indicating the ASCII character
for a newline, chr(10):
dbms_output.put(chr(10));
* you'll see this in 328lect05-2.sql,
in the reverse_play procedure added after class,
because I wanted to visually separate the
output of the different parts...