PL/SQL Functions – Syntax, Examples, Differences from Procedures & Use Cases

What is a Function in PL/SQL?

A function in PL/SQL is a named block of code designed to perform a task and return a single value. While procedures may or may not return values, functions must return exactly one value, using the RETURN clause.

Functions are stored in the database, can accept parameters, and can be called from SQL statements, PL/SQL blocks, or tools like SQL*Plus and Oracle SQL Developer.

Key Features of PL/SQL Functions

Must return a value
Can be stored in the Oracle database schema
Can accept IN parameters
Return value must be handled when the function is called
Can be used inside SQL queries
Can be called from other PL/SQL blocks

Syntax of a PL/SQL Function

The basic syntax for creating a Function is as follows:

CREATE OR REPLACE FUNCTION function_name
   (param1 IN datatype, param2 IN datatype)
RETURN return_datatype
IS
   -- Variable declaration
BEGIN
   -- Function logic
   RETURN value;
EXCEPTION
   -- Error handling
   WHEN OTHERS THEN
      -- Optional exception logic
      RETURN default_value;
END;
/

Example: Simple Function to Add Two Numbers

CREATE OR REPLACE FUNCTION add_numbers(a IN NUMBER, b IN NUMBER)
RETURN NUMBER
IS
   result NUMBER;
BEGIN
   result := a + b;
   RETURN result;
END;
/

Use Cases of Functions in PL/SQL

Use Case	Description
Performing calculations	Like tax, discount, interest, etc.
Transforming data	For example, formatting dates or strings
Returning derived values	For use in reports or queries
Calling inside SQL statements	Unlike procedures, functions can return values in `SELECT`
Building reusable logic	Encapsulate small, repeatable tasks in one place

Function vs Procedure in PL/SQL

Aspect	Function	Procedure
Definition	A named PL/SQL block that must return a single value.	A named PL/SQL block that may or may not return a value.
Return Requirement	Mandatory - must use RETURN.	Optional - can use OUT or IN OUT parameters.
Calling from SQL	Yes	No
Use in SELECT	Allowed if no DML	Not allowed
DML Operations	Allowed only in PL/SQL, not in SQL	Fully allowed
Parameters	Only IN	IN, OUT, IN OUT
Stored in DB	Yes	Yes
Return Type	Must define return data type	No return type needed
Best Use Case	Calculations, single value return	Business logic, DML tasks
Call in SQL	e.g. `SELECT get_tax(5000) FROM dual;`	Not supported
Call in PL/SQL	e.g. `v := get_tax(5000);`	e.g. `calculate_bonus(emp_id, v_bonus);`

When to Use a Function Instead of a Procedure?

Use a function when:

You need to return a value
You want to reuse logic inside SQL
You are performing a single logical operation
You want read-only operations (no DML like INSERT/UPDATE)

Best Practices for PL/SQL Functions

Keep the function simple and focused
Avoid DML inside functions called from SQL
Always handle exceptions gracefully
Make sure your function is deterministic when used in SQL
Use appropriate naming conventions like get_, calc_, or fetch_

Example: Function to Get Current Year

CREATE OR REPLACE FUNCTION get_current_year
RETURN NUMBER
IS
   v_year NUMBER;
BEGIN
   SELECT EXTRACT(YEAR FROM SYSDATE) INTO v_year FROM dual;
   RETURN v_year;
END;
/

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.