Stored Function

A stored function is a named, precompiled block of procedural code stored in the database that must return exactly one value and can be called from inside a SQL expression — unlike a stored procedure, which performs actions and is called as a standalone statement.

Because a function returns a value, you can use it directly in a SELECT list, WHERE clause, or CHECK constraint: SELECT calculate_bonus(employee_id) FROM employees. A procedure can't be used this way — it's invoked with CALL or EXEC and communicates results only through OUT parameters.

In Oracle, functions used inside SQL statements should generally avoid side effects (like writing to another table) unless marked appropriately — the optimizer may call them more or fewer times than you expect.

CREATE FUNCTION calculate_bonus(p_salary NUMBER) RETURN NUMBER IS
BEGIN
  RETURN p_salary * 0.10;
END;