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.
· 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
