=====
CS 328 - recorded for Week 5 Lecture 2 - 2026-02-18
=====
=====
IN THESE 2 RECORDINGS WE WILL:
=====
* announcements
* DATA TIER - more on PL/SQL
* a selection of PL/SQL loops
* intro to PL/SQL exceptions
* prep for next class/class activity
=====
* should be working on Homework 4!
* deadline: 11:59 pm on Friday, February 20
* must get at-least-1st-submissions in by then
* (it is very reasonable to have .sql files in a
directory that is not under public_html,
so remember to run ~st10/328submit from each
directory that has files to submit)
* if you haven't finished them yet,
should still be reading/working through Chapters 1 and 2
of the course zyBooks
* to receive credit for these activities, must complete
them by 11:59 pm on Friday, February 27
* remember:
* for the Week 5 Lab on Thursday, February 19:
* recommend completing the Week 5 Lab Exercise in lab using
pair-programming
* but, because I am traveling:
* the Week 5 Lab Exercise will be available starting
at 9:00 am on Thursday, February 19
* you have the OPTION to work either individually or
using pair-programming,
either in-lab or outside of lab
* to be accepted for credit, you must submit your
lab exercise files by 11:59 pm on FRIDAY, FEBRUARY 20
* I will send a class e-mail when Homework 5 is available.
* I hope to check e-mail at least once a day, conference-and-hotel
wi-fi and conference-schedule permitting
=====
one small PL/SQL aside - KLUGE to get a blank line output
from PL/SQL
=====
* PL/SQL seems to try REALLY hard to suppress blanks in its
printed output ??!!
* but you can ask to output the ASCII character for a newline,
using chr(10)
and you need to do so using the dbms_outout.put procedure,
(not the usual dbms_output.put_line)
* so:
dbms_output.put(chr(10));
=====
PL/SQL LOOPS, old and new!
=====
* PL/SQL uses a LOOP statement for its loops:
LOOP
....
END LOOP;
(but you add TO this basic syntax for its different
supported loop types)
* NOTE that you don't use { }
* NOTE that PL/SQL supports more loop styles than those
I am showing here -- do not use any not discussed
here unless you clear it with me first
(otherwise you may lose style points/or your work may not
be accepted for credit)
* RELATED: note this CS 328 COURSE STYLE STANDARD:
* NO GOTO or EXIT WHEN statements are allowed -- subroutines
including these will not be accepted for credit.
* Since PL/SQL has exception handling, that or better logic
structuring are BETTER choices (more readable, more maintainable)
=====
classic WHILE loop
=====
* WHILE bool_expr
LOOP
desired_statement(s);
END LOOP;
=====
count-controlled FOR loop
=====
* FOR desired_index IN lowest_num .. highest_num
LOOP
desired_statement(s);
END LOOP;
* desired_index will be set to lowest_num for the 1st
iteration of the loop,
then lowest_num + 1,
etc.,
until it reaches being set to highest_num, and after
that loop iteration, the loop ends.
* NOTES:
* you may NOT change the desired_index/loop control
variable in the body of loop!
* if you don't declare the desired_index/loop control
variable, it will be auto-declared as type integer
=====
cursor-controlled FOR loop
=====
* for our first version of this:
FOR desired_row_name IN (select_stmt_of_choice)
LOOP
desired_statement(s);
-- can use col_name in the current row using
-- desired_row_name.col_name
END LOOP;
* desired_row_name will be set to each row projected by
select_stmt_of_choice in turn
* fun fact: the desired_row_name variable you use
will also be automatically declared!
=====
a little more about cursors
=====
* from the Oracle documentation:
* Oracle uses WORK AREAS to execute SQL statements
* A PL/SQL construct called a CURSOR lets you name
a work area and access its stored information
* TWO kinds of cursors: IMPLICIT and EXPLICIT
* we used an IMPLICIT cursor in our 1st example
of a cursor-controlled for loop;
* you can also EXPLICITLY declare a cursor variable;
[in DECLARE section]
CURSOR sales_curs IS select empl_num, empl_last_name
from empl
where job_title = 'Sales';
BEGIN
...can now use sales_curs in a cursor-controlled for-loop...
=====
intro to PL/SQL exceptions
=====
* recall that SQL statements and scripts can have run-time
errors;
* and so can PL/SQL subroutines;
* we know, with the SELECT statement INTO clause,
that you must not try to project multiple values into
a single (scalar-valued) variable,
and some will throw an error if NO rows are projected;
* these run-time throw EXCEPTIONS --
these may be built-in, or user-defined
you can write your PL/SQL subroutine to CATCH exceptions,
and handle them appropriately for your domain;
if you don't catch them, they get passed up until you
get an error message.
=====
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
* so: how can your PL/SQL subroutine CATCH an exception?
you give it an optional EXCEPTION block (exception handler)
subr_header
decl_part
begin
body_statements;
EXCEPTION
WHEN desired_exception1 THEN
statements;
WHEN desired_exception2 THEN
statements;
...
WHEN OTHERS THEN
statements;
end;
/
show errors
* in the body, IF an exception is thrown/occurs,
control passes IMMEDIATELY to the EXCEPTION block.
* the thrown exception is compared to each when clause's
exception until it finds a match,
then JUST *that* WHEN clause's statement(s) are done,
and then the subroutine ENDS.
* NOTE: you do NOT go back and finish the subroutine
body!
* NOTE: you do at MOST one WHEN clause's actions!
(you can't do MORE than one!)
* hey, guess what?
if you have a SELECT with an INTO clause and a SCALAR
variable and more than one row is projected,
that throws a TOO_MANY_ROWS exception!
if you have a SELECT with an INTO clause and a SCALAR
variable and you are NOT projecting an aggregate function,
and NO rows are projected,
that throws a NO_DATA_FOUND exception!
...see this in action in the posted loopy procedure!