Functions vs procedures in Oracle

Question

can anybody explain what is the main difference between functions and procedures in Oracle? Why must I use procedures if I can do everything with functions?

1. If I cannot call procedure in sql statement, ok, I'll write a function to do the same work.
2. Procedures don't return values, ok, I'll return only sql%rowcount or 1(success), 0(exception) after any dml operation
3. Both procedures and functions can pass variables to calling environment via OUT/IN OUT parameters

I heard that the main difference is in performance, 'procedures are faster than functions'. But without any detail.

Thanks in advance.

Answer 1

The difference is- A function must return a value (of any type) by default definition of it, whereas in case of a procedure you need to use parameters like OUT or IN OUT parameters to get the results. You can use a function in a normal SQL where as you cannot use a procedure in SQL statements.

Some Differences between Functions and Procedures

1. A function always returns a value using the return statement while a procedure may return one or more values through parameters or may not return at all.Although, OUT parameters can still be used in functions, they are not advisable neither are there cases where one might find a need to do so. Using OUT parameter restricts a function from being used in a SQL Statement.

2. Functions can be used in typical SQL statements like SELECT, INSERT, UPDATE, DELETE, MERGE, while procedures can't.

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

4. Oracle provides the provision of creating "Function Based Indexes" to improve the performance of the subsequent SQL statement. This applies when performing the function on an indexed column in where clause of a query.

More Information on Functions Vs. Procedures here and here.