What is a Function in PL/SQL?

A function in PL/SQL is a named block of code that performs a specific task, can take inputs (parameters), and returns a single value. Functions are mainly used when a specific task needs to be performed repeatedly in the code, and the result is required as output.

Functions are similar to procedures but differ in that they must return a value. This returned value can be used in SQL statements, other PL/SQL blocks, or assignments.

Syntax of a PL/SQL Function:

CREATE [OR REPLACE] FUNCTION function_name
  (parameter_1 [IN | OUT | IN OUT] datatype,
   parameter_2 [IN | OUT | IN OUT] datatype, ...)
RETURN return_datatype
IS
  -- Declaration of variables (if any)
BEGIN
  -- Function logic here

  RETURN return_value;
END function_name;
/

Components:

CREATE OR REPLACE FUNCTION: This is used to create or modify an existing function.
function_name: The name of the function.
Parameters: Optional inputs to the function. The parameter mode can be:
IN: Input parameter (default).
OUT: Output parameter.
IN OUT: Used as both input and output.
RETURN: Specifies the data type of the value the function will return.
IS: Marks the start of the declaration section (optional).
BEGIN: Marks the beginning of the executable section.
RETURN: Used to return the result from the function.
END function_name: Ends the function.

Simple Example of a PL/SQL Function:

This example defines a simple function to calculate the square of a number.

CREATE OR REPLACE FUNCTION calculate_square (
  p_number IN NUMBER
) RETURN NUMBER
IS
  v_result NUMBER;
BEGIN
  -- Calculate the square of the input number
  v_result := p_number * p_number;

  -- Return the result
  RETURN v_result;
END calculate_square;
/

Explanation:

Function Name: calculate_square
Parameter: p_number (input number).
Return Type: The function returns a NUMBER.
Logic: It calculates the square of the input number and returns the result.

Example of Calling the Function

Calling the Function in PLSQL BLOCK
Function call in SELECT

1) Calling the Function in PLSQL BLOCK:

DECLARE
  result NUMBER;
BEGIN
  -- Call the function and store the result
  result := calculate_square(5);

  -- Output the result
  DBMS_OUTPUT.PUT_LINE('Square of 5: ' || result);
END;
/

Output:

Square of 5: 25

2) Calling the Function in SELECT

select calculate_square(7) as area from dual;