About the PL/SQL Procedure -A PL/SQL procedure, like procedures in any other language, are workhorse i.e. they are supposed to perform some "action" or "operation" (Query / DML). There may be some outcome of this action, in the form of one or more values, which might be required to be sent to the calling program of the procedure; which it does by assigning those values to the parameters (OUT or IN OUT mode parameters) passed to it by the calling program. However there is no compulsion of doing so on the procedures i.e. a procedure may not require to send any value to the calling program (no outcome of the action). As such, technologically the procedure does not "return" a value. The return statement in the procedure is never associated with an expression (no return <x>; , it may be only return;) but may be optionally used to end the execution and return the control to the calling program. For this different ability of the procedure to "send" the value to the calling program (without ending its execution simultaneously) does not allow it to be used in SQL statements and also can not be used in expression. For example, we can not use select p(arg) from <table>; neither we can use x := p(arg);. We may only use the procedure as an executable statement itself. For example, the procedure may be called like p(arg);.
About the PL/SQL Function -A PL/SQL function is supposed to perform only "computation" on the values provided to it through its arguments and no "action" or "operation" (Query / DML). The result of the computation must have to be returned through a "return" statement. The return statement must be associated with an expression, which may be a hard coded value or a variable containing a value or an arithmetic / logical expression. That means the execution of the function ends simultaneous with returning the value. This makes the function able to be called in SQL statement and as a term in expression; never as an executable statement. Since the execution of the function must end with simultaneous return of the value, only one value may be returned from the function.
Why should there be a confusion to choose? -As described above, the Procedures and Functions have diverse characteristics, so then why this discussion, "Procedure vs. Function"? The reason for this being is that, the functions may have everything that a procedure may have. A function may have a Query or DML statement, it is not illegal in the function, however they are not supposed to be used in a function. On the other hand a Procedure may be created for a problem which may be solved with a function, as the "action" in the procedure is optional. Also there is nothing like, a function may not contain an OUT or IN OUT mode parameters and nothing like it can not assign a value to them (though that is not the appropriate way the function should return the value). For example, the following two programs are technically legal but not appropriate -
Function Purity -
- WNDS (Write No Database States) - A function may not have DML statement(s).
- RNDS (Read No Database States) - A function may not read data from table(s).
- WNPS (Write No Packaged States) - A function may not assign to a package variable.
- RNPS (Read No Packaged States) - A function may not read a package variable.
- If the program is supposed to generate multiple values and all those if be required to be sent to the calling program, then use procedure.
- If the program is supposed to perform a query and/or DML (cursor), then irrespective of the number of values required to be returned to the calling program, use procedure.
- If the program is not supposed to use a query and/or DML and must return a single value then use function.
- However if the purity of a function may be forgone (and hence ready to be used in restricted environment) but is a matter of some convenience, then the function may be written, in such special cases, which of course are rare (to be true never faced by me).