1 Reply Latest reply on Dec 4, 2014 8:47 AM by BluShadow

    Difference between stored procedure and Function

    b7cd6936-d191-4f80-8034-8ca5c67b718d

      Hi All,

       

      I have seen many comment in blogs that people are giving commets that procecure many or may not return a value but function return a value.

       

      I need clear picture on it.Can you please write the basic syntax to clear understanding how procedure may or may not return a value,Function return only one valure not multiples value.Please write the basic syntax for both procdeure and functions.

       

      Someone says procedure my or may not return a valure,Some people will say  Procedure can return zero or n values).

      Please explain below points with basic examples and syntax.I want only basic examples for clear understanding.

       

       

      Waiting for your prompt response.

      Thanks in advance.

       

       

      My second question

       

      ProcedureFunction
      Stored Procedures can call functions.Stored procedures can’t be called from function.
      Can have select statements as well as DML statements such as insert, update, delete and so onwill not allow us to use DML statements.
      Can use both table variables as well as temporary table in it.We can use only table variables, it will not allow using temporary tables.
      Procedures can't be used in Join clauseA UDF can be used in join clause as a result set.
      We can go for transaction management in procedure

      we can't go in function.


      Procedures cannot be utilized in a select statement function can be embedded in a select statement.

      procedure can returnmultiple values(max. 1024)

      Function returns 1 value only
        • 1. Re: Difference between stored procedure and Function
          BluShadow

          Procedure do NOT return values.  They do not include a "RETURN <somevalue>" statement in the code.

          Procedures can include OUT parameters to allow values to be assigned to those parameters that the calling code can then use, but strictly and technically speaking that is not the same as RETURNing values.

           

          Functions allow for a single datatype to be returned.  That datatype may be atomic (i.e. a single value) or may be composite (i.e. a structure containing many values).

           

          I'm not going to write syntax for you, as you can find the syntax for creating procedures and functions in the Oracle documentation:

           

          http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6009.htm#i2072424

          http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5011.htm#i2153260

           

          Looking at the comparison table you've posted, I'm not sure where you got that, but it's wrong.

          Procedures can call functions, and functions can call procedures, there is no such limitation to say that a function can't call a procedure.

          Function's can use DML statements, though there are limitations if the function is going to be called from an DML statement itself.

          Temporary tables and collection variables (they are not called table variables) can be used in both procedures and functions.

          Not only can procedures NOT be used in a join clause, they cannot be used directly in SQL statements at all, only functions can (with some limitations on the function if it is to be used in SQL)

          Transaction management is not dependent on whether it's a function or procedure, those statements are just nonesense.

          Procedures and functions can both include multiple parameters including OUT parameters (if a function contains OUT parameters, it cannot be used in SQL).  Whilst there's a limit to how many parameters you can have, you would be writing bad code if you get anywhere near that limit.  In such cases, the values to be supplied back should be created in a structures variable type and supplied back using as few OUT parameters as possible.

          Functions can return a single datatype which may be atomic or composite, thus multiple values can be passed back.

          There is also Pipelined functions that can supply multiple 'rows' of data, examples of which you can find by searching.