GeekInterview.com
Series: Subject: Topic:
Question: 463 of 490

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.
Asked by: Interview Candidate | Asked on: Sep 2nd, 2005

Editorial / Best Answer

Answered by: krishnaindia2007

View all answers by krishnaindia2007

Member Since Sep-2007 | Answered On : 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

Answered On : Sep 2nd, 2005

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

  
Login to rate this answer.
Sunil Yadav

Answered On : 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 .

Yes  1 User has rated as useful.
  
Login to rate this answer.
promila

Answered On : Nov 10th, 2005

View all answers by promila

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

  
Login to rate this answer.
Zafar Imam Khan

Answered On : 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.

  
Login to rate this answer.
marisamy

Answered On : Dec 2nd, 2005

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

  
Login to rate this answer.
Haripriya

Answered On : Dec 23rd, 2005

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

  
Login to rate this answer.

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

  
Login to rate this answer.

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.

  
Login to rate this answer.
jaidev_ncc

Answered On : Mar 17th, 2006

View all answers by jaidev_ncc

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

  
Login to rate this answer.

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

  
Login to rate this answer.
Deepika S Verma

Answered On : 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

Yes  1 User has rated as useful.
  
Login to rate this answer.
Vikas Rathore

Answered On : Apr 13th, 2006

You can use DDL stmt inside function.

  
Login to rate this answer.
Krishna Gupta

Answered On : 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.  

  
Login to rate this answer.
Rajasekar

Answered On : 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.

  
Login to rate this answer.
kiran

Answered On : 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.

  
Login to rate this answer.
Muhammad Asif Siddiqui

Answered On : 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.

  
Login to rate this answer.
gautam

Answered On : Dec 13th, 2006

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

  
Login to rate this answer.
Rajesh

Answered On : Feb 19th, 2007

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

  
Login to rate this answer.
Sujaatha

Answered On : 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....

  
Login to rate this answer.
think

Answered On : 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

  
Login to rate this answer.
abhi

Answered On : 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 whileprocedures are used to performa task.Functions can have out parameter which can take the return type

  
Login to rate this answer.
prabhat saxena

Answered On : Jun 15th, 2007

By default function are return type where as procedure can'tFunction support the select statement where as procedure can't Procedure support DML, DDL where as function can't

  
Login to rate this answer.
narendra

Answered On : 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.

  
Login to rate this answer.
Srinivasa Reddy

Answered On : Jul 17th, 2007

  
Login to rate this answer.
sureshbala

Answered On : Aug 14th, 2007

View all answers by sureshbala

  
Login to rate this answer.
madhu

Answered On : Oct 31st, 2007

Hi We can use execute immediate in the function also

  
Login to rate this answer.
Vijay Jadhav

Answered On : Nov 26th, 2007

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

  
Login to rate this answer.
malik.aman

Answered On : Dec 4th, 2007

View all answers by malik.aman

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

  
Login to rate this answer.
g_sidhu

Answered On : 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

  
Login to rate this answer.

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

Yes  1 User has rated as useful.
  
Login to rate this answer.
vinothkumar.g

Answered On : Sep 30th, 2011

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

  
Login to rate this answer.
MANIKANDAN

Answered On : 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.

  
Login to rate this answer.
Susil Kumar Nagarajan

Answered On : 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.

  
Login to rate this answer.
jagat

Answered On : 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

  
Login to rate this answer.
kane alferos

Answered On : Jun 4th, 2012

You can use dml function in procedure

  
Login to rate this answer.
Kiran Marla

Answered On : 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.

  
Login to rate this answer.
Arun Kaushik

Answered On : Oct 1st, 2012

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

  
Login to rate this answer.
Swapnil

Answered On : 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.

  
Login to rate this answer.
Md Jamshaid

Answered On : Dec 26th, 2012

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

  
Login to rate this answer.
Shalini

Answered On : 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

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.