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

• 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;

Output:

Square of 7: 49

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.