Explain the difference between PL/SQL procedures and functions

How it works

A function must RETURN exactly one value of a declared type and is designed to be used inside an expression — a SELECT list, a WHERE clause, an assignment. A procedure returns nothing directly (though it can pass values back via OUT/IN OUT parameters) and is invoked as its own statement. A function called from SQL also has restrictions a plain PL/SQL-only procedure doesn't — by default it can't perform DML unless specifically marked to allow it.

CREATE OR REPLACE FUNCTION get_full_name(p_employee_id NUMBER) RETURN VARCHAR2 IS
  v_name VARCHAR2(100);
BEGIN
  SELECT first_name || ' ' || last_name INTO v_name
  FROM employees WHERE employee_id = p_employee_id;
  RETURN v_name;
END;
/

CREATE OR REPLACE PROCEDURE print_full_name(p_employee_id NUMBER) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(get_full_name(p_employee_id));
END;
/

Related Questions