PL/SQL Functions

Last Updated : 15 Jun, 2024

PL/SQL functions are reusable blocks of code that can be used to perform specific tasks. They are similar to procedures but must always return a value.

A function in PL/SQL contains:

Function Header: The function header includes the function name and an optional parameter list. It is the first part of the function and specifies the name and parameters.
Function Body: The function body contains the executable statements that implement the specific logic. It can include declarative statements, executable statements, and exception-handling statements.

Create Function in PL/SQL

To create a procedure in PL/SQL, use the CREATE FUNCTION statement.

Syntax

The syntax to create a function in PL/SQL is given below:

CREATE [OR REPLACE] FUNCTION function_name
(parameter_name type [, …])

— This statement is must for functions
RETURN return_datatype

{IS | AS}

BEGIN
— program code

[EXCEPTION
exception_section;

END [function_name];

Example

In this example, we create a PL/SQL function to calculate factorial of a number

CREATE OR REPLACE FUNCTION factorial(x NUMBER)
  RETURN NUMBER
IS
  f NUMBER;
BEGIN
  IF x = 0 THEN
    f := 1;
  ELSE
    f := x * factorial(x - 1);
  END IF;
  RETURN f;
END;

How to Call Function in PL/SQL

To call a function, specify the function name and any required parameters. The function will execute and return a value.

Example

Here, we call the factorial function which we created earlier.

DECLARE
  num NUMBER;
  result NUMBER;
BEGIN
  num := 5;
  result := factorial(num);
  DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is ' || result);
END;

Output:

the reverse of number is  987654321

PL/SQL Recursive Function

A PL/SQL recursive function is a function that calls itself to perform a specific task. The function continues to call itself until a certain condition is met, at which point it returns a value.

Recursive Function Example

Lets implement a recursive function to calculate the factorial of a number Recursive functions example:

DECLARE
  num INT;
  answer INT;

  -- Defining the function
  FUNCTION factorial(x NUMBER)
    RETURN INT
  IS
    f INT;
  BEGIN
    IF x = 0 THEN
      f := 1;
    ELSE
      f := x * factorial(x - 1);
    END IF;
    RETURN f;
  END;

BEGIN
  num := 5;
  answer := factorial(num);
  DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is ' || answer);
END;

Output:

 Factorial of  5 is 120

Exception handling in PL/SQL Functions

Exception handling can be done using an exception block in functions but exception handling using a try-catch block cannot be done. Example:

SET SERVEROUTPUT ON;

DECLARE
  a INT;
  b FLOAT;
  myexp EXCEPTION;

  FUNCTION sqroot(x INT)
    RETURN FLOAT
  AS
    answer FLOAT;
  BEGIN
    IF x < 0 THEN
      RAISE myexp;
    ELSE
      answer := SQRT(x);
    END IF;
    RETURN answer;
  EXCEPTION
    WHEN myexp THEN
      DBMS_OUTPUT.PUT_LINE('Square root of a negative number is not allowed, so returning the same number');
      RETURN x;
  END;

BEGIN
  b := sqroot(-2);
  DBMS_OUTPUT.PUT_LINE('The value is ' || b);
END;

Output:

square of negative number is  not allowed so returning the same number
the value is -2

Advantages of PL/SQL Functions

We can make a single call to the database to run a block of statements thus it improves the performance against running SQL multiple times. This will reduce the number of calls between the database and the application.
We can divide the overall work into small modules which becomes quite manageable also enhancing the readability of the code.
It promotes reusability.
It is secure since the code stays inside the database thus hiding internal database details from the application(user). The user only makes a call to the PL/SQL functions. Hence security and data hiding is ensured.

DROP Function in PL/SQL

To drop a function in PL/SQL, DROP function statement is used.

Syntax

DROP Function <function_name>;

Example

DROP Function func1;

Important Points About Function in PL/SQL

Functions in PL/SQL must return a value, which can be a scalar, table, or collection.
They can improve performance by executing logic on the database server.
They can enhance security by enforcing strict access controls.
They can simplify complex logic by breaking it down into smaller, manageable parts.
They can handle exceptions and errors by including exception handling blocks.
They can simplify data retrieval by encapsulating complex join operations and filtering conditions.