0

If we have only Out parameter in our PLSQL procedure.Then can we use function instead of procedure as function is also able to return the value. And if we still using procedure then we use this instead of function.

I hope I am able to convey the right question which I want to ask?

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Mogit
  • 7
  • 1
  • Well, question is a bit unclear. Stored procedure isnt returning any value, and function have to return some value. Of course you can use OUTPUT parameters in both: Function and Stored Procedure. So basicly instead of a function you could use Stored procedure, with for example one OUTPUT parameter, but its harder to understand what stored procedure does, and multifunctionality of one function is against object oriented programming. What is more you cant use stored procedure to return something in SQL query. If you could give more detailed info about your problem, we could help you better. – Bartosz Olchowik Jun 15 '20 at 11:42
  • Thanks Bartosz for response. I just want to know , as I am using very basic procedure having out parameter , so may I use function instead of using procedure as I am just getting output. – Mogit Jun 15 '20 at 11:59

2 Answers2

1

It depends on what the procedure does.

For example, if it (along with returning some value) uses DML operations (e.g. inserts rows into some table), then function can't do that - you'll have to use a procedure.

Procedure's drawback is that you can't use it in a SELECT statement, such as

select proc_name(out_param) from dual;

You'll have to use a function in such cases.

Also, OUT parameter has to be stored somewhere, and that's usually a locally declared variable, but that means that you need another PL/SQL block to call the procedure and see its result.

If everything your current procedure does is to find & return some value, then yes - a function might be a better choice.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
1

Some important difference between both are as following:

Function:

  • It can be called from the SQL statement (SELECT, UPDATE, DELETE)
  • Can return only one value
  • DML operations are not allowed in it
  • Best for selecting the value for some common complex logic.

Procedure:

  • It cannot be called from the SQL statement. You must need the PL/SQL block to call it.
  • Can return multiple values (OUT parameters)
  • All DML operations are allowed within procedures.
  • Best for doing some complex logic and updating the table data accordingly.
Popeye
  • 35,427
  • 4
  • 10
  • 31