21

I tried to looked in to difference between pl/sql procedure and function and found the link http://it.toolbox.com/blogs/oracle-guide/learn-plsql-procedures-and-functions-13030. First let me tell you what a developer generally do with pl/sql procedure and function

1) Wanted to get the some return value. He can acieve it with both function and procedure .With function if he want to return a single value he can use return statement . If he want to return multiple values he can achieve it with inout parameter.Similarily he can get return value with inout parameter from procedure(not with return statement)

But it does not make any difference to developer as long as he is able to achieve its intentention either with return statement or inout parameter.

so here also both can replace each other.

2) He can use DML in both Function and procedure. So here also he can use either of these to change the state of databse.

So i dont get any concrete reasoning which one to use where as both can replace each other in some.

The only reasonable reason i found up to some extent is that Functions can be called from SQL, procedure cannot

Could somebody explain which one to use when and why?

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
M Sach
  • 33,416
  • 76
  • 221
  • 314
  • See also [Functions vs Stored Procedures](https://forums.oracle.com/forums/thread.jspa?messageID=547397) on Oracle.com – dee-see Sep 01 '11 at 15:23
  • @Mohit - I beleive a similiar question has been asked before. Possible Dup http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server – Jon Raynor Sep 01 '11 at 15:23
  • @Vache i found this statement 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 at https://forums.oracle.com/forums/thread.jspa?messageID=547397 not sure if it is really true? – M Sach Sep 02 '11 at 18:13
  • I think this must have been asked about once per month for the last 25 years, apparently by people new to both computing and mathematics. – William Robertson Nov 07 '17 at 08:13
  • one of many answers: https://stackoverflow.com/questions/771949/what-is-the-difference-between-function-and-procedure-in-pl-sql/51917404#51917404 – Mateen Aug 19 '18 at 11:58

5 Answers5

14

You already found the main difference. You create a function if you want to use it in SQL. You create a procedure, when you want to use it only in PL/SQL.

Daniel Hilgarth
  • 171,043
  • 40
  • 335
  • 443
  • So i can assume i should always use procedure until and unless i want to use them in sql query – M Sach Sep 02 '11 at 18:09
  • 9
    I think that's a good rule of thumb. I would phrase it a bit differently: If you need to transform zero or more input values to one output value, use a function. If you want to manipulate data in the database, use a stored proc. – Daniel Hilgarth Sep 02 '11 at 18:31
  • I would also use a Function if I want only to use PL/SQL. This way if I need to reuse the logic in SQL any time in the future it is completely trivial. Using procedures has only one advantage: it allows you to return more than one parameter. – borjab Jan 30 '15 at 14:49
4

What I do. Use functions if there aren't side effects, procedures otherwise.

Moreover, only functions may be "pure"(suitable for function indexes) and "pipelined".

Samuel
  • 2,430
  • 2
  • 19
  • 21
1

There are main two different:

1:Use Procedure to take some action. But use function to return some value. 
2:You can call function from sql query but Procedure can't. 
3:Best practice to use Procedure then function if possible.

Thanks.

MrYo
  • 1,797
  • 3
  • 19
  • 33
0

A procedure and a function have the same structure, except that:

A function heading must include a RETURN clause that specifies the data type of the return value. A procedure heading cannot have a RETURN clause.

A function must have at least one RETURN statement in its executable part. In a procedure, the RETURN statement is optional. For details, see RETURN Statement.

For more information refer to: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/subprograms.htm#CHDDCFHD

Jay
  • 11
  • 1
  • Yes, I think this is the best answer. Also You should think use functions when You don't want to use OUT and IN OUT mode parameters. If there any reason to use these parameters You should think seriously use a stored procedure. –  Feb 09 '17 at 01:49
0

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/subprograms.htm#i4079

Overview of PL/SQL Subprograms A PL/SQL subprogram is a named PL/SQL block that can be invoked with a set of parameters. A subprogram can be either a procedure or a function. Typically, you use a procedure to perform an action and a function to compute and return a value.

Dmitriy Buldakov
  • 41
  • 3