Total Pageviews

April 2, 2015

4/02/2015 01:52:00 AM

PROCEDURES V FUNCTIONS
·         Procedures may return through out and in out parameters where as function must return.
·         Procedures cannot have return clause where as functions must.
·         We can use call statement directly for executing procedure where as we need to declare a variable in case of functions.
·         Functions can use in select statements where as procedures cannot.
·         Functions can call from reports environment where as procedures cannot.
·         We can use exec for executing procedures where as functions cannot.
·         Function can be used in dbms_output where as procedure cannot.
·         Procedure call is a standalone executable statement where as function call is a part of an executable statement.

STORED V LOCAL SUBPROGRAMS
·         The stored subprogram is stored in compiled p-code in the database, when the procedure is called it does not have to be compiled.
·         The local subprogram is compiled as part of its containing block. If the containing    block is anonymous and is run multiple times, the subprogram has to be compiled    each time.
·           Stored subprograms can be called from any block submitted by a user who has execute privileges on the subprogram.
·           Local subprograms can be called only from the block containing the subprogram.
·           By keeping the stored subprogram code separate from the calling block, the calling block is shorter  and easier to understand.
·          The local subprogram and the calling block are one and the same, which can lead to   part confusion. If a change to the calling block is made, the subprogram will be   recompiled as of the recompilation of the containing block.
·           The compiled p-code can be pinned in the shared pool using the DBMS_SHARED_POOL Package. This can improve performance.
·          Local subprograms cannot be pinned in the shared pool by themselves.
·         Standalone stored subprograms cannot be overloaded, but packaged subprograms can be overloaded within the same package.
·           Local subprograms can be overloaded within the same block


 
Related Posts Plugin for WordPress, Blogger...