=====
CS 328 - Week 5 Lecture 1 - 2024-02-12
=====

=====
TODAY WE WILL
=====
*   announcements
*   still on the data tier!
*   PL/SQL...
    *   parameter basics
    *   functions
    *   if statements
*   prep for next class

*   should start Homework 4!
    *   submit early, submit often!

==== ****** ====
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

=====
PL/SQL typical parameters!
=====
*   functions and procedures can have parameters!
    (triggers cannot!)

*   declare them in the function or procedure header,
    RIGHT after the function or procedure name

    *   comma-separated list of declarations
    *   BUT! as for PL/SQL local variables,
        you put the parameter name and THEN its type

    *   BUT! thoe PL/SQL parameters, the parameter
        type MUST be UNCONSTRAINED
	*   cannot have ( ) after it

        *   ex: varchar2 is OK, varchar2(20) is not

            create or replace procedure blah(par1 varchar2, par2 number) is

    *   CLASS STYLE:
        do not name a parameter or local variable
	to be exactly the same name as a table column
	for a table involved in that PL/SQL subroutine

=====
PL/SQL stored functions
=====
*   here's the basic function header syntax:

    create or replace function desired_funct_name(par_name par_type, ...) RETURN ret_type is/as
                      ^^^^^^^^                                            ^^^^^^^^^^^^^^^

*   LIKE for a procedure, its parameter types MUST be unconstrained

*   NOTE: its return type must ALSO be unconstrained!!

*   and should have at least one appropriate return statement:

    return desired_expr;

    (which ends the functions and returns desired_expr as the value for that
        call of that function)

=====
PL/SQL basic if statement!
=====

*   basic syntax:

    if bool_expr THEN
       statement;
       ...
       statement;
    else
       statement;
       ...
       statement;
    end IF;

*   yes, there's a THEN keyword required!

*   yes, there's a END IF; required!

*   fun fact: there's also an odd syntax for an
    "else if" kind of situation:

    IF bool_expr THEN
        actions;
    ELSIF      /* ODD BUT TRUE! NOT A TYPO!!! */
        actions;
    ELSIF
        actions;
    ...
    ELSE
        actions;
    END IF;