Difference between a PROCEDURE & FUNCTION in oracle

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won?t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

1. Functions must return a value(scalar, inline table or multi statement table) whereas stored proc may or may not return a value.
2. Functions can return a table whereas stored procs can create a table but can't return table.
3. Stored procs can be called independently using exec keyword whereas function are called using select statements.
4. Stored procs can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this
5. XML and output parameters can't be passed to functions whereas it can be with sp's.
6. Transaction related statement can be handled in sp whereas it can't be in function.
7. Stored procedures can call a function or another stored proc similarly a function can call another function and a stored proc. The catch with function is that no user defined stored proc can be called. Only extended/system defined procs can be called.