Procedures:

A Procedure is a named PL/SQL block with declarative section, an executable section and an exception handling section. Procedures are stored in the data dictionary and are executed whenever they are invoked from another PL/SQL block. Procedures can be called from any PL/SQL block and arguments can be passed while calling. A procedure call is a PL/SQL statement and cannot be a part of an expression.

The syntax for creating a procedure is as below:

CREATE [OR REPLACE] procedure procedure_name
[(argument_1[{IN/OUT/IN OUT}] data_type,
…
…
…
…
argument_n[{IN/OUT/IN OUT}] data_type)] {IS/AS}
/*declarative section*/
BEGIN
/*executable section*/
EXCEPTION
/*exception section*/
END [procedure_name];

Below is an example of creating a procedure:

Figure -2 Procedure Creation

Functions:

A Function, just like a procedure, is a named PL/SQL block that has an executable section and an exception handling section. Functions also, are stored in the data dictionary and can be called from other PL/SQL blocks when required. Functions can also have parameters and the arguments can be passed while calling them.

There are some differences between procedures and functions though. Functions can only be called as a part of an expression (as opposed to procedure calls which is a PL/SQL statement by itself). Function body contains a ‘return’ statement that returns the result of function execution to the calling expression. So, the function has a return type that has to be declared in the function header.

The syntax for creating a function is as below:

CREATE [OR REPLACE] FUNCTION function_name
[( argument_1 [{IN/OUT/IN OUT}] data_type,
…
…
…
…
argument_n [{IN/OUT/IN OUT}] data_type,)]
RETURN return_type {IS/AS}
/*declarative section*/
BEGIN
/*executable section*/
RETURN expression;
EXCEPTION
/*exception section*/
END [function_name];

Below is an example of creating a function:

Figure -3 Function Creation

First Page: Oracle 10g Tutorials - Identifying PL/SQL Objects