1. Stored Procedures
2. Functions
3. Triggers
4. Function, Procedure and Trigger difference

Stored procedures are stored in the database and can be executed in either
a pl/sql block or by the sql*plus execute command 

Only a procedure can be executed with the execute command and not a FUNCTION

In the argument part of a stored procedure or function or triggger we can
never pass a size for the datatype for example
the following is incorrect

Create procedure my_proc( name in varchar2(200 ) 
is 
begin 
null;
end;

The moment you specify the size as in varchar2(200) oracle pl/sql engine
will throw an error. This may come up in the exam just remember that 
never to specify a datatype in case of arguments in a procedure, function.

The fundamental difference between a function and a stored procedures is that
a function returns a value and a procedure does not.

This question can be twisted and put to you in a way such as :
Which of the following stored objects return a value?
a)procedure
b)function
c)trigger.

You can easily choose a) as your answer.

A Function is similar to a stored procedure in creation except that
it has to return a value and this is what makes it different from a procedure.
A function is generally used to compute a value and return it to the calling
program , while a procedure is used to do processing.

A function can be used as part of the sql statement as well such a function
example are : chr,ascii,to_char and so on.

If a user creates a function to do some calculation it is  called a user defined
function.

Example:
create or replace function my_sqrt (a_in as number)
return number is
begin
return a_in * a_in ;
end;

For example in the above case I have created a user defined implementation
of sqrt function.
The above function can be easily used in a sql statement such as :

select my_sqrt( 3 ) from dual ;

A procedure can never be used in a sql statement.

A Trigger is executed automatically based on an event from the user.

Here is the hierarchy of triggers
_____________________________________________
CREATE OR REPLACE TRIGGER
_____________________________________________
          BEFORE | AFTER                             
                ||
                ||
                \/
        DELETE|INSERT|UPDATE
                ||
                ||
                \/
    Foreach row | statement level
_____________________________________________

Here is how to apply the above information :

Create or replace trigger
before delete on table_name
for each row as 
begin
null;
end;

Function, Procedure and Trigger difference

The fundamental difference between lies in the storage of
these objects, procedures and functions are stored in compiled form
however a trigger's text is pulled from the database compiled and then
executed each time when it fires.

When a underlying object is changed for any of these oracle automatically
recompiles the first time the objects are referenced.

When a Procedure or a function is created we have to grant explicit
access to other users the execute permission however in case of triggers
the access permissions are tied to the table itself, if a user has insert,
delete, update permission then when a row in the table is modified the trigger
will be executed automatically.

Hosted by www.Geocities.ws