Taral Desai

• Sep 2nd, 2005

 

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

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

 Profile Answers by promila 

• Nov 10th, 2005

 

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

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.

marisamy

• Dec 2nd, 2005

 

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

Haripriya

• Dec 23rd, 2005

 

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

prem123

 Profile Answers by prem123 Questions by prem123

• Mar 8th, 2006

 

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

aseemnaithani

 Profile Answers by aseemnaithani Questions by aseemnaithani

• Mar 15th, 2006

 

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.

jaidev_ncc

 Profile Answers by 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........

prafull.vn

 Profile Answers by prafull.vn Questions by prafull.vn

• Mar 19th, 2006

 

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

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.

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.

  

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.

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.

gautam

• Dec 13th, 2006

 

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

Rajesh

• Feb 19th, 2007

 

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

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

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

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

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

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.

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

sureshbala

 Profile Answers by 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.

madhu

• Oct 31st, 2007

 

Hi We can use execute immediate in the function also

Vijay Jadhav

• Nov 26th, 2007

 

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

malik.aman

 Profile Answers by 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

g_sidhu

 Profile 

• 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

krishnaindia2007

 Profile Answers by krishnaindia2007 

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

vinothkumar.g

• Sep 30th, 2011

 

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

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.

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.

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

kane alferos

• Jun 4th, 2012

 

you can use dml function in procedure

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.

Arun Kaushik

• Oct 1st, 2012

 

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

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.

Md Jamshaid

• Dec 26th, 2012

 

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

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

Giri

• Dec 2nd, 2015

 

DML statement can be used in function also.

Narendra.rudra

• Jul 8th, 2016

 

Procedure is compiled once, but function is compiled every time you call the function.

qptopm

 Profile Answers by qptopm 

• Nov 3rd, 2016

 

Procedure can perform one or more tasks where as function performs a specific task.

Procedure may or may not return value where as function should return one value.

Function can be called from SQL statement where as procedure cant be called from the SQL statement.

Function are normally used for computation where as procedure are normally used for executing business logic.

Pramod

• Nov 17th, 2016

 

DML statement are used in procedure

pankaj

• Jan 13th, 2017

 

Hi , We can use execute immediate in function also

Code
CREATE OR REPLACE FUNCTION abc_fun RETURN NUMBER AS 


BEGIN 


RETURN 1;


END;


 


 


DECLARE


a NUMBER;


BEGIN a:= abc_fun;


DBMS_OUTPUT.put_line(a);


END;

Debadatta

• May 27th, 2017

 

DML can be used in function but we cant call that function directly in SELECT statement.
For calling that function in SELECT statement use pragma_autonomous transaction.

Pavan

• Jul 27th, 2017

 

function works at variable level where as procedures works at dataset level

mangesh patil

• Aug 22nd, 2017

 

Procedure may or may not return value where as function should return one value.

mahendra pal

• Sep 18th, 2018

 

procedure may or may not return value