=====
CS 328 - Week 4 Lecture 2 - 2026-02-11
=====
=====
TODAY WE WILL:
=====
* announcements
* over to the DATA TIER - more on PL/SQL
* prep for next class
=====
* should be working on Homework 3!
* deadline for at-least-1st-attempts: 11:59 pm Friday, Feb. 13
* all files are submitted on nrs-projects using
~st10/328submit
* BUT! Problem's 1's "second database" files are
submitted with a homework number of *** 33 ***
and Problems 2-onward use a homework number of *** 3 ***
* (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
=====
more on PL/SQL
=====
* PL/SQL - Procedural Language/Structured Query Language
* Oracle's extension of SQL adding procedural structures
(if, loops, etc.) and other features (local variables,
assignment, stored procedures, stored functions) common
to procedural programming languages
* (syntax seems similar to that of the programming language
family that includes Ada...)
* these PL/SQL subroutines (triggers, stored procedures,
stored functions) LIVE on the *DATA* tier;
(not the client tier, not one of the application tier(s))
* these are STORED as database objects,
along with your tables and views and constraints and etc.,
managed by the DBMS
* something on an application tier can ASK that a PL/SQL
subroutine be executed,
can ASK the DBMS for this,
and these subroutines are then EXECUTED on the DATA TIER
* these have useful access to the database relations,
and may be particularly useful for enhancing
DATA INTEGRITY,
and sometimes are a good choice for certain implementing
certain BUSINESS RULES
* you used triggers in CS 325 --
a trigger is executed, or triggered, when a particular
action is done on the database;
what is the difference between a stored function and a
stored procedure?
* a stored function RETURNS something
* PL/SQL: NEEEEEDS to return something
* a stored procedure does NOT return ANYTHING (it just has
side-effects <-- printing, affecting tables, maybe more)
* yes, C/C++ kluge procedures with their void functions...!
* PL/SQL: must NOT have a return statement!
=====
* PL/SQL is mostly NOT case-sensitive!
* PL/SQL keywords are NOT case-sensitive,
lowercase letters are equivalent to corresponding
uppercase letters EXCEPT within string and character
literals
* as in SQL, char and varchar2 literals are written in
single quotes:
'moo'
=====
PL/SQL local variables and data types
=====
* must be declared in a declare block (right before a
begin ... end block)
* and the declaration includes the desired name and type --
BUT!!!!! in a different order than you might expect!!
var_name var_type;
YES, "backwards" to C family languages!!
* what types?
* if you can declare a table attribute to be a particular
type, you can (I think) declare a local variable of that
type
* PL/SQL also supports some additional data types --
for example, boolean!!!!!!!!!!!!
and cursors
and exceptions!
so, for example, in a declare part, you could have:
new_item varchar2(25); /* char will be assumed to be char(1),
varchar2 will be assumed to be varchar2(1) */
latest_hiredate date;
num_invoices integer;
=====
little fun additional type syntax
=====
* if you'd like a local variable to have the SAME
type as that of a particular table's attribute,
you can use a type of:
tbl_name.attrib_name%TYPE
e.g.,
rep_job_title empl.job_title%type;
item_ordered inventory.item_num%type;
=====
PL/SQL assignment
=====
* PL/SQL's assignment operator is := (not =) !!!!!!!!!!!
quantity integer;
BEGIN
quantity := 0;
* also OK:
quantity integer := 0;
BEGIN
...
* = operator is used for equality comparison
=====
can you print to the screen?
=====
* YES, but this is a side-effect you can ONLY
see on the data tier! (not the application or
client tiers)
* the DEFAULT for these, also, even on the data
tier (sqlplus) is to suppress these UNLESS
you set serveroutput to on:
set serveroutput on
* IF you have set serveroutput to on,
then THIS is how you can print to the screen:
dbms_output.put_line('desired string' || variable || expression);
* only one argument is allowed, BUT you can use
concatenation, ||, to combine expressions;
* NOTE: you cannot use prompt in a PL/SQL subroutine,
because prompt is a SQL*Plus command;
only PL/SQL statements and SQL statements can be
within a PL/SQL subroutine
=====
comments
=====
* just like SQL
-- single line comment
/* multi
line
comment */
=====
INTO clause
=====
* some SQL statements, in some contexts, might need
additional clauses added to use them in PL/SQL;
* for example:
* a select statement cannot be "by iself" in a PL/SQL
block
nor can it be on the right hand side of an assignment
statement
BUT -- it can have an INTO clause so that what
it projects goes into APPROPRIATE local variable(s)
an_empl_num empl.empl_num%type;
begin
select empl_num
INTO an_empl_num
from empl
where job_title = 'President';
* be careful that the type of the variable
is compatible with the value being projected;
and so-called scalar variables (single-valued) can only
accept a single projected value;
======
PL/SQL procedure header and structure
=====
CREATE OR REPLACE PROCEDURE proc_name(param_list) IS/AS
optional_local_decls
BEGIN
pl/sql_statements;
...
[EXCEPTION-block]
END;
/ <--- this / COMPILES the procedure!!!!!
* and to see any compilation errors,
you can use the SQL*Plus command:
show errors
* FUN FACTS:
* UNLIKE C++, if a procedure has NO parameters, do NOT put () after
its name in its header!
CREATE OR REPLACE PROCEDURE no_param_proc_name IS/AS
...
* having an empty () after the procedure name causes a
compile-time error!!
* BUT - when you CALL a no-parameter procedure, can either omit
or put (), and either works;
=====
to execute a PL/SQL procedure from ANOTHER PL/SQL
subroutine
=====
* just call it!
my_proc;
my_proc();
my_proc2(arg, arg, ...);
=====
to execute a PL/SQL procedure from sqlplus
=====
* use the exec command
exec my_proc
exec my_proc()
exec my_proc2(arg, arg, ... )
* and see example stored procedure hello_world in posted
SQL script 328lect04-2.sql