Can PL/SQL procedures call themselves recursively?

Using PL/SQL, it is possible to call a stored function from within that same function. This can be demonstrated with the following example:

CREATE OR REPLACE FUNCTION factorial(x in number) RETURN number IS f number; BEGIN IF x = 0 THEN f := 1; ELSE f := x * factorial(x-1); END IF; RETURN f; END; / DECLARE num number; factorial number; BEGIN num := # factorial := factorial(num); dbms_output.put_line(' The factorial of '|| num || ' is ' || factorial); END; /

Can this be done using PL/SQL stored procedures as well? 15.8k 4 4 gold badges 39 39 silver badges 47 47 bronze badges asked Dec 18, 2017 at 19:48 584 3 3 gold badges 14 14 silver badges 37 37 bronze badges Why nor just try it to find out? Commented Dec 18, 2017 at 22:11

2 Answers 2

Yes, you can write a procedure that calls itself recursively in PL/SQL. Here is an example - implementing the factorial.

With that said, don't ever write a procedure (or a function like yours) without error handling. If you don't understand why, change 5 to 5.3 in the anonymous block below, and you'll see why.

create or replace procedure fact ( x in number, x_fact out number ) as begin if x = 0 then x_fact := 1; else fact(x-1, x_fact); x_fact := x * x_fact; end if; end; / set serveroutput on declare z number; begin fact(5, z); dbms_output.put_line(z); end; /

SCRIPT OUTPUT window (matching each "result" to the corresponding part of the code left as an exercise):

Procedure FACT compiled PL/SQL procedure successfully completed. 120