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

procedure :- perform an action

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

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  

table.

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.

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"

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.

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
                                                                                            statement
4> its cant be called as part of expression                  4>its always called as part of
                                                                                              expression

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

Second part answer is Package

Packages are faster when compared to procedure and function.This is because you can store related objects together.

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


bye

no difference

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.

Stored procedure are more faster than functions.  
Usage of Stored Procedure and functions are dependant on the rquirement.

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

Regards,
SSG

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.
 

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

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

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