The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Thanks for the question, Vivek.

Asked: June 07, 2018 - 8:16 pm UTC

Last updated: June 11, 2018 - 7:22 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

Difference between Procedure and function(at least 5, if there are)

Seems like a basic question but its a very tricky question..

Some of the differences which I encountered on the internet seems incorrect later, I will list some of them below.

like..

* function returns 1 value (I found out later that function can also return multiple values using type)
* we cannot perform dml operations in functions( Later i found out that we can perform dml operations in function as well)

* we cannot use OUT parameter in function(it seems we can use OUT parameter in function as well)

Hence my confusion.. :)

Request you to kindly state all the differences between procedure and function.

Thanks

and Connor said...

There is plenty of overlap, but I choose the make the distinction as an interpretation of the inputs:

- a function takes a set of inputs, and *derives* something based on those inputs. So typically it would not *alter* the state of anything, it merely comes up with a new value. It "answers a question" so to speak, based on the inputs.

- a procedure takes a set of inputs and *performs* something based on those inputs. So those inputs are used to *do* something, rather than *derive* something.

That's why (even though you *can* do it), I would not have an OUT parameter in a function. Similarly, its pretty rare for me to perform DML in a function, unless the job of the function is return the outcome of that DML attempt/action.

One obvious difference (a consequence of the function always *returning* something), and that is, you can call a function from SQL, and cannot do so with a procedure.

Rating

(1 rating)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Can you tell me a very very very unique difference between function and procedure?

A reader, July 05, 2022 - 8:11 pm UTC

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer