Functions

A PL/SQL function is similar to a procedure but always returns a single value. Functions can be called directly from SQL statements — a key advantage over procedures.

Creating a Function

CREATE OR REPLACE FUNCTION function_name (
  parameter1 IN datatype,
  parameter2 IN datatype
) RETURN return_datatype AS
BEGIN
  -- statements
  RETURN some_value;
END function_name;
/

The RETURN clause in the header declares the type of value returned. The RETURN statement in the body provides the actual value.

A Simple Example

CREATE OR REPLACE FUNCTION calculate_bonus (
  p_salary IN NUMBER,
  p_rating IN NUMBER
) RETURN NUMBER AS
  v_bonus NUMBER;
BEGIN
  v_bonus := p_salary * (p_rating / 100);
  RETURN v_bonus;
END calculate_bonus;
/

Calling a Function

From PL/SQL:

DECLARE
  v_result NUMBER;
BEGIN
  v_result := calculate_bonus(50000, 10);
  DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_result);
END;
/

From SQL — this is unique to functions:

SELECT first_name, calculate_bonus(salary, 10) AS bonus
FROM employees;

Rules for Functions Called from SQL

When a function is used in a SQL statement, it must follow these rules:

1. No DML — the function cannot INSERT, UPDATE, or DELETE
2. No DDL — cannot CREATE or DROP objects
3. No transaction control — no COMMIT or ROLLBACK
4. Single return value — no OUT parameters

DETERMINISTIC Keyword

If a function always returns the same output for the same inputs, declare it DETERMINISTIC. Oracle can cache results and avoid re-executing the function:

CREATE OR REPLACE FUNCTION full_name (
  p_first IN VARCHAR2,
  p_last  IN VARCHAR2
) RETURN VARCHAR2 DETERMINISTIC AS
BEGIN
  RETURN p_first || ' ' || p_last;
END;
/

Function vs Procedure

Feature	Function	Procedure
Returns a value	Yes (via RETURN)	Not directly (uses OUT params)
Callable from SQL	Yes	No
Can have OUT params	Technically yes, but avoid	Yes
Use case	Compute & return a value	Perform an action

Multiple RETURN Paths

A function can have multiple RETURN statements, but execution ends at the first one reached:

CREATE OR REPLACE FUNCTION grade_score(p_score NUMBER) RETURN VARCHAR2 AS
BEGIN
  IF p_score >= 90 THEN RETURN 'A'; END IF;
  IF p_score >= 80 THEN RETURN 'B'; END IF;
  IF p_score >= 70 THEN RETURN 'C'; END IF;
  RETURN 'F';
END;
/