1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

difference between oracle stored procedure and stored function

Discussion in 'SQL PL/SQL' started by mukulverma2408, Jul 7, 2015.

  1. what are the design consideration to be kept in mind while using oracle procedure and functions and what is the difference between two. when to prefer one over other.
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    If the subprogram
    -- needs to be called from SQL, you must use a function.
    -- always returns a single value -- you normally use a function.
    -- needs to return multiple values (through OUT or IN/OUT parameters), you always use a procedure.
    -- never returns a value, you always use a procedure.
     
    mukulverma2408 likes this.
  3. revathimano86@gmail.com

    revathimano86@gmail.com Newly Initiated

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bangalore
    Find the difference
    Procedure
    1. Procedure may or may not return values.
    2. Procedure can’t be called into SQL statements.
    3. Procedure is used to implement business logic.
    4. Allow SELECT statements as well as DML statement in procedure.
    5. Procedure can call function.
    6. Procedure have accept commit & Rollback:
    7. We can do DML operation in procedure.

    Function
    1. Function must Return a value.
    2. Function can be called into SQL statement.
    3. Function are used for compute expressions
    4. Allow only select statements in it.
    5. But function will not call procedure.
    6. Function does not accept commit and Rollback.
    7. No DML in function – (possible tru pragma).
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Function
    1. Function must Return a value.
    2. Function can be called into SQL statement.
    3. Function are used for compute expressions
    4. Allow only select statements in it.
    5. But function will not call procedure.
    6. Function does not accept commit and Rollback.
    7. No DML in function – (possible tru pragma).


    WRONG!
    A function can certainly call a procedure.
    A function can perform DML as well as performing COMMIT and ROLLBACK operations. HOWEVER -- if it does so, the function can then not be called from a SQL statement.

    A Function calling a procedure:
    Code (Text):
    CREATE PROCEDURE proc1(p_outvar OUT VARCHAR2)
    AS
    BEGIN
      p_outvar := 'Of course a function can call a procedure';
    END;

    CREATE FUNCTION func1
    RETURN VARCHAR2
    AS
      v_retval   VARCHAR2(100);
    BEGIN
      proc1(v_retval);
      RETURN v_retval;
    END;



    SELECT func1 FROM dual;
    FUNC1
    -------------------------------------------
    Of course a function can call a procedure

    A Function performing DML (when not called from SQL)
    Code (Text):
    CREATE FUNCTION func2
    RETURN VARCHAR2
    AS
    BEGIN
      UPDATE emp1
      SET    emp_name = 'fred'
      WHERE  emp_id = 20;
      COMMIT;
      RETURN '*I just did DML and a COMMIT*';
    END;

    CREATE PROCEDURE proc2
    AS
      v_retval   VARCHAR2(50);
    BEGIN
      v_retval := func2;
      DBMS_OUTPUT.PUT_LINE(v_retval);
    END;

    EXECUTE proc2
    anonymous block completed
    *I just did DML and a COMMIT*

    SELECT func2 FROM dual;
    ORA-14551: cannot perform a DML operation inside a query
    ORA-06512: at "MMORRIS.FUNC2", line 5
    14551. 00000 -  "cannot perform a DML operation inside a query "