CS 328 - Week 2 - Labs - 2016-01-29

*   loops!

    while <condition>
    loop
        <desired_statements;>
    end loop;

    for <loop_index> in <lowest_num> .. <highest_num>
    loop
        <desired_statements;>
    end loop;

*   another really cool PL/SQL loop variation:

    simple cursor-controlled loop

    *   you can LOOP through the results of a select!

    *   for <row_name> in (<select of your choice>)
        loop
            <... row_name.col_name ...>
        end loop;

*   of course, we want parameters, also!
    
    create or replace procedure <myproc> <(p_name p_type,
                                        p_name p_type,
					...)> as

    create or replace function <myfun> <(p_name p_type,
                                        p_name p_type,
					...)>
        return <ret_type> as

    *   in PL/SQL (as in Ada and that ilk),
        you don't say HOW a parameter is passed,
	you say IF it is:
	...an INput parameter (giving info to the 
                               funct/proc),
	...an OUTput parameter (being set BY the 
	                        funct/proc),
	... or an INput OUTput parameter
	    (giving info to AND being set BY the
	    funct/proc)

    *   if you don't say how to pass it,
        it is an INput parameter by default

        to specify otherwise,
        you put the desired type --

	IN
	OUT
	IN OUT

	...between the param name and its type
	   when it is declared

    create or replace procedure blah(inny integer,
                                     outy OUT date,
                                     bothy IN OUT char) as

    *   ONE MORE IMPORTANT THING!!!
        parameter types in the declarations
	must be UNCONSTRAINED --
	it means you put varchar2 instead of varchar2(10),
                         integer instead of integer(3),
			 char instead of char(7),
			 number instead of number(5, 2)
			 ...

*   EXCEPTION HANDLING in PL/SQL
    *   can we be a bit more robust in how
        we handle run-time exceptions
	in our code?

    *   when an exception occurs in PL/SQL,
        control passes to the running block's
	exception part (if it has one);

	(control does NOT go back to earlier in
	the PL/SL block)

	what if block doesn't have an exception
	block, or doesn't handle that exception?
	it'll be passed on to the next level up;

*   within the block, how can you say you want to
    handle an exception?

exception
    when <desired_exception> then 
        <desired_actions>
    when <desired_exception> then
        <desired_actions>
    ...
    [when others then
        <desired>actions>
    ] 
end;

*   some examples of Oracle PL/SQL built-in exceptions:

    NO_DATA_FOUND         CURSOR_ALREADY_OPEN
    TOO_MANY_ROWS         INVALID_CURSOR
    ZERO_DIVIDE	          LOGIN_DENIED
    DUP_VALUE_ON_INDEX	  NOT_LOGGED_ON
    VALUE_ERROR		  PROGRAM_ERROR
    INVALID_NUMBER	  STORAGE_ERROR
    TIMEOUT_ON_RESOURCE   TRANSACTION_BACKED_OUT

*   how to declare an exception:

    my_new_exception exception;
    ...

    if bad_thing_I_worry_abt then
        raise my_new_exception;

    ...
    when my_new_exception then
        <handle that exception>