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 - 44 of 44 Answers

Taral Desai

Sep 2nd, 2005

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

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.

Deepika S Verma

[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

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.

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.

Rajesh

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

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.

Vijay Jadhav

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

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.

jagat

[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

kane alferos

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.

Md Jamshaid

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

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.