=====
CS 328 - Week 4 Lecture 2 - 2024-02-07
=====

=====
TODAY WE WILL
=====
*   announcements
*   over to the DATA TIER: starting intro to PL/SQL
*   prep for next class

=====
*   should be working on Homework 3!

=====
intro to PL/SQL
=====
*   "Procedural Language" SQL -- Oracle's extension
    of SQL to add procedural structures (for ex: if, loops)
    and other such features (local variables, procedures,
    functions) common to procedural programming languages

    *   looks VERY much (to the instructor) as if it were influenced
        by the family of programming language that includes
	Ada

*   where do your chunks of PL/SQL "live"?
    *   in your database!
        along with your other database objects (like tables,
	    views, etc.!)
	managed by your DBMS!
	executed on the DATA TIER!

        application tier may ASK that they BE executed,
	but the PL/SQL chunks are actually executed on
	the data tier;

=====
*   in PL/SQL, three major kinds of "chunks" of code:
    *   stored functions! that may expects arguments
        and ALWAYS return something

    *   stored procedures! that may expect arguments
        and NEVER return anything!
	*   they just have side-effects;

    *   triggers! that are not called directly,
        but are executed -- or triggered --
	when a specified action is done to the database

    *   a fancier word for "chunks" of code is
        SUBROUTINES - so a PL/SQL subroutine might
	be a stored function or a stored procedure
	or a trigger!

*   PL/SQL -- like SQL -- is mostly NOT case-sensitive

=====
PL/SQL local variables
=====
*   in PL/SQL,
    *   you can declare local variables in the
        declarative part of any PL/SQL block,
	subroutine, or package

        ^ that's RIGHT before the block's beginning

        ^ SOMETIMES starts with the keyword
	  declare

    *   within that declarative part:

        var_name var_type;

        *   YES, the name and THEN its type!!!

=====
PL/SQL types
=====
*   if you can declare a SQL column to be of a type,
    you can use that type for a PL/SQL local variable

    and there are also ADDITIONAL types available,
    such as    boolean   !!!!!!!!!
    and        cursor    <-- to hold, say, a select statement result
    and        exception (more on that later)

*   for example:

    declare
        new_item         varchar2(25);
	latest_hiredate  date;
	num_invoices     integer;

*   you CAN declare a local variable's type to be the
    same as a table's attribute!

    give it the type:   desired_tbl.desired_col%TYPE

    for example:

    declare
        rep_job_title empl.job_title%TYPE;

=====
PL/SQL assignment operator
=====
*   in this family of languages,
    the assignment operator is:		:=

    and you can initialize a local variable using this:

    declare
        count integer := 0;

    and within a block, you can assign to a local
    variable using this:

    begin
        count := count + 1;

*   and you use = for equality comparison...

=====
can you print to the screen?
=====
*   um yes but you can ONLY see it when you run a PL/SQL subroutine
    on the data tier within sqlplus

    AND the default for sqlplus is that you NOT see them
    unless you ask for them:

    set serveroutput on

*   here's PL/SQL command:

    dbms_output.put_line('desired string');

    (you CAN use concatenate || in put_line's argument!)

=====
comments in PL/SQL
=====

*   like in SQL!

-- single line comment

/*    multi-
      line
      comment   */

=====
what can go in a PL/SQL block?
=====
*   PL/SQL statements
*   SQL statements - although sometimes they need parts added to them

*   you CANNOT put sqlplus COMMANDS in there!
    ...that would be like putting Linux ls or cd commands
       directly into a C++ program

*   example of a cool addition you can make to a select statement
    to be able to use it as a top-level statement in a PL/SQL block:

    an INTO clause!

    select thing_to_project
    INTO   local_variable
    where  ...

    ...and the value projected goes into the given local variable

=====
PL/SQL stored procedure basic syntax:
=====

CREATE OR REPLACE PROCEDURE desired_proc_name(param_list) IS/AS
    declaration part
BEGIN
    PL/SQL and/or SQL statements;
    ...
[exception-handling part]
END;
/    -- REQUIRED or Oracle will NOT ACTUALLY CREATE IT!!!

show errors   -- to see the syntax errors from the most-recent
              --    PL/SQL subroutine creation

-- you can EXECUTE a stored procedure you have created
--    within sqlplus using the execute command (exec for short)

exec desired_proc_name(arg1)

=====
DIFFERENCE between start/@ and execute/exec
=====
*   make sure this is clear to you!

*   start (@ for short) - SQL*Plus command
    *   allows you to run the specified SQL *script*
        (which can contain a combination of
            SQL*Plus commands,
	    SQL statements, and
	    creation of PL/SQL subroutines)
	from the sqlplus SQL> prompt

*   execute (exec for short) - SQL*Plus command
    *   allows you to run the specified
        already-created-and-compiled
	PL/SQL stored procedure or stored function
        from the sqlplus SQL> prompt