What is the difference between procedure and function and package, which is the fastest

Showing Answers 1 - 18 of 18 Answers


  • May 1st, 2006

procedure :- perform an action

Function: - Function used to compute a value and return a value

  Was this answer useful?  Yes


  • May 2nd, 2006

1) stored procedures are designed to return its output to the application.
 A UDF (User Defines Functions) returns table variables, while a SPROC can't return a table variable although it can create a  


2) UDFs can't change the server environment or your operating system environment,
 while a SPROC can.

3) when T-SQL encounters an error the function stops,
 while T-SQL will ignore an error in a SPROC and proceed to the next statement in your code
 (provided you've included error handling support).

4) a SPROC can be used in an XML FOR clause
 a UDF cannot be.

5) If you have an operation such as a query with a FROM clause that requires a rowset be drawn from a table or set of  

 tables, then a function will be your appropriate choice.
 However, when you want to use that same rowset in your application the better choice would be a stored procedure.

  Was this answer useful?  Yes


  • May 29th, 2006

procedure: It may or may not return a value.

function: It always return a value.

procedure :it can't be used in select query.for example if we have a procedure abc(salary number ) then we can't use it as " select abc(salary) from employee.

whereas , we can use functions in a query . "select sum(salary) from employee"

  Was this answer useful?  Yes

Package-->It stores and compiles all the functions and procedures declared in SGA(System Global Area).Whenever stored procedures or functions are called from this package,it will get them from SGA.So it perfomance is definetly high.

Procedures-->They are subprograms which perform certain action.They cannot be called from select statement.

Functions-->They are subprograms which return a value.They can be used in select statement and also its return value can be assigned to a variable.

  Was this answer useful?  Yes


  • Mar 8th, 2007

main diffences between procedure and function as follows

              procedure                                                                    function
1>procedure is used for performing                            1> its used for computing value
   action                                                                                 and returning value

2> procedure may or maynot return                             2> function must and should 
     value                                                                              return value

3>it cant be used inside sql statement                        3>it can be called inside sql
4> its cant be called as part of expression                  4>its always called as part of

  Was this answer useful?  Yes


  • Apr 12th, 2007

Hi guys.......
nobody is concentrating in the second part of the question.....
plz post your comments on the second part of Question......
which is more better for use....function, procedure or a package....
Thanx & Regards,
Ahmed Sayeed

  Was this answer useful?  Yes


  • Jun 28th, 2007

Package is faster when you working on specific project and all your functions and procedure are in that package.
because package load into memory and remain in memory till the place available so execution is fast.
for calling realted procedure, function and global variable.

but if you have a small and misc work. then you should use single function or procedure
it will take a small memory and load fast


1. Function can be used in a select statement where as procedures cannot.
2. Procedure take both input and output parameters but Function take only input    parameter.
3. Fuction cannot return value of type text, ntext, image and time stamps whereas prosedure can.
4. Function can be used as user defined data types in create table but procedure cannot.

  Was this answer useful?  Yes


  • Dec 21st, 2010

Function must Return a value

procedure may or may not return one or more value's

package is like a filler you can write procedure, functions within the package

Package is the fastest, because For standalone function & procedure if you call means each and every time it will complie, but for the package once if you complied it means when ever you call that package it need not recomplie so obviously the performance will increase thats why always package is best


  Was this answer useful?  Yes

Procedure : Procedure does not return any values with return clause, however can return data with "out" parameter to calling program

Function : Funcation must return values to calling program with return statement. Funcation can also returns values with "out" parameter to calling program.

Package : Package is group of procedure, fuctions and data types ete. The package is faster as compared to procedure or function. The package will be loaded on the memory when first execution and will be available for subsequent calls, this way package is faster compared to procedure and functions.

  Was this answer useful?  Yes


  • Dec 28th, 2010

Procedure may or may not return one or more value's

Function must Return a value

Function can use in the SQL query while procedure can't thats the major Difference between procedure and function

Package is the fastest because For standalone function & procedure if you call means each and every time it will complie but for the package once if you complied it means when ever you call that package it need not recomplie so obviously the performance will increase thats why always package is best

  Was this answer useful?  Yes

Kiran Shelar

  • Dec 29th, 2011

Hi Everybody,
I dont see much update on why package is more faster than function or procedure.
Of corse package is must faster than procedure or function The reason being whenvever package is called first time it is instantiated meaning it is loaded from disk to memory & it is in the form of compiled (p-code).At this point memory will be allocated to each variables & cursor etc defined in the package.

Each session has its own copy of package variables in shared pool so it is faster to access package through these shared pool whenever call or reference to package has been made & Oracle does it by using LRU algorithm even if package state is aged out.

Hope this clears..cheers


  • Sep 9th, 2012

I do not agree with this, Even a function can perform an action.

  Was this answer useful?  Yes

Varun Tiwari

  • Jul 4th, 2013

Function Procedure
1. Can be used as column Cant used in select statement
in select statement
2. Can be used in where Cant used in where condition
2. It must return value Return keyword can be used but will
with return keyword only pass control back to the main program.

Complex logic can be embedded in the function which can be used in query to filter the record selection.

It can be called standalone and process list of actions (although function can also do the same) to perform.
The only difference with respect to function is that it do not require any placeholder to hold the return value.

If any of the package component called the whole package gets compiled and remain loaded in the memory untill flushed out (restart or using pragma serially_reusable).
Therefore whenever any function/procedure called the response time is fast.

1. If function has group by or any DML statement it cant be used inside select query.

It cant be called inside select/where statement.

If package procedure/function needs to be used only once, unnecessary the whole package gets loaded in the memory.


  Was this answer useful?  Yes

Function can be called in select statement but procedure cant be called in select statement. function has return keyword but procedure not

  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.


Related Answered Questions


Related Open Questions