how differentiate between function and procedure?

In ORACLE where you are creating function, you can create Procedure and vice-versa?Can someone explain the practical sceneria where Procedure will work not Function or Vice-Versa?:confused:

Use a function whenever you want to return a value.

In some scenarios you wish to trap the return value when a function execution is completed and based on the return value proceed further.

At these scenarios a procedure cannot be used as it only executes a pre-defined set of steps.

But you can use RETURN keyword with procedure,so there is no difference at all in the functioning....

1) Both are strored subprogrames
2) functions can return a value back to the calling environment by its return statement
3) procedures can not have any return statement ,but it can return more values via multiple OUT parameters to the calling programme
4) function can be called from SQL statemetns
5) procedure can not be called from the sql statments
6) we can also use the OUT parameters in functions

[QUOTE=Sant_parkash;19818]But you can use RETURN keyword with procedure,so there is no difference at all in the functioning....[/QUOTE]


Yes ur right.but Acc to their syntax...we can not use any RETURN Statement in procedural declaration..I think it may be in the body section

[QUOTE=Sant_parkash;19818]But you can use RETURN keyword with procedure,so there is no difference at all in the functioning....[/QUOTE]

You can't use RETURN keyword to return any value out of the function. 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. Where you want to retun a value it is strongly recommended to use a function not a procedure even though you can use it with OUT parameters..

Functions are normally used for computations where as procedures
are normally used for executing business logic.

Functions MUST return a value, procedures doesn't need to.

You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
For example, if you have a function that is updating a table, you cannot call that function from a SQL query.
- select myFunction(field) from sometable; will throw error.

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

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. Stored procedure returns always integer value by default zero. where as function return type could be
scalar or table or table values(SQL Server).
Stored procedure is precompiled execution plan where as functions are not.

A procedure may modify an object where a function can only return a value.

function return something whereas procedure dont return anything