Skip to Main Content
  • Questions
  • Procedure having OUT parameter vs Function

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rahul.

Asked: June 21, 2016 - 5:40 am UTC

Last updated: August 01, 2018 - 12:40 pm UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Thanks for taking up this question.

Are there any guidelines regarding when to use a procedure(OUT parameter) vs Function. Both structures can be used to achieve the same objective in specific situation.
I have created a function F1 and a procedure P1. Both of them simply return a number. Then in the 2 anonymous blocks at the bottom, I have called the function and procedure to print the number.

Which of these is an efficient/better way?

CREATE OR REPLACE FUNCTION f1
RETURN NUMBER
AS
num NUMBER;
BEGIN
SELECT 1 INTO num FROM dual;
RETURN num;
END;
/

CREATE OR REPLACE PROCEDURE p1(
num OUT NUMBER)
AS
BEGIN
SELECT 1 INTO num FROM dual;
END;
/

DECLARE
num1 NUMBER;
BEGIN
num1:=f1;
--p1(num1);
dbms_output.put_line(num1);
END;
/

DECLARE
num1 NUMBER;
BEGIN
--num1:=f1;
p1(num1);
dbms_output.put_line(num1);
END;
/

and Connor said...

Other than the ability to call a plsql function from SQL (which you can't do with a procedure) its probably more of a philosophical debate than a technical one.

A common means via which the distinction is made is what the inputs represent:

- a function takes a set of inputs, and returns 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 changes something based on those inputs. So those inputs are used to *do* something, rather than *derive* something.

Do all procedures and functions map to these definitions ? Of course not :-)

Rating

  (4 ratings)

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

Comments

??

Ghassan, June 21, 2016 - 7:38 am UTC

"its probably more of a philosophical debate than a technical one."

Not really. For example out parameter and nocopy hint are not philosophy topics but technical. And the li diff list is shortless .

Rahul Gupta, June 22, 2016 - 10:21 am UTC

@Ghassan : I asked the question in context of a very specific case.

It was not a question of differences between a Function and Procedure in a generic sense.

I got what I was looking for.
Thanks a lot for taking out time to answer this question, Connor.
Connor McDonald
June 23, 2016 - 1:54 am UTC

GLad we could help out, and thanks for the feedback

Followup

A reader, June 23, 2016 - 5:05 am UTC

Yes first of all countless thanks to Connor and all asktom team for what they do to us.

Still the questions in this site are to be clear for everyone to avoid redondant ones
And I still think that Connor's assertion above still wrong whether or not this responded to a specific need.

Still valid?

A reader, July 31, 2018 - 3:05 pm UTC

Someone says." There is one more difference between stored procedure and functions that stored procedures compiled only once and can be called again and again without being compiled each time, this improves performance and saves time, on the other hand functions compile each time they get called"

Is that true?
If yes is this still true for functions in packages.

Q2 what is your viewpoint if a function is used to di a business stuff like inserting controlling updating data in multiple tables and returning status if exceptions occur. Should keep it or rewrite it as procedure?





Connor McDonald
August 01, 2018 - 12:40 pm UTC

Re 1: Functions are definitely not compiled each time you use them.

Re 2: As I said, *my* preference is not to use functions for that, but I there is not a *technical* reason for not doing so.

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