Functions in PL/SQL

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:

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

In this example, the function calculate_square(5) returns the square of the number 5, which is 25, and it is printed to the console.