=====
CS 328 - Week 5 Lecture 2 - 2024-02-14
=====
=====
TODAY WE WILL
=====
* announcements
* on the data tier, continuing our intro to PL/SQL:
* a selection of PL/SQL loops
* intro to PL/SQL exception-handling
=====
* Should be working on Homework 4!
=====
REMINDER:
==== ****** ====
NEW EXAM 1 DATE - WEDNESDAY, FEBRUARY 28 <===========
==== ****** ====
* Exam 1 review - moving to Wednesday, February 21
* zyBooks Chapters 1, 2 deadline - moving to Friday, February 23
* Exam 1 - moving to Wednesday, February 28
=====
note: while calling a PL/SQL function/procedure
from SQL*Plus has special syntax,
calling them from another PL/SQL subroutine
should look familiar!
====
* remember job_overview? and job_count?
COULD call them in another PL/SQL subroutine with just:
begin
...
local_var := job_count('President');
job_overview('President');
...
end;
/
show errors
* (and we made a REFACTORED version of procedure
job_overview in 328lect05-2.sql, now calling
job_count)
AND adding a NOTE in its opening comment that:
uses: function job_count
...because you/someone in the future might need to
know that!)
=====
a selection of PL/SQL loops!
=====
* TODAY: if I write something in [ ], it means that
syntax is OPTIONAL
AND: if I write { choice1 | choice2 }, it means
you can choose choice1 or choice2
* loop statement implements iteration
something
LOOP
statements;
END LOOP;
* the LOOP keyword can be on its own line,
or at the end of the something line
=====
* here's a basic while loop in PL/SQL:
WHILE bool_expr LOOP
statements;
END LOOP;
=====
* here's a "classic" iterative for oop in PL/SQL:
FOR desired_index IN [REVERSE] start_num .. end_num LOOP
statements;
END LOOP;
* desired_index is set to start_num, then proceeds for each
value until end_num
* those two periods .. *ARE* part of the statement's syntax!!
* 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!
==== 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 ====
=====
* here's my favorite: a cursor-controlled for loop!
for desired_row_name IN { cursor_name | select_stmt } loop
statements;
... desired_row_name.desired_attrib_name ....
end loop;
* 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!
* 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;
=====
PL/SQL exception handling
=====
* 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
* PL/SQL handles these with an EXCEPTION block,
which must be right before the END; of a block
begin
....
EXCEPTION
WHEN desired_exc THEN
statements;
WHEN desired_exc THEN
statements;
....
WHEN OTHERS THEN
statements;
end;
* if an exception is RAISED in a block,
control passes immediately to the EXCEPTION part,
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.
* 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 TRANSACTION_BACKED_OUT
...and maybe more??
=====
* 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,
because I wanted to visually separate the
output of the different parts...