How to choose between a procedure and a function in PL/SQL
Only use a procedure when a function won’t do the job.
Oracle has been around so long and so many questions have been asked and answered in so many different ways that it can be difficult to find a definitive answer, especially to a best-practices type question like this one.
There are dozens of pages and blog posts explaining the basic differences between PL/SQL functions and procedures:
- Functions return a value. Procedures don’t.
- Functions are callable (with some restrictions) from within standard SQL statements. Procedures aren’t.
Take this advice with a big box of salt. I’m writing this mostly in hopes of being contradicted. I know that many developers use procedures when a function could do the job just as well. I’m hoping to be enlightened as to why. Is it just convention? I would love it if some seasoned Oracle developers could provide tips for best practices on when to use procedures instead of functions.
There is nothing a procedure can do that a function cannot do. Just like procedures, functions can take
IN OUT parameters.
In his 6th edition of Oracle PL/SQL Programming, Steven Feuerstein recommends that you reserve
IN OUT parameters for procedures and only return information in functions via the
RETURN clause (p. 613). But aside from this recommendation, I have found little guidance on when to make a subprogram a procedure or a function.
One recommendation I have heard is that procedures should be used for anything that makes changes to the database (e.g., inserts, updates, and deletes) and that functions should be reserved for getting a specific piece of information (e.g., the radius of a circle or an employee’s salary). But in practice, I have a problem with using procedures for simple inserts, updates, and deletes:
I want to be able to, but don’t want to be forced to, get the result.
For example, I want to be able to call the subprogram like this if I want to know the result:
result := delete_employee(207);
And like this if I don’t care about the result:
Unfortunately, that’s not allowed even with functions. In PL/SQL, unlike in other languages, you cannot call a function without handling the result, but I can do this:
IF delete_employee(207) THEN ...
With a procedure, to find out if a record was actually deleted, I would have to pass in an
out parameters cannot have defaults, I have to pass that parameter in every time I call the procedure, even if I don’t care about the result.
To further illustrate, I’ve created the procedure and function below:
CREATE OR REPLACE PROCEDURE delete_employee_p( employee_id_in IN employees.employee_id%TYPE, deleted OUT BOOLEAN ) AS BEGIN DELETE FROM employees WHERE employee_id = employee_id_in; deleted := SQL%FOUND; END delete_employee_p;
CREATE OR REPLACE FUNCTION delete_employee_f( employee_id_in IN employees.employee_id%TYPE ) RETURN BOOLEAN IS BEGIN DELETE FROM employees WHERE employee_id = employee_id_in; RETURN SQL%FOUND; END delete_employee_f;
When I call the procedure, I have to declare and pass in an
out parameter to capture the result:
DECLARE result BOOLEAN; BEGIN delete_employee_p(207, result); IF result THEN DBMS_OUTPUT.PUT_LINE('Employee deleted.'); ELSE DBMS_OUTPUT.PUT_LINE('No employee with that ID.'); END IF; END;
But with the function, I can just call it directly and check it’s return value without even assigning it to a variable:
BEGIN IF delete_employee_f(207) THEN DBMS_OUTPUT.PUT_LINE('Employee deleted.'); ELSE DBMS_OUTPUT.PUT_LINE('No employee with that ID.'); END IF; END;
Given the above and the fact that I might want to run the delete and move on without checking to see if anything was actually deleted, it seems to me that it makes more sense to use a function than a procedure for these types of DML operations.
Even the first example of a procedure in the Oracle PL/SQL documentation (Example 8-1 Declaring, Defining, and Invoking a Simple PL/SQL Procedure) would be better written as a function. Here’s a simplified version of it using an
OUT parameter rather than a global variable:
Oracle’s Procedure Example (modified)
CREATE OR REPLACE PROCEDURE create_email_p ( name1 IN VARCHAR2, name2 IN VARCHAR2, company IN VARCHAR2, email OUT VARCHAR2 ) IS BEGIN email := name1 || '.' || name2 || '@' || company; END create_email_p;
And here’s how we call it:
DECLARE first_name employees.first_name%TYPE; last_name employees.last_name%TYPE; employer VARCHAR2(8) := 'AcmeCorp'; email employees.email%TYPE; BEGIN first_name := 'John'; last_name := 'Doe'; create_email_p(first_name, last_name, employer, email); DBMS_OUTPUT.PUT_LINE ('Resulting email is: ' || email); END;
Resulting email is: John.Doe@AcmeCorp
Function Version of Oracle’s Procedure Example
And here is the procedure converted to a function:
CREATE OR REPLACE FUNCTION create_email_f ( name1 IN VARCHAR2, name2 IN VARCHAR2, company IN VARCHAR2 ) RETURN VARCHAR2 IS BEGIN RETURN name1 || '.' || name2 || '@' || company; END create_email_f;
And the call:
DECLARE first_name employees.first_name%TYPE; last_name employees.last_name%TYPE; employer VARCHAR2(8) := 'AcmeCorp'; email employees.email%TYPE; BEGIN first_name := 'John'; last_name := 'Doe'; email := create_email_f(first_name, last_name, employer); DBMS_OUTPUT.PUT_LINE ('Resulting email is: ' || email); END;
Why is this better as a function? First of all, it makes intuitive sense to me. The subprogram takes inputs and returns a single result, and that’s what a function is for. Also, as stated earlier, functions can be used within standard SQL, so we can do this:
UPDATE employees SET email = create_email_f(first_name, last_name, 'AcmeCorp') WHERE employee_id = 207;
Use functions by default. Only use procedures if you need to get more than one value out after running the subprogram. Most subprograms are either getters, where you want to get a single object or value, or processes in which you make changes or validate data and want to get a Boolean in return, letting you know the result. For these types of subprograms, functions work at least as well as procedures.