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)
Is this answer out of date? If it is, please let us know via a Comment