Wednesday 17 October 2012

Differnce B/W Procedure & Function In Oracle

Difference B/w Procedure & Function in Oracle Database:

 PLSQL is a block structured language has two types of blocks

1.   
 Anonymous (Without any name, not stored in the data dictionary) 
2.    Subprograms are named Procedures and Functions. (Stored in the data dictionary)
We have three types of subprograms
 1.    Standalone Subprograms: Subprograms defined at the Schema Level (Cannot Overload this type of Subprograms), store in the data dictionary as individual Objects. See Listing 2 &3
2.   Packaged Subprograms: Subprograms defined inside the Package. (Can be overloaded) See Listing 9.
3.    Local Subprograms: Subprograms which we can define inside a block either anonymous or subprograms’, we can overload this type of subprograms. Not part of the data dictionary as individual object. See Listing 1.

Now Back to the Question Differences


1.    Typically procedure is used to perform some actions or tasks and Function is typically used to computes value or calculates something.

2.    For Function It’s mandatory to have Return Clause in the Header section, No Return Clause required for procedure in the header section. See Listing 2.

3.   Difference in calling a function and procedure For calling a function we must assign its value in a variable as its always return a value but not for procedure. See Listing 2 & 3


4.   Only a Function can have options like Deterministic, Pipelined, Parallel enable & Result Cache while procedure cannot have these options.

5.   For a procedure a return statement does not return a value to invoker its return the control to the Invoker so it can not contain an expression in the return.
 Function Return actual value to the invoker and Invoke as part of an expression.
 See listing 2 for Example.

6.   Procedure may or may not have return statement in the Executable section while function must have a return statement in the executable section, it will compile but give error at run time.
Function Can contain multiple return statement and last statement need not to be a return statement or we can say every execution path must lead to a return statement.
See in Listing 4.

7.    You cannot call the procedure from a select Statement, we can
Execute it as a PL/SQL statement only For Function we can call them from Select Statement but with some restrictions.

1.   Only take IN type parameters as argument.
2.            must follow the Purity levels rules
When invoked from a SELECT statement or a parallelized INSERT, UPDATE, or DELETE statement, the subprogram cannot modify any database tables.
When invoked from an INSERT, UPDATE, or DELETE statement, the subprogram cannot query or modify any database tables modified by that statement.
If a function either queries or modifies a table, and a DML statement on that table invokes the function, then ORA-04091 (mutating-table error) occurs. There is one exception: ORA-04091 does not occur if a single-row INSERT statement that is not in a FORALL statement invokes the function in a VALUES clause.
When invoked from a SELECT, INSERT, UPDATE, or DELETE statement, the subprogram cannot execute any of the following SQL statements (unless PRAGMAAUTONOMOUS_TRANSACTION was specified):
– Transaction control statements (such as COMMIT)
– Session control statements (such as SET ROLE)
– System control statements (such as ALTER SYSTEM)
– Database definition language (DDL) statements (such as CREATE), which are committed automatically
Common Myths:
1.    We cannot use out and INOUT with functions
We can use all three modes (IN, OUT & INOUT) for both procedure and functions, but Oracle do not recommend use of Out and IN OUT for a Function. See Listing 5 & 6
2.    Function will return a Single value only
Function can return multiple values by using collections and Ref cursors as out parameters.. But it’s not recommended see Listing 7 & 8
3.    We cannot use DDL statements in Function & can use in procedure
       We can use DDL statement in the both the Subprograms.
4.     Procedure support deferred compilation and function not, it’s not true in case of Oracle database.
Now Examples:
************************************************************************
Listing 1. Local Subprograms & Overloading.
declare
            -- Anonymous Block
            am number;
  -- Procedure 1 with name inside_block
            procedure inside_block  is
                        a varchar2(1);
            begin
                                    select '$' into a from dual;
                                    dbms_output.put_line(a);
            end inside_block;
    -- Procedure 2 with Same name inside_block example of Overloading
            Procedure inside_block(a number) is
                        an number;
            begin
                        select 1 into an from dual;
                                    dbms_output.put_line(an);
            end Inside_block;
-- main anonymous  
begin
            -- Invoke the procedures
            inside_block;
           inside_block(1);
