CS 328 - Week 1 Labs - 3:00 pm Lab - 2016-01-22

*   PL/SQL - an extension of SQL from Oracle
    *   Oracle's "procedural language" extension SQL

*   so: keep straight!!: difference between
    *   "plain" SQL
    *   SQL*Plus
    *   PL/SQL (which also includes versions of most SQL cmds)

*   what does SQL need to be procedural?
    *   needs basic structures like branching, repetition,
        "procedure" <-- subroutines

    *   needs local variables, ways to assign to them,
        ways to output them, etc.

    *   etc.

*   database trigger - procedural code stored in the database
    that is executed when a particular database event,
    such as insert, or update, or delete (IUD), occurs
   
    *   its primary goal is to enhance database integrity

    *   you can make sure, when something is inserted,
        that other changes based on that insertion
	are made;

	OR you can prevent certain actions if they'd violate
	subtle aspects of database integrity

	etc.

*   basic structure of a PL/SQL trigger:

    [ ] - optional
    < > - a name that you choose
    { } - you choose one of the options within

    create [or replace] trigger <trigger_name>
        {before | after} {insert | update | delete}
        on <table_name>
        [for each row]
    [declare
        <local_variable_declarations>
        ]
    begin
        [statements]
    [exception
        ]
    end;
    /      /* DO NOT FORGET THIS or won't compile!!!!!!!!!!! */
    show errors
    
*   PL/SQL declaration nifty-thing!

    *   you can declare a local variable to have the
        same type as a table column with the syntax:

        var_name tbl_name.col_name%TYPE;

        item_ordered inventory.item_num%TYPE;

    *   note that you can grab the values from
        the newly (to-be)inserted row or
	newly to-be-updated row

	:new.colname

    *   note that you can grab the values from
        the  (to-be)deleted row or
	prior-to-being-updated row

	:old.colname

*   ANOTHER way to assign to a local variable
    is to use a select statement with an
    INTO clause
    *   usually a select in PL/SQL needs an INTO clause

*   if syntax:

    if <condition> then
        [statements]
    [else
        statements]
    end if;

*   you can specify in a PL/SQL subroutine to print
    to the screen using:

    dbms_output.put_line('desired string');

    BUT you will ONLY SEE this output IF you set
    SQL*Plus's serveroutput to be on!!!!
    (by default, it is off)

    set serveroutput on