Tuesday 12 February 2013

Differentiate between PL/SQL functions and procedures.



        i.     Procedure is named PL/SQL block which perform one or more task but function performs a specific task.
     ii.            Procedure may or may not return value whereas function should return one value.
   iii.            Function can be called from SQL statements whereas procedure cannot be called from the SQL statements
   iv.            We can call a stored procedure within function but we cannot call function within stored procedure.
      v.            Functions are normally used for computations whereas procedures are normally used for executing business logic.
   vi.            You can have DML (insert update delete) statements in a function. But you cannot call such a function in a SQL query.
 vii.            Function returns 1 value only. Procedure can return multiple values (max 1024).
viii.            Stored procedure returns always integer value by default zero. Whereas function returns type could be scalar or table or table values
    ix.            Stored procedure is precompiled execution plan whereas functions are not.
A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.

No comments:

Post a Comment