end;
*****************************************************************
Listing 2. No return statement for procedure
create or replace procedure no_return_in_proc_header
-- no return clause in the Header section
   is
-- Declartive Section for decalaring the variables , Cursor , Excpetion ,  --Types etc.
   a varchar2(100):='No expression';
Begin
-- Executable Section
-- Must conatain a statement a null will work
   Dbms_Output.put_line('No Return statement required in the Procedure');
-- can use Return statement in the procedure but it will return the control  to the invoker , it will not return any value , so we can not use expression here. so below statement is invalid
--return a;
         return;
        Dbms_Output.put_line('Control back to caller so this will not be executed');
Exception
-- Exception Handling Section (optional)
when others then
null;
End no_return_in_proc_header ;
/
-- how to invoke the procedure
Begin
-- Invoke the procedure
no_return_in_proc_header;
dbms_output.put_line('control back to the caller');
end;
*****************************************************************
Listing 3. Must return statement in Header
create or replace function return_must_function_header
return varchar2 -- must required
is
avalue varchar2(100);
-- Declartive Section for decalaring the variables , Cursor , Excpetion , Types etc.
Begin
-- Executable Section
-- Must conatain a statement inside the executable sectioneven a null will work
  avalue:='Return stetemnt is must';
  -- Return clause in the exectable section is must it will compile without this but give error at run time
  return avalue;
Exception
         -- Exception Handling Section (optional)
when others then
     return null;
End return_must_function_header ;
/
-- how to invoke the function
declare
m varchar2(100);
begin
-- function value must be assign to avariable
m:=return_must_function_header;
dbms_output.put_line(m);
end;
/
-- Another Method using Host variables
variable var_m varchar2(100);
begin
-- function value must be assign to avariable
:var_m:=return_must_function_header;
end;
/
print var_m
*****************************************************************
Listing 4. Multiple Return Statement in Function
create or replace function multiple_returnstatement ( i char )
return varchar2 -- must required
is
Begin
-- Executable Section
  if i='A' then
  return 'Good';
  elsif i='B' then
  return 'Very Good';
  else
  return 'not applicable';
  end if;
Exception
        
when others then
     return null;
End multiple_returnstatement;
/
-- how to invoke it
declare
a varchar2(16);
begin
a:=multiple_returnstatement('A');
dbms_output.put_line(a);
a:=multiple_returnstatement('B');
dbms_output.put_line(a);
a:=multiple_returnstatement(Null);
dbms_output.put_line(a);
end;
/
*****************************************************************
Listing  5 Out parameter with Procedure
create or replace procedure proc_out_para
(aempid number,  asal out number)
is
-- declare variables here
begin
-- make sure asal is a saclar variable so only one value
select salary into asal from employees where employee_id=aempid;
end proc_out_para;
/
 -- how we invoke this
declare
vsal number;
begin
proc_out_para(aempid => 101,asal => vsal);
dbms_output.put_line(vsal);
end;
/
*****************************************************************
Listing 6 Out Parameter and Function
create or replace function function_out_para (aempid number,  asal out number)
return number
is
-- declare variables here
begin
-- make sure asal is a saclar variable so only one value
select salary into asal from employees where employee_id=aempid;
return asal;
end function_out_para;
/
 -- how we invoke this
 declare
vsal number;
begin
vsal:=function_out_para(aempid => 101,asal => vsal);
dbms_output.put_line(vsal);
end;
*****************************************************************
Listing 7 Multple Values for Function using record
create or replace function fun_multiplevalue_rec
(aempid number,  aAll out employees%rowtype) return employees%rowtype
is
begin
-- make sure asal is a saclar variable so only one value
select * into aAll from employees where employee_id=aempid;
return aAll;
end fun_multiplevalue_rec;
/
-- HOW TO INVOKE
declare
a employees%rowtype;
begin
a:=fun_multiplevalue_rec(aempid => 101,aAll => a);
dbms_output.put_line(a.salary||a.last_name);
end;
*****************************************************************
Listing 8 Multiple Value with Columns As well as Rows using collecttion
 create TYPE t_emp IS TABLE OF Number ;
