What is the difference between a function and a stored procedure in Oracle?

Procedures are traditionally the workhorse of the coding world and functions are traditionally the smaller, more specific pieces of code. In general, if you need to update the chart of accounts, you would write a procedure. If you need to retrieve the organization code for a particular GL account, you would write a function.

Here are a few more differences between a procedure and a function:

A function MUST return a value
A procedure cannot return a value
Procedures and functions can both return data in OUT and IN OUT parameters
The return statement in a function returns control to the calling program and returns the results of the function
The return statement of a procedure returns control to the calling program and cannot return a value
Functions can be called from SQL, procedure cannot
Functions are considered expressions, procedure are not

 

Thanks

Vijaya Kadiyala

http://dotnetvj.blogspot.com

Function :

1. Should return atleast one output parameter.Can return more than one parameter using OUT argument.

2. Parsed and compiled at runtime.

3.Cannot affect the state of database.

4.Can be invoked from SQL statement e.g. SELECT.

5. Functions are mainly used to compute values.

Procedure:

1. Doesn't need to return values, but can return value.

2.Stored as a pseudo-code in database i.e. compiled form.

3.Can affect the state of database using commit etc.

4.Cannnot be invoked from SQL statements e.g. SELECT.

5.Procedures are mainly used to process the tasks.