Functions in PLSQL

A function in PL/SQL is a named block of code that:

* Accepts input parameters

* Performs a task or computation

* Returns a single value using the `RETURN` statement

It promotes modularity, reusability, and maintainability in your database programs.

Basic Structure of a PL/SQL Function

CREATE OR REPLACE FUNCTION function_name (

    param1 IN datatype,

    param2 IN datatype

)

RETURN return_datatype

IS

    -- Local variables

    result return_datatype;

BEGIN

    -- Function logic

    result := ...;

    RETURN result;

END;

Simple Example – Return Full Name

----------------------------------

CREATE OR REPLACE FUNCTION GetFullName (

    first_name IN VARCHAR2,

    last_name IN VARCHAR2

)

RETURN VARCHAR2

IS

    full_name VARCHAR2(100);

BEGIN

    full_name := first_name || ' ' || last_name;

    RETURN full_name;

END;

Usage:

------

SELECT GetFullName('John', 'Doe') FROM dual;

Input Parameters

----------------

Functions can take IN parameters:

(param1 IN datatype, param2 IN datatype)

They cannot have `OUT` or `IN OUT` parameters — that's a procedure's job.

Return Statement

----------------

* Every function must use a `RETURN` statement.

* The return value must match the declared return type.

RETURN result_variable;

Calling Functions

-----------------

SELECT empno, ename, GetFullName(ename, job) FROM emp;

From PL/SQL:

------------

DECLARE

    name VARCHAR2(100);

BEGIN

    name := GetFullName('John', 'Smith');

    DBMS_OUTPUT.PUT_LINE(name);

END;

DML in Functions

-----------------

You can use `INSERT`, `UPDATE`, or `DELETE` inside a function, but you then cannot call that function from SQL (only from PL/SQL).

Calculate Annual Salary

-----------------------

CREATE OR REPLACE FUNCTION Get_Annual_Salary (

    monthly_salary IN NUMBER

)

RETURN NUMBER

IS

BEGIN

    RETURN monthly_salary * 12;

END;

Usage in SQL:

-------------

SELECT ename, sal, Get_Annual_Salary(sal) AS annual_sal FROM emp;

What is the difference between stored procedure and function?

1) Stored Procedure Can have IN, OUT, and INOUT parameters but Function has Only IN parameters

2) Stored Procedure Can't be called in SELECT, JOIN and WHERE but Function Can be called in SELECT queries

3) Stored Procedure return multiple values using OUT or IN OUT but Function return exactly one value using RETURN

Think of functions as calculators – give input, get a single result.

Think of procedures as workers – they can do many things, with or without returning data.