=====
CS 328 - Week 5 Lecture 1 - 2026-02-16
=====

=====
TODAY WE WILL:
=====
*   announcements
*   DATA TIER: continuing adding to PL/SQL
*   ...parameters
*   ...stored functions
*   ...basic if statement
*   prep for next class

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

=======
=== UNUSUAL SCHEDULE for FEBRUARY 17 - 20 ===
=======
*   I am traveling to a conference starting Tuesday, February 17,
    and will be gone for the rest of the week
    
*   There will be a RECORDED LECTURE in place of Wednesday, February 18's
    regularly-scheduled lecture -- I will send a class e-mail when it is
    available on Canvas.

*   How Thursday, February 19's lab exercise will be handled:
    *   There *will* be a Week 5 Lab Exercise.

        *   I 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 on Canvas 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

=====
PL/SQL parameter basics
=====
*   happily, PL/SQL procedures and functions CAN have parameters
    (to specify the number and type of expected arguments)

*   (we are talking about basic input parameters here --
    there is a slightly different syntax for output parameters
    and input/output parameters, which you are unlikely to need
    for CS 328)

*   happily, the syntax is almost what you might expect:

    create or replace procedure desired_proc(param_name param_type,
        param_name param_type, ...) is|as

    *   note that, as for local variables, you put the parameter's
        name and then its type

    *   if it has NO parameters, you CANNOT put () after the procedure/
        function name in the header

    *   the parameter types must be UNCONSTRAINED
        (that is, NO parentheses with numeric bounds/limits are
	allowed)

        *   number is the unconstrained type for number(3, 0)
	*   varchar2 is the unconstrained type for varchar2(30)
	*   char is the unconstrained type for char(4)
	... basically, avoid parentheses in parameter types!

*   CS 328 CLASS STYLE: do NOT give a parameter or local variable
    a name that is EXACTLY the same as a column name in one of the
    tables involved in that subroutine!

=====
PL/SQL stored functions
=====
*   these return a value!
    (and a RETURNED value can be accessed on the application tier...)

*   header:

    CREATE OR REPLACE FUNCTION desired_funct_name(param_list) RETURN des_type is|as

    *   note: the function's return type must ALSO be unconstrained (no parens!)

*   and its body must include a happily-familiar return statement:

    RETURN desired_expr;

*   you can call this function in another PL/SQL subroutine
    like you probably expect:

    begin
        dbms_output.put_line(job_count('President'));

        my_local_var := job_count('Sales');

*   to call a PL/SQL stored function at the SQL*Plus level, though,
    you need a PLACE to put what is returned;

    ONE (least-sleazy?) way:

    *   declare a SQL*Plus-level local variable with var:

        var num_job number  -- beware, fewer types available at sqlplus level

        when you use this local variable, it needs to be preceded by a :

        :num_job

        then you can assign the function's result to this

        exec :num_job := job_count('President')
    
    *   and you can print the value of a SQL*Plus local variable
        with the SQL*Plus print command:

        print :num_job
        print num_job

=====
PL/SQL IF statement
=====
*   basic syntax:

    IF bool_expr THEN
       actions
    ELSE
       actions
    END IF;

    FUN FACT: it has an...interesting multi-else option:

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

=====
after class
=====
*   what does Oracle function nvl's name stand for?

*   My quick search did not give an answer from
    Oracle's documentation;

    but a number of sites suggest thinking of it
    as NullVaLue -- a means to replace a null value
    with another value

    nvl(desired_expr, replacement_val_if_desired_expr_is_null)

    for example,
    
    select nvl(avg(salary), 0)
    from   empl
    where  job_title = desired_job_title;

    *   it expects 2 arguments
    
    *   if the first argument is non-null, that's the value
        of this expression -- otherwise, the second argument's
	value is the value of this expression

...Oracle's coalesce function is similar, but also a little different,
   based on the Oracle documentation:

   *   it can have two *or more* arguments 
   *   it returns the value of the first non-null argument
   *   it uses short-circuit evaluation for this (once it
       has found a non-null argument, it does not evaluate its
       remaining arguments)
   *   if all of its arguments evaluate to null, then the function
       returns null