=====
CS 328 - Week 4 Lecture 2 - 2025-02-12
=====
=====
TODAY WE WILL:
=====
* announcements
* over to the DATA TIER: intro to PL/SQL
* prep for next class
=====
* should be working on Homework 3!
* at-least-1st-attempts need to be submitted by 11:59
on Friday, February 14
* continue completing zyBooks Chapters 1 & 2
* deadline for completing those for credit
is 11:59 pm February 28 (Friday before Exam 1)
* see the now-posted course PL/SQL style standards
and some PL/SQL resources/references
=====
PL/SQL - "Procedural Language" SQL
=====
* Oracle's implementation for extending SQL to include
procedural structures (if statements, looping statements)
and other related features (local variables, assignment,
procedures, functions) that are common to procedural
programming languages
* looks very much to me like it was influenced
by the family of programming languages that includes
Ada (ALGOL family)
* interesting aspects:
* these "live" on the data tier,
amongst the objects that make up your data,
managed by the DBMS,
EXECUTED on the data tier under the auspices
of the DBMS;
* 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
* program "chunks"?
* stored procedure - a procedure stored as part of a database
* stored function - a function stored as part of a database
* trigger - also stored as part of a database
^ better "collective" name for these is SUBROUTINES
* function - expects 0 or more arguments, and returns something
* they certainly might have side-effects
* procedure - expects 0 or more arguments, but does NOT
RETURN anything <-- better have side-effects!
* C++ void functions are kluging procedures...
* trigger - as discussed in CS 325 - it is not called directly,
but is executed -- triggered -- when a specified action is
done to the database
=====
more details!
=====
* like SQL, PL/SQL is mostly NOT case-sensitive
* (except within string and character literals)
* char and varchar2 literals are written in single quotes
'moo'
=====
PL/SQL local variables and data types
=====
* need these to control loops, hold computations in progress,
etc.
* Ada-like bit:
local variables are declared giving the name THEN the data type!!!!!!!!!
var_name var_type;
quantity number;
* Ada-like bit:
where can you declare these?
* ONLY in the declare part of a block
...typically at the beginning of a block,
and we'll discuss this more in a moment
* sometimes the declare part starts with the word
DECLARE
...except not always... more on that in a moment;
* what types are available?
* if you can use a type for a SQL relation column,
you can use it for a local variable type in PL/SQL
* PL/SQL also adds several additional types --
LIKE boolean!!!!!!!!!!
* see the posted link for more PL/SQL data types
declare
latest_hiredate date;
num_invoices integer;
* there's even syntax for basing the type of a
local variable on the type of a relation's attribute:
tablename.columnname%TYPE
rep_job_title empl.job_title%type;
next_name empl.empl_last_name%type;
=====
PL/SQL assignment operator
=====
* this is := here, NOT = !!!!!!!!!!!!!!!!!!!
* = is used for equality comparison
* it can be used in an assignment statement
as well as to initialize a local variable
declare
count integer := 0;
...
count := count + 1;
=====
can you print to the screen?
=====
* this is executing on the data tier,
anything a PL/SQL subroutine prints to the screen
cannot make it to the application tier...
* but...
IF you run the following SQL*Plus command
set serveroutput on
...THEN the following PL/SQL statement WILL print to
the screen (to your sqlplus session):
dbms_output.put_line('desired_string');
* hooray, concatenation works here! But it is SQL concatenation,
so: ||
(and you can concatenate non-string data to a string)
=====
comments
=====
-- single line comment
/* multi-
line
comment */
=====
you can select INTO a local variable using the INTO clause
=====
select expr1, expr2, ..
INTO appropriate_local_variable, appr_2, ...
from blah,...
where blah...
* you can't put a select at the top level of a PL/SQL block
without an INTO clause
* be careful to make sure the local variable's
types are compatible with what the select is
projecting --
so-called scalar variables (single-valued) can only
accept a single projected value;
=====
what can go in a PL/SQL block?
=====
* PL/SQL statements
* many SQL statements (with some restrictions)
* NO SQL*Plus commands!!!
=====
where do you declare a PL/SQL subroutine?
=====
* in a SQL script, in a file with suffix .sql
* (and that script can contain PL/SQL, SQL, and SQL*Plus
statement
=====
how do you compile a PL/SQL subroutine?
=====
* in your .sql script,
you create your subroutine, to create it,
then you follow it -- on the line after the END; --
with a / to compile it (!!!!!)
* if there are errors, you can see the errors from
the latest thing compiled with the SQL*Plus command
show errors
...
END;
/
show errors
=====
SO!!!! basic procedure syntax:
=====
CREATE or replace PROCEDURE desired_proc_name(param_list) IS/AS
local declarations
BEGIN
body of procedure
END;
/
show errors
* FUN FACT that came up in WEEK 4 LAB EXERCISE:
UNLIKE C++, if a procedure has NO parameters, you OMIT the () after
the procedure name (empty () cause a compile-time error!!):
CREATE or replace PROCEDURE no_arg_proc_name IS/AS
...
* and see example stored procedure hello_world in posted
SQL script hello.sql