Why should you care?

Tax calculations, fee formulas, and status labels are cleaner as Oracle functions reused in many queries.

See it live — copy this example

Run these statements in SQL Developer or SQL*Plus against your lab PDB. Prefer a practice user — not SYS — for application SQL.

CREATE OR REPLACE FUNCTION get_balance (
  p_customer_id IN NUMBER
) RETURN NUMBER AS
  v_bal NUMBER;
BEGIN
  SELECT balance INTO v_bal FROM customers WHERE customer_id = p_customer_id;
  RETURN v_bal;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

SELECT customer_id, full_name, get_balance(customer_id) AS bal
FROM customers;

What happened?

• RETURN NUMBER defines the result type.
• SELECT INTO loads one value.
• EXCEPTION handles missing customers.
• You can call the function from SQL.

Practice next

1. Compile get_balance.
2. Select from customers using the function.
3. Call get_balance(999) and confirm NULL.
4. Return 0 instead of NULL for missing customers.
5. Add a city parameter filter function.

Remember

Functions return values; great in SELECT. Handle NO_DATA_FOUND. Keep functions side-effect free when used in SQL.

Balance widget

Mobile app shows balance via a single function used by web and batch jobs.

Outcome: One formula, many callers.