rem -----------------------------------------------------------------------
rem Filename:   recurse.sql
rem Purpose:    Script to demonstrate how recursive algorithms like
rem             Fibonacci and Factorials can be implemented in PL/SQL
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

-- Computing the Factorial of a number (n!)
CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN INTEGER IS
BEGIN
  IF n = 1 THEN  -- terminating condition
     RETURN 1;
  ELSE
     RETURN n * fac(n - 1);  -- recursive call
  END IF;
END fac;
/

-- Test n!
SELECT fac(1), fac(2), fac(3), fac(4), fac(5) FROM dual;

-- Sample output:
--     FAC(1)     FAC(2)     FAC(3)     FAC(4)     FAC(5)
-- ---------- ---------- ---------- ---------- ----------
--          1          2          6         24        120


-- Computing the Nth Fibonacci number
CREATE OR REPLACE FUNCTION fib (n POSITIVE) RETURN INTEGER IS
BEGIN
  IF (n = 1) OR (n = 2) THEN  -- terminating condition
     RETURN 1;
  ELSE
     RETURN fib(n - 1) + fib(n - 2);  -- recursive call
  END IF;
END fib;
/

-- Test Fibonacci Series:
SELECT fib(1), fib(2), fib(3), fib(4), fib(5) FROM dual;

-- Sample output:
--     FIB(1)     FIB(2)     FIB(3)     FIB(4)     FIB(5)
-- ---------- ---------- ---------- ---------- ----------
--         1          1          2          3          5
--