Avoiding recursion

In this recipe, we will investigate the use of recursive PL/SQL functions and their impact on performance.

How to do it...

The following steps demonstrate recursive functions:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create the FACTORIAL_RECURSIVE function to calculate the factorial of a given number (which is the product of all positive integers less than or equal to the given number) using the well-known recursive algorithm, as follows:

   CREATE OR REPLACE FUNCTION FACTORIAL_RECURSIVE (ANUM NUMBER) RETURN NUMBER IS
     AVALUE NUMBER;
   BEGIN
     IF ANUM <= 1 THEN
       AVALUE := 1;
     ELSE
       AVALUE := ANUM * FACTORIAL_RECURSIVE(ANUM - 1);
     END IF;
     RETURN AVALUE;
   END;
   

3. Create the function FACTORIAL_ITERATIVE to calculate the factorial of a given number using an iterative algorithm:

   CREATE OR REPLACE FUNCTION FACTORIAL_ITERATIVE (ANUM NUMBER) RETURN NUMBER IS
     AVALUE NUMBER := 1;
   BEGIN
     FOR J IN 2..ANUM LOOP
       AVALUE := AVALUE * J;
     END LOOP;
     RETURN AVALUE;
   END;...