Differences ( Function, Procedure, Package )

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.

Example:

SQL> create or replace function func_with_insert
2 return number
3 as
4 begin
5 insert into emp values('Name-xxxxx',4544);
6 return sql%rowcount;
7 commit;
8 end;
9 /

Function created.

SQL> declare
2 no_of_rows number;
3 begin
4 no_of_rows := func_with_insert();
5 dbms_output.put_line(no_of_rows||' rows inserted in EMP table');
6 end;
7 /
1 rows inserted in EMP table

PL/SQL procedure successfully completed.

SQL> select func_with_insert() from dual;
select func_with_insert() from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SUSIL.FUNC_WITH_INSERT", line 5