=====
CS 328 - Week 5 Lecture 1 - 2025-02-17
=====

=====
TODAY WE WILL:
=====
*   https://csclubhumboldt.org/hackathon - full spiel next class! 8-) !

*   announcements
*   DATA TIER: PL/SQL
*   PL SQL basic parameters
*   PL/SQL stored functions
*   maybe more? -> PL/SQL if statement!
*   prep for next class

=====
*   should be working on Homework 4!
    at-least-1st-attempts due by 11:59 pm on Friday, February 21

*   remember to complete the zyBooks activities for Chapters 1-2
    by 11:59 pm on Friday, February 28

=====
PL/SQL parameters!
=====
*   PL/SQL stored procedures and stored functions CAN have
    parameters, and these provide information that subroutine
    needs
    *   (note: triggers can't have parameters...)

*   the parameter declarations DO go in a set of parentheses
    RIGHT after the proc/function name in its header:

    create or replace [procedure|function] desired_name(param_list) ...

    where the param_list is a comma-separated list of parameter
    declarations:
    *   with the parameter name THEN its type 

    *   and the type MUST be UNCONSTRAINED <----- !!!!!!!!!!!
        ...NO parentheses with a size/limit included!

        so:
	chosen_name varchar2, ...
	next_code char, ...
	quant number, ...

    *   CS 328 class style: avoid giving parameters or local variables
        a name IDENTICAL to a column in one of tables involved in that
	subroutine!
	*   frequently a syntax error, always confusing to someone
	    reading your code!

=====
PL/SQL stored functions!
=====

*   basic function header:

create or replace FUNCTION desired_name(param_list) RETURN unconstrained_type is|as

*   and be sure to include a return statement!

    return desired_expr;

*   when you call a PL/SQL function, you have to provide a place for
    what it returns

    *   if this is within an PL/SQL subroutine, this is pretty straightforward --
        it can be within an assignment statement, for example

    *   but what about from sqlplus?

        ...you can declare a sqlplus local variable (!!!)

        var desired_name desired_type

        (it has fewer types available than SQL... or PL/SQL...)

        NOW you can use exec with that sqlplus local variable preceded by a colon
	and your function call:

	exec :desired_name := job_count('blah')

        and you can print the value of a sqlplus local variable with the
	print command:

        print desired_name

=====
PL/SQL if statement
=====
*   of course there's an if statement!

    IF bool_expr THEN
        actions...
    ELSE
        actions...
    END IF;

    *   NOTE that it includes the keyword THEN
    *   NOTE that it ends with an END IF;

    *   and, note that PL/SQL has a bit of an odd version for the if-else-if pattern:

    IF bool_expr THEN
        actions...
    ELSIF bool_expr THEN
       actions...
    ELSIF bool_expr THEN
       actions...
    ...
    ELSE
       actions...
    END IF;