Home » SQL & PL/SQL » SQL & PL/SQL » difference between functions and stored procedures
difference between functions and stored procedures [message #97682] Fri, 08 November 2002 13:27 Go to next message
saleema
Messages: 1
Registered: November 2002
Junior Member
hi.

could anyone tell me what is the differance between a function and procedure in ORACLE and if I would use functions instead of procedures would it affect theperfromance as comapred to using stored procedures.
Re: difference between functions and stored procedures [message #97689 is a reply to message #97682] Wed, 13 November 2002 11:02 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
functions return a value (or record or pl/sql table or whatever) and procedures dont. Both functions and procedures support in, in/out and out parameters.

Whether you use a function or procedure is often just a matter of programming style. Functions have the advantage of being able to be imbedded in a larger statement. e.g.

x := lower(trim(lpad(v1, 'x', 3)));

If you wanted to set a value you would likely use a procedure:
set_emp_sal('John', 1000);
If you wanted to get the value, you would typically use a function:
x := get_emp_sal('John');
Re: difference between functions and stored procedures [message #98093 is a reply to message #97689] Mon, 09 February 2004 02:08 Go to previous messageGo to next message
Rama lingeshwer reddy
Messages: 1
Registered: February 2004
Junior Member
The above example is fine to tell that when to use a function. but thougth functions are already there what is the need to have a stored procedure?

what are the advantages of stored procedures compare to functions
Re: difference between functions and stored procedures [message #98100 is a reply to message #97689] Fri, 13 February 2004 19:03 Go to previous messageGo to next message
Sarfraz Khan
Messages: 2
Registered: July 2002
Junior Member
I am unable to understand the major difference between Functions and Stored Procedures.And the advantage of Functions over Procedures.
Please clearify if u can.

with all regards
(sarfraz khan)
Re: difference between functions and stored procedures [message #98134 is a reply to message #97689] Wed, 24 March 2004 04:35 Go to previous messageGo to next message
Ritesh Triapthi
Messages: 2
Registered: March 2004
Junior Member
Hi , Thanks for the answer of my question.
Re: difference between functions and stored procedures [message #98145 is a reply to message #97682] Mon, 05 April 2004 00:27 Go to previous messageGo to next message
prakash
Messages: 24
Registered: October 2001
Junior Member
please
Re: difference between functions and stored procedures [message #98245 is a reply to message #98134] Tue, 25 May 2004 21:42 Go to previous messageGo to next message
Venugopal.P
Messages: 1
Registered: May 2004
Junior Member
I need the difference between procedures and functions other than returning the value.
Re: difference between functions and stored procedures [message #98259 is a reply to message #97689] Tue, 01 June 2004 23:29 Go to previous messageGo to next message
muralidhar
Messages: 13
Registered: September 2002
Junior Member
Stored Procedure :supports deffered name resoultion 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 deffered name resolution. Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values(SQL Server).Stored Procedure is pre compiled exuction plan where as functions are not.
Re: difference between functions and stored procedures [message #98315 is a reply to message #98259] Mon, 19 July 2004 02:50 Go to previous messageGo to next message
Jitendra Kumar Prajapati
Messages: 1
Registered: July 2004
Junior Member
Strore Procedure retuns more than one value at a time while funtion returns only one value at a time.
functions and storedprocedure [message #98385 is a reply to message #98245] Sun, 05 September 2004 23:05 Go to previous messageGo to next message
sasikumar
Messages: 1
Registered: September 2004
Junior Member
Hi,

Functions and storedprocedure :

1.We can call the functions in sql statements (select max(sal) from emp). where as sp is not so

2.Function do not return the images,text whereas sp returns all.

Have a nice day!

by
Sasikumar.R
Re: difference between functions and stored procedures [message #98440 is a reply to message #97689] Sun, 10 October 2004 22:28 Go to previous messageGo to next message
sriharik
Messages: 1
Registered: October 2004
Junior Member
Hi,

Please give more information and examples if u have any to find the exact difference between the functions and stored procdures.On the basis of perfomance with one is good.please if u know any links where i can find the exact complete diffrences list of both of them.I am very thank full to u guys...

Thanks,

Srihari.k
Re: difference between functions and stored procedures [message #98461 is a reply to message #98145] Sat, 06 November 2004 01:09 Go to previous messageGo to next message
harris
Messages: 3
Registered: April 2002
Junior Member
Hi
important differences between user-defined functions and stored procedures—the most pronounced being what types of data they can return.
Re: difference between functions and stored procedures [message #98476 is a reply to message #97689] Thu, 18 November 2004 00:30 Go to previous messageGo to next message
Manikumar
Messages: 1
Registered: November 2004
Junior Member
function and sp both can return the values.
function returns 1 value only.
procedure can return multiple values(max. 1024)
we can select the fields from function.
in the case of procdure we cannot select the fields.
Re: difference between triggers and procedures [message #98517 is a reply to message #97689] Sat, 11 December 2004 22:15 Go to previous messageGo to next message
savitha
Messages: 9
Registered: November 2000
Junior Member
hie... can anyone explain tome the difference between triggers and procedure? and is stored procedure the same as procedure?
Re: difference between functions and stored procedures [message #98530 is a reply to message #97689] Fri, 17 December 2004 00:17 Go to previous messageGo to next message
jagadish
Messages: 4
Registered: April 2004
Junior Member
Difference bw functions and procedures:

1) functions are used for computations where as procedures can be used for performing business logic

2) functions MUST return a value, procedures need not be.

3) you can have DML(insert, update, delete) statements in a function. But, you cannot call such a function in a SQL query..
eg: suppose, if u have a function that is updating a table.. you can't call that function in any sql query.
- select myFunction(field) from sometable;
will throw error.

4) function parameters are always IN, no OUT is possible
Re: difference between functions and stored procedures [message #98547 is a reply to message #97689] Mon, 27 December 2004 04:47 Go to previous messageGo to next message
mvkishore
Messages: 1
Registered: December 2004
Junior Member
Thanks for the answer
is there any use of out parameters for functions.can functions with out parameters used in a sql statement.

Thanks once again.
Re: difference between triggers and procedures [message #98585 is a reply to message #98517] Mon, 17 January 2005 18:49 Go to previous messageGo to next message
paramasivam
Messages: 1
Registered: January 2005
Junior Member
function - it perform some calculation and one value must be return by function.
after compiling the named function, one object is created in your data base.
you can use this function is any DML statements

procedure
it performs fome action. it may or may not return values.
you cann't use this function in any DML statement.
but it is possible to call this named procedure from any oracle developer tools like forms, reprots,
Re: difference between functions and stored procedures [message #211216 is a reply to message #97682] Wed, 27 December 2006 05:31 Go to previous messageGo to next message
sayeed321in
Messages: 1
Registered: December 2006
Junior Member

Differences between a function and a procedure:
1. A function can have return statement where as a procedure cannot have a return statement.
2. A procedure can be executed separately from SQL and also can be invoked from some other procedures or functions where as a function can only be invoked from a function or a procedure.
3. A function can be used in DML where as a procedure cannot be invoked from a DML.
4. We cannot use DDL in a function where as in procedure it is possible using dynamic SQL package.
5. A procedure can be either stored procedure or an anonymous where as a function cannot be anonymous.
6. We cannot use a Transaction control language in a function where as in Procedures we can use TCL.
7. Functions can also have IN and OUT parameters.
8. Functions are used for computations where as procedures can be used for performing business logic.

Happy Learning!!!!!!!!!!!!

Thanx & Regards,
Ahmed Sayeed.
Re: difference between functions and stored procedures [message #211286 is a reply to message #211216] Wed, 27 December 2006 16:06 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
1. A function can have return statement where as a procedure cannot have a return statement.
>>    A function always has a return value. The "value" have be one of many things including pl/sql tables, ref cursors etc.
2. A procedure can be executed separately from SQL and also can be invoked from some other procedures or functions where as a function can only be invoked from a function or a procedure.
>>    A function can be called from anonomous pl/sql - the return value needs somewhere to go...
exec dbms_output.put_line(my_funct());
3. A function can be used in DML where as a procedure cannot be invoked from a DML.
>>    Triggers can call functions. procedures can't be called directly in sql statements. Procedures can be called from simple wrapper functions.
4. We cannot use DDL in a function where as in procedure it is possible using dynamic SQL package.
>>    dml can be used in functions and procedures
5. A procedure can be either stored procedure or an anonymous where as a function cannot be anonymous.
>>    anonomous pl/sql is not usually refered to as a procedure in Oracle.
6. We cannot use a Transaction control language in a function where as in Procedures we can use TCL.
>>    Not true in general. (in any case, a wrapper function can call procedures so anything a procedure and do a function can do)
7. Functions can also have IN and OUT parameters.
>>    yes
8. Functions are used for computations where as procedures can be used for performing business logic.
>>    functions and procedures can be used for whatever you choose.
Previous Topic: need to export 2 million records
Next Topic: Porting data from Oracle 8I to XML
Goto Forum:
  


Current Time: Thu Dec 08 12:15:26 CST 2016

Total time taken to generate the page: 0.15432 seconds