What is difference between a PROCEDURE & FUNCTION ?

functions can be useed in select or update or delete statement while procedure can't.

A Function can be used in the Sql Queries while a procedure can not be used in sql queries .that cause a major difference b/w function and procedures .

We can use DDL in Procedure using Execute Immediate statement while tht is not possible in functions

A function always return a values while procedure can return one or more values through Parameters.

A function can call directly by sql statement like select "func_name" from dual while procedure cannot.

Dml statement cannot be used in function,but it used in procedure.

A function returns a value where as a proceedure does not return a value

procedure can call in another project but function work in same project . 

We can't have any DDL,DML and TLC command inside a function, if that function is called from a query.But if the function is not called from query then we can have all transactional statement(DDL,DML and TLC ) inside a function.

function can return a single values at maximum, where as in case if procedure returns one or more than one value and might not return a even a single value........

Moral of the story is that,
Functions can be part of any valid Pl/SQL Expression but
Procs cannot be .. We need to call procs standalone...:)

The Main Difference Between function and procedure is

[1] function return value whereas procedure can return one , more than one or no value

[2] the other difference which is a advantage of function over procedure is

function can be called in a SQL statement as a UDF(user defined function) but procedure cannot

Also You can use DDL statements in functions & procedure by using execute_immediate package in latest version and for parse_sql packaege in old oracle version. So regarding using of DDL or DML statments in function or procedure , there is no difference in that context

you can use DDL stmt inside function.

1. A function return single value through return statement but procedure can return one or more than one values through parameter.

2. function  use through select statement while procedure can not.

  

Functions are basically pre-compiled, but procedures are not. Thats why we are able to call functions from select statement but not procedure. In that case, functions are fater than proceduers. Please correct me if i am wrong.

A Function can return a single value using return statement, whereas a Procedure cannot return using return statement but can return using parameters, i.e., a procedure can return one or more values.A Function can be called inside the select statement, whereas the Procedures cannot called inside the select statement,procedures are standalone.

A can not be executed or exist as a standalone entity while PROCEDURE hasn't got any of such limitations. A PROCEDURE can contian one or more FUNCTION.

Function can create and return a table where as procedure can not return a table

function and procedure both return value But fuction return onlyfor caller. PROCEDURE returns for all of them.

Both are nothing but set of statement which will perform some operations... More over both are same...in C we call it as functions.. in Pascal & COBOL..we call it as procedures..But the d/f b/w this two is function will return a value to call function...but procedure wont return any value to call function....

both are precompiled but act differently like for exe: functions can be called at any point of queries but u cant do the same with procedure and so on

Procedures are stored in precompiled form and hence are faster than function



Function occupies less memory space as compared to procedure.



We cant use DDL, DML, TCL statements within a fuction if it is called by some SQL query or if it is used in some SQL query.



Fuctions are used to return a value whileprocedures are used to performa task.

Functions can have out parameter which can take the return type

By default function are return type where as procedure can't

Function support the select statement where as procedure can't

Procedure support DML, DDL where as function can't

Hi Rajasekhar,
What you are saying is wrong. Both functions and prcodures are pre complied statements. Even we can call a procedure or function in any package body if they are declared properly.

Procedure take in, out, inout parameters,
Function accept only in parameter only.
Toexecute procedures parameters required
Toexecute functions parameter optional

The following solutions are possible

1) A function should return a value as default. whereas a procedure may or may not return a value in demand.
2) Functions can be integrated in a SQL select statement. But it cannot be applied to procedures.
3) A function can becalled inside a procedure whereas the vice versa is not applicable.

Hi We can use execute immediate in the function also

Function should return atlestsingle value (it is a barden), wherePROCEDURE is not.

Function can return at most one value at a time while procedure can return more than one value at a time You can use function within Sql Statement and you cannot use stored procedure with in Sql Statement

Procedures:

Execute as a PL/SQL statement
Do not contain RETURN clause in the header
Can return none, one, or many values
Can contain a RETURN statement

Functions:

Invoke as part of an expression
Must contain a RETURN clause in the header
Must return a single value
Must contain at least one RETURN statement

1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.

2. Function can be called from SQL statements where as procedure can not be called from the sql statements

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

4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.

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

6.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.

7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values

8. Stored procedure is precompiled execution plan where as functions are not.

9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.

one most important diff is
-> In procedure exception handle is mandatory but in function Exception handling is not mandatory.

1. Procedure and function both used to perform some mathematical and business calculation.

2. Procedure may return one or more values(max 1024) through parameter but function must return only one values through return statements.

3. Function can use DDL and DML SQL statements, But procedure used for calculation and updation operation.

1. A function is expected to return values and can be assigned to a variable

Example : substr() is a string function which is expected to return a value
Can be assigned to a variable:
var1 := substr(Susil Kumar,1,5);

Procedure cannot be assigned to a variable.

2. A function can be called inside a procedure. Reverse is not possible unless you use Execute Immediate to generate dynamic code which is executed during runtime.

3. Global variables can only be declared in Pacakage

4. If you want to do overloading of functions or procedures, you should seal them in a package. If not, you cannot overload functions or procedures

5. Though functions allow insert statements in it, it throws error when you use the function in select statement. Procedure goes good with Insert statements.

The Main Difference Between function and procedure is

[1] function return value whereas procedure can return one , more than one or no value

[2] the other difference which is a advantage of function over procedure is

function can be called in a SQL statement , but procedure cannot

Also You can use DDL statements in functions & procedure by using execute_immediate package in latest version and for parse_sql package in old oracle version. So regarding using of DDL or DML statements in function or procedure , there is no difference in that context

you can use dml function in procedure

Function can return more than One value with using OUT Parameter like Procedure.
Function needs to check the purity levels by pragma restrict_references(Function_name, WNPS,RNPS,WNDS,RNDS) done automatically by PL/SQL Engine while procedures do not need.

Main difference is procedure may or may not return a value, but Function must return.

Also Procedure can be called from triggers but Function cannot be called from trigger. As function returns value. And no value can be returned to trigger.

What is the difference between system procedure and system function?.