What is difference between a PROCEDURE & FUNCTION ?

 A FUNCTION is always returns a value using the return statement.        A  PROCEDURE   may  return one or more values through parameters or may not return at all.

Editorial / Best Answer

krishnaindia2007  

  • Member Since Sep-2007 | May 3rd, 2008


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.

Showing Answers 1 - 40 of 40 Answers

Taral Desai

  • Sep 2nd, 2005
 

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

  Was this answer useful?  Yes

Sunil Yadav

  • Oct 19th, 2005
 

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 .

promila

  • Nov 10th, 2005
 

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

  Was this answer useful?  Yes

Zafar Imam Khan

  • Nov 11th, 2005
 

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.

  Was this answer useful?  Yes

marisamy

  • Dec 2nd, 2005
 

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

  Was this answer useful?  Yes

Haripriya

  • Dec 23rd, 2005
 

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

  Was this answer useful?  Yes

jaidev_ncc

  • Mar 17th, 2006
 

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

  Was this answer useful?  Yes

Deepika S Verma

  • Mar 31st, 2006
 

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

Vikas Rathore

  • Apr 13th, 2006
 

you can use DDL stmt inside function.

  Was this answer useful?  Yes

Krishna Gupta

  • Aug 24th, 2006
 

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.

  

  Was this answer useful?  Yes

Rajasekar

  • Sep 29th, 2006
 

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.

  Was this answer useful?  Yes

kiran

  • Oct 17th, 2006
 

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.

  Was this answer useful?  Yes

Muhammad Asif Siddiqui

  • Nov 30th, 2006
 

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.

  Was this answer useful?  Yes

gautam

  • Dec 13th, 2006
 

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

  Was this answer useful?  Yes

Rajesh

  • Feb 19th, 2007
 

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

  Was this answer useful?  Yes

Sujaatha

  • Feb 26th, 2007
 

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

  Was this answer useful?  Yes

think

  • Apr 4th, 2007
 

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

  Was this answer useful?  Yes

abhi

  • May 2nd, 2007
 

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 while procedures are used to perform a task.

Functions can have out parameter which can take the return type

  Was this answer useful?  Yes

prabhat saxena

  • Jun 15th, 2007
 

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

  Was this answer useful?  Yes

narendra

  • Jul 11th, 2007
 

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.

  Was this answer useful?  Yes

Srinivasa Reddy

  • Jul 17th, 2007
 

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

  Was this answer useful?  Yes

sureshbala

  • Aug 14th, 2007
 

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 be called inside a procedure whereas the vice versa is not applicable.

  Was this answer useful?  Yes

madhu

  • Oct 31st, 2007
 

Hi We can use execute immediate in the function also

  Was this answer useful?  Yes

Vijay Jadhav

  • Nov 26th, 2007
 

Function should return atlest single value (it is a barden), where PROCEDURE  is not.

  Was this answer useful?  Yes

malik.aman

  • Dec 4th, 2007
 

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

  Was this answer useful?  Yes

g_sidhu

  • Jan 31st, 2008
 

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

  Was this answer useful?  Yes

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.

vinothkumar.g

  • Sep 30th, 2011
 

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

  Was this answer useful?  Yes

MANIKANDAN

  • Dec 21st, 2011
 

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.

  Was this answer useful?  Yes

Susil Kumar Nagarajan

  • Jan 20th, 2012
 

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.

  Was this answer useful?  Yes

jagat

  • May 28th, 2012
 

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

  Was this answer useful?  Yes

kane alferos

  • Jun 4th, 2012
 

you can use dml function in procedure

  Was this answer useful?  Yes

Kiran Marla

  • Jun 14th, 2012
 

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.

  Was this answer useful?  Yes

Arun Kaushik

  • Oct 1st, 2012
 

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

  Was this answer useful?  Yes

Swapnil

  • Oct 31st, 2012
 

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.

  Was this answer useful?  Yes

Md Jamshaid

  • Dec 26th, 2012
 

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

  Was this answer useful?  Yes

Shalini

  • Jul 23rd, 2014
 

function can be called from trigger.
1.CREATE OR REPLACE TRIGGER emp_trig
2. before INSERT ON emp
3. FOR each row
4.--referencing old as old and new as new
5.declare
6. i pls_integer;
7.begin
8. emp_pro;
9. i := emp_func;
10. dbms_output.put_line(TRIGGER called AND FUNCTION returned || i);
11.
12.end

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions