What are the primary differences between functions and procedures in PL/SQL?

In PL/SQL, functions and procedures are used to create modularized and reusable code blocks. Although the two are similar in many ways, they have some primary differences which are as follows:

1. Return Value: A function always returns a value whereas a procedure may or may not return a value. A function is required to contain a “RETURN” statement to specify the return value. The return value of a procedure is represented by an OUT parameter.

Example of a function with a return value:

CREATE FUNCTION get_employee_name (id NUMBER)
RETURN VARCHAR2
IS
  e_name VARCHAR2(100);
BEGIN
  SELECT name INTO e_name FROM employees WHERE employee_id = id;
  RETURN e_name;
END;

2. Usage: Functions are used to compute and return a single value whereas procedures are used to perform an action and manipulate the data in the database. Functions can be used in SQL queries, inside expressions and can be assigned to variables. Procedures perform database operations, but they do not return any value.

Example procedure:

CREATE PROCEDURE delete_employee (id NUMBER)
IS
BEGIN
  DELETE FROM employees WHERE employee_id = id;
END;

3. Constraints: Functions are allowed to be used in SQL statements and are subject to various constraints such as not allowing DML operations inside them. Whereas, procedures are not subject to these constraints and can contain DML inside them.

4. Calling conventions: Procedures can be called from other procedures, functions or directly from the SQL environment. Functions can also be called from procedures and functions but cannot be called from SQL statements that modify data (INSERT, UPDATE, DELETE).

Example function call:

DECLARE
  emp_name VARCHAR2(100);
BEGIN
  emp_name := get_employee_name(101);
END;