1

Possible Duplicate:
What is the difference between function and procedure in PL/SQL?

Functions always return a value, however when we use 'out' in a procedure that procedure also returns a value so please tell me when should we write a function and when should we write a procedure? What are the criteria on which we decide whether to use a procedure or a function?

Community
  • 1
  • 1
ratsy
  • 575
  • 4
  • 7
  • 13
  • 1
    Also on a generic note: [What is the difference between a 'function' and a 'procedure'?](http://stackoverflow.com/questions/721090/721107#721107) – Sathyajith Bhat Dec 19 '11 at 07:20

1 Answers1

5

Functions mostly do computations of some kind, and always return a value using the return statement, and can be used in a select statement. Procedures are used to implement business logic and can return one or more values using the out parameter(s). You should not do DML in functions.

Rob van Laarhoven
  • 8,737
  • 2
  • 31
  • 49
  • 2
    If you do use DML in a function, you cannot use it in a select statement else you get ORA-14551. – DCookie Dec 19 '11 at 04:44
  • Thanks obert & DCookie...so i should ignore statements which always say 'procedures dont return a value' since this is the answer generally everybody gives when asked abt difference betn function & procedure wrt oracle..okay any logic may be implemeted by both..d only difference would be functions cannot be used in select statements(f DMl is there in function)...thanks for the answers...I dont know ahy my question was closed since its not simi;ar to questions already asked on this forum... – ratsy Dec 19 '11 at 20:45
  • You should not ignore the statement but try to understand the meaning/implications of the statement. Procedures do not return a value. The OUT and IN OUT parameters of a procedure can contain a value. So x:=function_x(); is OK but x:=procedure_x(a,b,c); is NOTOK. – Rob van Laarhoven Dec 20 '11 at 08:09
  • Yes, you are absolutely right..thanks for clarifying this in your above answer, but can we write code in a way where output then would be passed to any variable in a procedure or outside it? in that way it would just matter of choice whether we choose a functn or proc? sorry for asking again....thanks.. – ratsy Dec 20 '11 at 20:52
  • If you want to do computations on variable x you can create a function with parameter x do the computations in the function and return the new value OR create a procedure with IN OUT parameter x and change value of x in de procedure. So calling it would be: x = function_y(x); OR procedure_y(x); – Rob van Laarhoven Dec 21 '11 at 14:01
  • A crystal clear answer, thanks a lot..thats what i wanted to clarify..Thanks Robert..would get back if wud have any doubt again..btw deviating from this question..am new to this forum,can we add people to our account here? or we need have certain points to do that..thnxx again – ratsy Dec 21 '11 at 21:18
  • person:account is a 1:n relation – Rob van Laarhoven Dec 22 '11 at 08:43
  • oh,i meant whether one can add somebody as buddy here,like we do in yahoo answers on some other forums, so that if need be, we could directly ask any urgent query to them? pardon my bad english..wont eat ur brains more :) thanks a lot lot for ur help though...:) – ratsy Dec 23 '11 at 21:12