Functions vs procedures in Oracle

Question

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 supporting detail.

Answer 1

This is a great question and as far as I can tell has not really been answered. The question is not "What's the difference between a function and a procedure?" Rather, it is "Why would I ever use a procedure when I can do the same thing with a function?"

I think the real answer is "It's just convention." And as it's convention, it's what other developers are used to and expect, so you should follow the convention. But there is no functional reason to write a subprogram as a procedure over a function. The one exception may be when there are multiple OUT parameters.

In his 6th edition of Oracle PL/SQL Programming, Steven Feuerstein recommends that you reserve OUT and IN OUT parameters for procedures and only return information in functions via the RETURN clause (p. 613). But again, the reason for this is convention. Developers don't expect functions to have OUT parameters.

I've written a longish post here arguing that you should only use a procedure when a function won’t do the job. I personally prefer functions and wish that the convention was to use functions by default, but I think a better practice is to accept the things I cannot change and bow to the actual convention and not the one I would wish for.

Answer 2

State-changing vs non-state-changing

On top of Romo Daneghyan's answer, I've always viewed the difference as their behaviour on the program state. That is, conceptually,

• Procedures can change some state, either of the parameters or of the environment (eg, data in tables etc).
• Functions do not change state, and you would expect that calling a particular function would not modify any data/state. (Ie, the concept underlying functional programming)

Ie, if you called a function named generateId(...), you'd expect it to only do some computation and return a value. But calling a procedure generateId ..., you might expect it to change values in some tables.

Of course, it seems like in Oracle as well as many languages, this does not apply and is not enforced, so perhaps it's just me.

Answer 3

i think the major difference is :

Functions can not contain DML Statemnt whereas the procedures can. for example like Update and Insert.

if i am wrong correct me

Answer 4

1. Procedure may or may not return value but functions return value.

2. procedure use out parameter returnvalue purpose but function returnstatment provide.

3. procedure used manipulation of data but function use calculation of data.
4. procedure execution time not use select statement but function use select statement. These are major difference of it.

Answer 5

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.

Answer 6

There is almost never a performance difference between procedures and functions.

In a few extremely rare cases:

• A procedure IN OUT argument is faster than a function return, when inlining is enabled.
• A procedure IN OUT argument is slower than a function return, when inlining is disabled.

Test code

--Run one of these to set optimization level:
--alter session set plsql_optimize_level=0;
--alter session set plsql_optimize_level=1;
--alter session set plsql_optimize_level=2;
--alter session set plsql_optimize_level=3;

--Run this to compare times.  Move the comment to enable the procedure or the function.
declare
    v_result varchar2(4000);

    procedure test_procedure(p_result in out varchar2) is
    begin
        p_result := '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789';
    end;

    function test_function return varchar2 is
    begin
        return '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789';
    end;
begin
    for i in 1 .. 10000000 loop
        --Comment out one of these lines to change the test.
        --test_procedure(v_result);
        v_result := test_function;
    end loop;
end;
/

Results

Inlining enabled:  PLSQL_OPTIMIZE_LEVEL = 2 (default) or 3
Function  run time in seconds: 2.839, 2.933, 2.979
Procedure run time in seconds: 1.685, 1.700, 1.762

Inlining disabled: PLSQL_OPTIMIZE_LEVEL = 0 or 1
Function  run time in seconds:  5.164, 4.967, 5.632
Procedure run time in seconds: 6.1, 6.006, 6.037

The above code is trivial and perhaps subject to other optimizations. But I have seen similar results with production code.

Why the difference doesn't matter

Don't look at the above test and think "a procedure runs twice as fast as a function!". Yes, the overhead of a function is almost twice as much as the overhead of a procedure. But either way, the overhead is irrelevantly small.

The key to database performance is to do as much work as possible in SQL statements, in batches. If a program calls a function or procedure ten million times per second then that program has serious design problems.

Answer 7

As I know, Store procedure is compiled once and can be called again and again without compiled again. But function is compiled each time called. So, Store procedure improves performance than function.