How to choose between a procedure and a function in PL/SQL

TL;DR

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:

1. Functions return a value. Procedures don’t.
2. Functions are callable (with some restrictions) from within standard SQL statements. Procedures aren’t.

There is nothing a procedure can do that a function cannot do. Just like procedures, functions can take OUT and IN OUT parameters.

In his 6th edition of Oracle PL/SQL Programming, Steven Feuerstein recommends that you reserve OUT and 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:

delete_employee(207);

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 parameter:

delete_employee(207, result);

And because 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:

Procedure: delete_employee_p

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;

Function: delete_employee_f

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 its 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;

The result:

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;

Takeaway

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.