Call Procedure or Function Recursively in Oracle

Q

Can Sub Procedure/Function Be Called Recursively in Oracle?

✍: FYIcenter.com

A

PL/SQL allows sub procedures or functions to be called recursively. The tutorial example below shows you how to calculate factorial values with a recursive sub function:

SQL> CREATE OR REPLACE PROCEDURE FACTORIAL_TEST AS              
  2    FUNCTION FACTORIAL(N NUMBER)                             
  3      RETURN NUMBER AS                                       
  4    BEGIN            
  5      IF N <= 1 THEN 
  6        RETURN 1;    
  7      ELSE           
  8        RETURN N*FACTORIAL(N-1);                             
  9      END IF;        
 10    END;             
 11  BEGIN              
 12    DBMS_OUTPUT.PUT_LINE('3! = ' ||
 13      TO_CHAR(FACTORIAL(3)));
 14    DBMS_OUTPUT.PUT_LINE('10! = ' ||
 15      TO_CHAR(FACTORIAL(10)));
 16    DBMS_OUTPUT.PUT_LINE('64! = ' ||
 17      TO_CHAR(FACTORIAL(64)));
 18  END;
 19  /               
                        
SQL> EXECUTE FACTORIAL_TEST;
3! = 6
10! = 3628800
64! = 126886932185884164103433389335161480802000000000000...

There must be something wrong with the FACTORIAL() definition that causes those many extra '0's in the '64!' result.

 

Creating Oracle PL/SQL Procedures and Functions

⇒⇒Oracle Database Tutorials

2016-10-17, 137👍, 0💬