create or replace function multiplevalue RETURN t_emp IS
 -- return multiple outputs using the collections ..
 v_rec t_emp;
 BEGIN
 SELECT salary  INTO v_rec FROM EMPLOYEES;
 RETURN v_rec;
END multiplevalue ;
declare
  -- Non-scalar parameters require additional processing
  result t_emp;
begin
  -- Call the function
  result := proc_function_diff.multiplevalue;
  for i in result.first .. result.last loop
  dbms_output.put_line(result(i).last_name || ' salary = '|| result(i).salary);
  end loop;
end;
****************************************************************************************************
Listing 9. Package Spec & Body containg Procedure and Functions
CREATE OR REPLACE PACKAGE PROC_FUNCTION_DIFF IS
  TYPE t_emp IS TABLE OF EMPLOYEES%ROWTYPE INDEX BY BINARY_INTEGER;
  emp_type t_emp;
  --***********************************************************
  procedure no_return_in_proc_header;
  --***********************************************************
  function multiplevalue RETURN t_emp;
  --***********************************************************
  function return_must_function_header return varchar2;
  --***********************************************************
  function multiple_returnstatement(i char) return varchar2;
  --***********************************************************
  procedure proc_out_para(aempid number, asal out number);
  --***********************************************************
  function fun_multiplevalue_rec(aempid number, aAll out employees%rowtype)
    return employees%rowtype;
end;
***********************************************************
Package Body
CREATE OR REPLACE PACKAGE BODY PROC_FUNCTION_DIFF IS
-- Author Arun Kaushik
-- Purpose : Procedure function difference and methods.
--************************************************************************************************************************
procedure no_return_in_proc_header
          -- no return clause required for procedure in the Header section
   is
          -- Declartive Section for decalaring the variables , Cursor , Excpetion , Types etc.
   a varchar2(100):='No expression';
Begin
     -- Executable Section
     -- Must conatain a statement , a null can work
   Dbms_Output.put_line('No Return statement required in the Procedure');
           -- we can use Return statement in the procedure but it will return the control
           -- to the invoker , it will not return any value , so we can not use expression here.
           -- so below statement is invalid
   --return a;
         return;
        Dbms_Output.put_line('Control back to caller so this will not be executed');
Exception
-- Exception Handling Section (optional)
when others then
null;
End no_return_in_proc_header ;
--************************************************************************************************************************
function return_must_function_header
return varchar2 -- return clause must required in the header section
is
avalue varchar2(100);
-- Declartive Section for decalaring the variables , Cursor , Excpetion , Types etc.
Begin
-- Executable Section
-- Must conatain a statement inside the executable sectioneven a null will work
  avalue:='Return stetemnt is must';
  -- Return clause in the exectable section is must it will compile without this but give error at run time
  return avalue;
Exception
         -- Exception Handling Section (optional)
when others then
     return null;
End return_must_function_header ;
--**********************************************************************************************************************
function multiple_returnstatement ( i char )
return varchar2 -- must required
is
-- example of multiple return statement in the function
-- Declartive Section for decalaring the variables , Cursor , Excpetion , Types etc.
Begin
-- Executable Section
  if i='A' then
  return 'Good';
  elsif i='B' then
  return 'Very Good';
  else
  return 'not applicable';
  end if;
Exception
         -- Exception Handling Section (optional)
when others then
     return null;
End multiple_returnstatement;
--************************************************************************************************************************
procedure proc_out_para (aempid number,  asal out number)
is
-- procedure with out parameter example
-- declare variables here
begin
-- make sure asal is a saclar variable so only one value
select salary into asal from employees where employee_id=aempid;
end proc_out_para;
--************************************************************************************************************************
function fun_multiplevalue_rec (aempid number,  aAll out employees%rowtype) return employees%rowtype
is
-- declare variables here
begin
-- make sure asal is a saclar variable so only one value
select * into aAll from employees where employee_id=aempid;
return aAll;
end fun_multiplevalue_rec;
--************************************************************************************************************************
function multiplevalue RETURN t_emp IS
 -- return multiple outputs using the collections ..
 v_rec t_emp;
 BEGIN
 SELECT * BULK COLLECT INTO v_rec FROM EMPLOYEES;
 RETURN v_rec;
END multiplevalue ;
 --************************************************************************************************************************
end;