Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PL/Sql procedure vs function? [duplicate]

Tags:

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?

like image 476
M Sach Avatar asked Sep 01 '11 15:09

M Sach


People also ask

What is the difference between procedure and functions in PL SQL?

A procedure is compiled once and can be called multiple times without being compiled. A function returns a value and control to calling function or code. A procedure returns the control but not any value to calling function or code. A procedure has support for try-catch blocks.

Which is faster procedure or function?

There is no difference in speed between a query run inside a function and one run inside a procedure. Stored procedures have problems aggregating results, they cannot be composed with other stored procedures.

What is the difference between Oracle procedure and function?

The difference is- A function must return a value (of any type) by default definition of it, whereas in case of a procedure you need to use parameters like OUT or IN OUT parameters to get the results. You can use a function in a normal SQL where as you cannot use a procedure in SQL statements.

What is the difference between procedure and function?

A function would return the returning value/control to the code or calling function. The procedures perform certain tasks in a particular order on the basis of the given inputs. A procedure, on the other hand, would return the control, but would not return any value to the calling function or the code.


1 Answers

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.

like image 91
Daniel Hilgarth Avatar answered Sep 22 '22 02:09

Daniel Hilgarth