Skip to Main Content
  • Questions
  • Difference between Procedure and function(at least 5, if there are)

Breadcrumb

Question and Answer

Connor McDonald

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 1000+ times

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 we 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.

Is this answer out of date? If it is, please let us know via a Comment

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