Oracle PL/SQL Functions

By /

1. Introduction to Functions

A Function in Oracle PL/SQL is a named program unit that must return a value. Functions are commonly used in SQL queries, expressions, and calculations.

Key Characteristics

  • Must return exactly one value
  • Can be used in SQL and PL/SQL
  • Cannot perform transaction control when used in SQL

2. Basic Syntax of a Function

CREATE OR REPLACE FUNCTION function_name (
    parameter_name datatype
)
RETURN datatype
IS
BEGIN
    RETURN value;
END function_name;
/

3. Simple Function Example

CREATE OR REPLACE FUNCTION get_fixed_value
RETURN NUMBER
IS
BEGIN
    RETURN 100;
END;
/

Calling the Function

SELECT get_fixed_value FROM dual;

4. Function with Parameters

CREATE OR REPLACE FUNCTION calculate_bonus (
    p_salary IN NUMBER
)
RETURN NUMBER
IS
BEGIN
    RETURN p_salary * 0.10;
END;
/

5. Real-Time Use Cases of Functions

Typical Scenarios

  • Calculations
  • Data formatting
  • Business rule enforcement
  • Derived column logic
  • Reusable expressions

Example: Conditional Logic

CREATE OR REPLACE FUNCTION grade_score (
    p_score IN NUMBER
)
RETURN VARCHAR2
IS
BEGIN
    IF p_score >= 80 THEN
        RETURN 'A';
    ELSIF p_score >= 60 THEN
        RETURN 'B';
    ELSE
        RETURN 'C';
    END IF;
END;
/

6. Advanced Function Concepts

6.1 Deterministic Functions

CREATE OR REPLACE FUNCTION square_value (
    p_num IN NUMBER
)
RETURN NUMBER
DETERMINISTIC
IS
BEGIN
    RETURN p_num * p_num;
END;
/

Deterministic functions always return the same result for the same input.

6.2 Functions in SQL Queries

SELECT square_value(5) FROM dual;

SQL Restrictions

  • No COMMIT or ROLLBACK
  • No DDL
  • Limited side effects

6.3 Exception Handling in Functions

CREATE OR REPLACE FUNCTION safe_divide (
    p_num1 IN NUMBER,
    p_num2 IN NUMBER
)
RETURN NUMBER
IS
BEGIN
    RETURN p_num1 / p_num2;
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        RETURN NULL;
END;
/

6.4 Pipelined Functions (Advanced)

CREATE OR REPLACE TYPE num_table AS TABLE OF NUMBER;
/

CREATE OR REPLACE FUNCTION generate_numbers
RETURN num_table PIPELINED
IS
BEGIN
    FOR i IN 1..5 LOOP
        PIPE ROW(i);
    END LOOP;
    RETURN;
END;
/

7. Debugging Functions

Common Techniques

  • Test via SELECT from DUAL
  • Use USER_ERRORS
  • Add temporary logging
  • Validate return paths
SELECT line, text FROM user_errors WHERE name = 'FUNCTION_NAME';

8. Oracle Data Dictionary Views for Functions

View NameDescription
USER_FUNCTIONSUser-owned functions
USER_OBJECTSStatus
USER_SOURCESource code
USER_ERRORSErrors
ALL_FUNCTIONSAccessible functions

9. Best Practices

  • Keep functions side-effect free
  • Ensure all paths return a value
  • Avoid complex logic inside SQL-used functions
  • Mark deterministic functions properly
  • Use functions for calculations, not actions
  • Avoid excessive nesting

10. Procedure vs Function (Quick Comparison)

FeatureProcedureFunction
Returns valueOptionalMandatory
Used in SQLNoYes
Transaction controlYesNo (in SQL)
PurposeActionsCalculations

11. Summary

Functions are best used for:

  • Reusable calculations
  • Query logic
  • Data transformations

They play a crucial role in building efficient, readable SQL and PL/SQL applications.

Package-Based Implementation (Procedure + Function)

Oracle strongly recommends using packages for organizing related procedures and functions.

📦 Package Design Overview

Package Name: lab_business_pkg
Contains:

  • One procedure
  • One function

📄 Package Specification

CREATE OR REPLACE PACKAGE lab_business_pkg
IS
    PROCEDURE calculate_total_amount (
        p_quantity IN NUMBER,
        p_unit_price IN NUMBER,
        p_total OUT NUMBER
    );

    FUNCTION calculate_discount (
        p_total IN NUMBER
    ) RETURN NUMBER;
END lab_business_pkg;
/

📄 Package Body

CREATE OR REPLACE PACKAGE BODY lab_business_pkg
IS

    PROCEDURE calculate_total_amount (
        p_quantity IN NUMBER,
        p_unit_price IN NUMBER,
        p_total OUT NUMBER
    )
    IS
    BEGIN
        p_total := p_quantity * p_unit_price;
    END calculate_total_amount;


    FUNCTION calculate_discount (
        p_total IN NUMBER
    )
    RETURN NUMBER
    IS
    BEGIN
        IF p_total > 1000 THEN
            RETURN p_total * 0.10;
        ELSE
            RETURN p_total * 0.05;
        END IF;
    END calculate_discount;

END lab_business_pkg;
/

📌 Benefits of Package Usage

  • Better modularity
  • Improved performance (memory caching)
  • Clear separation of specification and implementation
  • Enhanced security (private procedures/functions)

Best Practice Notes (Oracle-Aligned)

  • Always expose only required subprograms in package spec
  • Keep heavy logic inside package body
  • Avoid COMMIT inside functions
  • Validate all input parameters
  • Log exceptions meaningfully
  • Use USER_SOURCE for audits
  • Recompile invalid objects after deployment

Disclaimer

All labs, packages, procedures, and functions shown above are strictly for educational purposes.
They use synthetic logic and non-real data and do not represent any real business system.
Always follow your organization’s standards and consult the latest official Oracle documentation before implementing in production environments.

Scroll to Top