Related Articles

Related Articles

SQL | Difference between functions and stored procedures in PL/SQL
  • Last Updated : 23 Aug, 2018

Prerequisite:

Differences between Stored procedures(SP) and Functions(User defined functions (UDF)):

  1. SP may or may not return a value but UDF must return a value.
    Example:

    SP ->
    create or replace procedure GEEKS(x int) 
    is
    y int;
    begin
    .....
    
    UDF->
    FUNCTION GEEKS(x int)  
    /*return statement so we must return value in function */  
    RETURN int             
    IS  
      y int;  
    BEGIN 
    .....
    
    
  2. SP can have input/output parameter but UDF only has input parameter.
    Example:

     SP ->
     CREATE OR REPLACE PROCEDURE Factorial(x IN NUMBER, result OUT NUMBER)
     is
     begin
     ....
    
    UDF ->
    FUNCTION Factorial(x IN NUMBER) /* only input parameter */ 
    return  NUMBER
    is
    result NUMBER;
    begin
    .....
    
    
  3. We can call UDF from SP but cannot call SP from a function.

    Example:

    Calling UDF cal() inside SP square() but reverse is not possible.
    
    set serveroutput on;
    declare
    a int;
    c int;
    
    function cal(temp  int)
    return int
    as 
    ans int;
    begin
    ans:=temp* temp;
    return ans;
    end;
    
    procedure square(x in int, ans out int) 
    is
    begin
    dbms_output.put_line('calling function in procedure');
    ans:= cal(x);
    end;
    
    begin
    a:=6;
    square(a, c);
    dbms_output.put_line('the answer is '|| c);
    end;
    

    OUTPUT:

    calling function in procedure
    the answer is 36
    
  4. We cannot use SP in SQL statement like SELECT, INSERT, UPDATE, DELETE, MERGE etc. but we can use them with UDF.
  5. We can use try-catch exception handling in SP but we cannot do that in UDF.
  6. We can use transaction in SP but it is not possible in UDF.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :