Oracle PL/SQL - PL SQL Function Procedure Package Recursion

Introduction

Oracle PL/SQL supports function recursion.

You can call the function from itself.

The following code shows how to use recursion by calculating the factorial of any integer.

A factorial is a product of all integer numbers between 1 and a specified integer.

It is defined using the recursive formula (n!=n*(n-1)!).

Demo

SQL>
SQL> create or replace function f_factorial_nr (i_nr NUMBER)
  2  return NUMBER
  3  is-- from   w  w w .  j a v a  2  s.  c o  m
  4  begin
  5        if sign(i_nr)=-1 or abs(i_nr)!=i_nr
  6        then
  7             return null;
  8        else
  9             if i_nr = 1
 10             then
 11                   return 1;
 12             else
 13                   return i_nr*f_factorial_nr(i_nr-1);
 14             end if;
 15        end if;
 16  end;
 17  /

Function created.

SQL>
SQL> declare
  2  begin
  3       DBMS_OUTPUT.put_line(f_factorial_nr(5));
  4  end;
  5  /
120

PL/SQL procedure successfully completed.
SQL>