CIS 318 - Week 2 Lab - 1-28-13
********
PL/SQL -- adds procedural programming features to Oracle SQL
* you saw an example of a trigger written in PL/SQL in CIS 315;
now we are adding PL/SQL stored procedures and stored
functions
* usually, (in theory? 8-) ) the primary goal of a trigger
is to ENHANCE DATABASE INTEGRITY
in contrast, usually the primary goal of stored procedures
and stored functions is to IMPLEMENT BUSINESS RULES
* wait a minute -- aren't we, then, kind of violating the
idea of our n-tiers, putting business rules in the
data-server tier?
I think so! -- philosophical to consider: when, then,
are the costs of this worth it?
...we'll see, for example, that there can be SECURITY
advantages to stored procedures and stored functions
(harder to SQL-inject)
* I don't have a good answer for how "bad" this violation
is yet --
but it is something to consider/keep in mind;
* as you know -- PL/SQL is block-structured;
* basic PL/SQL block: ([] means optional piece within!)
[DECLARE -- but you don't use DECLARE before a
chosen_name type; -- PL/SQL subroutine's local
another_name type; -- declarations
]
BEGIN
[statements;]
[EXCEPTION
exception_handlers_here;]
END;
/ -- NEED / after the END; of an OUTERMOST block, or
-- PL/SQL subroutine will NOT be compiled!
* also nice to put:
show errors
...after a subroutine's final END; (an outermost block), so that
you will be shown any compiler errors;
* procedure header syntax:
-- opening comment (at least procedure name, description of what
-- it expects and does)
create or replace procedure procedure_name(param_list) as
local_decls;
begin
statements;
end;
/
show errors
* to execute a stored procedure from the SQLPLUS prompt,
type
exec proc_call(argument, argument, ... argument)
* to execute a stored procedure from PL/SQL subroutines,
type
proc_call(argument, argument, ... argument)
* (but a zero-argument procedure doesn't have to have () in
its call, although it can!)
* let's review/intro a few more PL/SQL features before our
first stored procedure example:
* PL/SQL identifiers:
* begins with a letter;
* followed by 0 or more letters, digits, underscores, $, #
* like SQL -- case is NOT significant for identifiers;
* cannot be a reserved word
* variable types
* any SQL type can be the type of the PL/SQL variable;
* BUT PL/SQL variables have a few ADDITIONAL possible
types as well -- for example,
* boolean
* in PL/SQL declaration, you put the name, THEN its type!
DECLARE
found_name boolean;
name varchar2(27);
id_code char(5);
quantity int;
cost number(6, 2);
date_done date := sysdate;
max_salary number;
* PL/SQL statements
* any SQL statement is a PL/SQL statement;
* assignment statements
variable := expression;
cost := 3.95;
* to "grab" the result from a select into a variable,
put an INTO clause between the SELECT and FROM clauses
of a select statement;
select blah
into desired_variable
where blah;
select max(salary)
into max_salary
from empl;
...and INTO clause can have a comma-separated
list of variables:
select book_no, pub_no
into bnum, pnum
from books
where book_no = '100';
* NOTE: number and types of values projected have to
be suitable for the number and types of variables in
the INTO clause!
* STYLE: avoid having variable names
that are the SAME as attribute names
in the tables being used!!
* example of a PL/SQL procedure: see count_empls in
count-empls-proc.sql
* function header syntax:
-- opening comment (at least procedure name, description of what
-- it expects and returns)
create or replace function funct_name(param_list)
return ret_type is
local_decls;
begin
statements;
...
return expr;
end;
/
show errors
* I thought for a long time you couldn't directly call
a PL/SQL function from the SQL*Plus command line --
then I thought maybe you could, but with very arcane
syntax;
now I found out -- can, with syntax that ISN'T so bad!!
-- this worked 1-28-13!
-- here's how you can declare a local variable at
-- the SQL*Plus level
var num_empls number
-- here's how you can execute a function, storing what that
-- function returns in that local variable
-- (notice the : and := )
exec :num_empls := empl_count();
-- and here's how you can output the value of a local
-- SQL*Plus variable
print num_empls
* can ALSO project a function's result by using a select
from special table dual:
select my_funct(arguments)
from dual;
* and, if you prefer, can also include a little
testing *procedure* as a convenient tester for a function...
(how I USED to do it...!)
* example of a PL/SQL function: see empl_count in
empl_count_funct.sql
********
* IF statements:
IF <condition> THEN
statements;
[ELSE
statements;]
END IF;
* we'll see examples of this on Wednesday;
********
* 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;
* we'll also see examples of these on Wednesday;
********
* simple version of a cursor
for row in (select_of_your_choice)
loop
... row.col_name ...
end loop;
* ...and this as well;
...we'll talk about adding parameters to these,
and exception handling, on Wednesday as well, hopefully;