Basic Oracle Concepts and Programming Question:

Can Sub Procedure/Function Be Called Recursively?

Tweet Share WhatsApp

Answer:

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.

Download Oracle Database PDF Read All 430 Oracle Database Questions
Previous QuestionNext Question
How To Define a Sub Function?What Happens If Recursive Calls Get Out of Control?