CS 318 - Week 2 Lecture - 1-30-13

*   relation structure form:

    when you write a relation/table in the following form:

    table_name1(PK_NAME_1, PK_NAME_2, other1, other2,
               ... other3)

    table_name2(MY_ID, other1, another_name)
        foreign key (another_name) references 
            table_name1(pk_name_1, pk_name_2)

    *   ONLY write primary key attribute names
        in all-uppercase -- that will be our
	"syntax" for this-is-a-primary-key-attribute

    *   indicate foreign keys with a SQL-style
        foreign key clause after the table's
	relation structure

    *   for our reference purposes with such
        collection of relation structures,
	I don't care what order the relations
	are within the collection
	(although the DBMS cares at table-creation
	time...)

*   now continuing with PL/SQL discussion...

********
*   IF statements:

    IF <condition> THEN
        statements;
    [ELSE
        statements;]
    END IF;

    *   example: see how-many.sql 

********
*   repetition in PL/SQL --

    LOOP statement, that ends with
    END LOOP;

    *   here are two of the common variants:

    WHILE condition
    LOOP
       repeated_statements;
    END LOOP;

    FOR loop_index IN lowest_number .. highest_number
    LOOP
       repeated_statements;
    END LOOP;

    *   see loop-exs.sql

********
*   simple version of a cursor

    for row in (select_of_your_choice)
    loop
        ... row.col_name ...
    end loop;

    *   see cursor-loop-ex.sql

********
BASICS of PL/SQL PARAMETERS:

create or replace procedure blah(param_name param_type,
                                 param_name param_type,
				 ...
				 param_name param_type) as

create or replace function bleah(param_name param_type,
                                 param_name param_type,
				 ...
				 param_name param_type) return type is

    *   see how-many.sql for a 1-parameter function how_many

*   if not indicated otherwise, a parameter is an input
    parameter -- changing it WON'T change the corresponding
    argument (indeed, you'll get an ERROR if you try!)

*   if a parameter is an output parameter,
    it CAN be changed by the PL/SQL subroutine 
    and it WILL change the corresponding argument
    (and it shouldn't be used before it is set!!)

    *   declare an output parameter by putting OUT between
        the parameter name and the parameter type
        *   NOTE: I have tried OUTPUT in the past, and that 
            does NOT work.
            USE OUT... 8-)

*   if a parameter is an INPUT OUTPUT parameter
    it CAN be used and it CAN be changed in the subroutine
    and it WILL change the corresponding argument

    *   declare this by putting IN OUT between
        the parameter name and the parameter type
        *   NOTE: I also tried INPUT OUTPUT in the past,
            and ALSO found it does NOT work.
            USE IN OUT.

*   ONE MORE THING: parameter types must be UNCONSTRAINED
    varchar2  NOT varchar2(100),
    char NOT  char(3)
    number NOT number(3,2)

    *   see param-exs.sql 

********
EXCEPTION HANDLING

*   exception - a "bad thing" that happens at run time

*   in PL/SQL, an exception can be pre-defined or 
    user-defined;

    pre-defined expections are raised by the system;
    user-defined exceptions can be raised in your PL/SQL subroutine;

*   in your PL/SQL subroutine, you can choose to handle 
    exceptions
    in an EXCEPTION part that is placed right before the END;
    of a PL/SQL block;

...
begin
  ...
exception
   WHEN desired_exception THEN
       ...desired actions...
   WHEN desired_exception THEN
       ...desired actions...
   WHEN OTHERS THEN
       ...desired actions...
end;

*   if an exception is thrown in a block, control immediately
    passes to the exception part (and you DON'T return back
    afterward -- it's a one-way ticket!)

*   a small selection of the predefined exceptions:

    TOO_MANY_ROWS
    NO_DATA_FOUND
    ZERO_DIVIDE
    DUP_VALUE_ON_INDEX
    and many others!

*   EXCEPTION-HANDLER syntax:

    AFTER the keyword EXCEPTION,

    you can put:

EXCEPTION
    WHEN exception-name THEN
       statements;
    WHEN exception-name THEN
       statements;
    ...
    WHEN OTHERS THEN    -- this is OPTIONAL
       statements;
END;

*   see auth-exists.sql - example using predefined exceptions
 
*   see odetail-ex.sql - example using a user-defined exception

*   see exc-test.sql - example demonstrating the "one-way" nature
    when an exception is thrown

*   see bizarre-ex.sql - example with a nested block, both with
    exception handlers

*   see null-into-clarif.sql - example clarifying when a NULL is
    assigned and when a NO_DATA_FOUND exception is thrown