Procedure vs. Function

A database (back end) programming language like PL/SQL in Oracle, has been provided to push the processing of the data to the server end where the actual data resides. This helps reduce network traffic otherwise involved in taking the data from database to front end, if the processing is done in the application in lack of back end language. The two main types of programs used in the database for supporting the data handling on clues / calls from front end are Procedures & Functions. They are source of confusion while making choice (procedure or function) to solve a problem, because in most problem cases, both may be written with equal ease, and it is the question of appropriateness to be addressed rather than correctness. Beginners are also observed to be failing to answer the question of difference and/or similarity between the two confidently and correctly in the interviews. Hence this post. Here we shall be taking the cases with PL/SQL, however other database systems also have their own back end language sharing same syntax as PL/SQL, so this should help them too.

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 -

create function delrows (dno emp.deptno%type)

return number is

       x number;

begin

      delete from emp where deptno = dno;

            x := sql%rowcount;

      return x;

end;

OR

create procedure addnum(n1 number,n2 number,n out number)

is

begin

      n := n1 + n2;

end;

In the above case "delrows" should have been developed as procedure and "addnum" as a function. But even as of now, they are fully legal and they work in their own ambit. The "delrows" will indeed return the number of rows deleted and addnum will return the sum of the two numbers. Then, where the thing has gone wrong -

Function Purity - 

You can see from the foregoing explanation on the procedure and function, that the function is a versatile entity and ideally it should be callable from SQL as well as PL/SQL (programming) environment, whereas the procedure may only be called in PL/SQL environment as an executable statement itself. However, the function like "delrows" can not be called in SQL, but the same may be called in PL/SQL environment in an expression to return the number of rows deleted. So the function, though syntactically correct has now a restricted use. The versatility of the function actually comes by following certain rules defined in terms of its purity levels, which are - WNDS, RNDS, WNPS and RNPS. They respectively mean -

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

So it means, when all the above conditions are met in a function, the function is truly pure and versatile with respect to its calling environment. In the instant case, "delrows" violates the WNDS purity level by the presence of delete statement in it, hence can not be used in SQL.

On the other hand, adding two numbers is a simple computational activity, but in the form of a procedure can not be conveniently used in SQL statement, by passing columns of a table say salary and commission of employee table for calculating the total earning of employees.

Guidelines to make a choice -

The following considerations may be made while choosing between procedure or function for solving a problem -

• 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).

Other interesting reads : Data Integrity Triggers, Transaction Concurrency, Object Tables