Oracle Stored Procedures Vs Functions

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

---------------------------------------------------------------------------

1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
 
2. Function can be called from SQL statements where as procedure can not be called from the sql statements 

3. Functions are normally used for computations where as procedures are normally used for executingbusiness logic. 

4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.

5. Function returns 1 value only. Procedure can return multiple values (max 1024). 

6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution. 

7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values

8. Stored procedure is precompiled execution plan where as functions are not.
 
9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.