Oracle functions and procedures

[vellas]

what r the differences between function and procedure?

[tsanthi]

Hi,
The Function should return a value and u can use the function as part of your SQL statement.
Where as Procedure, we can not use as part of SQL statement. By using the Out Params u can get more than one value.

[tsunil10]

function can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Of course there will be Syntax differences .

[tsunil10]

1. Procedure takes both input and output parameters but Functions takes only input parameters
2. Functions cannot return values of type text, ntext, image & timestamps where as procedures can
3. Functions can be used as user defined datatypes in create table but procedures cannot

[jegathes]

procedure -->
it execute as pl/sql stmt
NO return clause in the header
Can return none, one or many values,
Can contain a return stmt

function -->

invoke as a part of an expression,
must contain a return clause in the header
Must return a single value
must contain at least one return stmt

[Alok.Microsoft]

A function always returns a value, while a procedure does not. When you call a function you must always assign its value to a variable

[Alok.Microsoft]

Functions are normally used for computations where as procedures
are normally used for executing business logic.

Functions MUST return a value, procedures doesn't need to.


You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
For example, if you have a function that is updating a table, you cannot call that function from a SQL query.
- select myFunction(field) from sometable; will throw error.


Function returns 1 value only. Procedure can return multiple values (max 1024).


Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution. Stored procedure returns always integer value by default zero. where as function return type could be
scalar or table or table values(SQL Server).
Stored procedure is precompiled execution plan where as functions are not.

[Alok.Microsoft]

Functions can return value while procedure i mean vb procedures cannot return value

[debasisdas]

in oracle function must return a value buit a procedure may or may not returna value or might return more than one value using the out parameter.

But if you want to return value then,function should be used.