OracleBlog

When writing code to do repetitive tasks, the two primary approaches are iteration and recursion. Generally you can use either one interchangeably, but potentially with different performance and complexity.

A recursive function calls itself (possibly more than once), with different parameters, and defines an exit clause that is guaranteed to be reached.

CREATE OR REPLACE FUNCTION FIBONACCI_REC (in_number IN NUMBER)
RETURN NUMBER DETERMINISTIC
AS
BEGIN
CASE
WHEN in_number = 1 THEN RETURN 1;
WHEN in_number = 2 THEN RETURN 1;
ELSE RETURN (FIBONACCI_REC (in_number - 1) + FIBONACCI_REC (in_number - 2));
END CASE;
END;

An iterative function includes a loop, which iterates a pre-determined number of times, or checks for an exit clause every time through.

CREATE OR REPLACE FUNCTION FIBONACCI_IT (in_number IN NUMBER)
RETURN NUMBER DETERMINISTIC
AS
fib1 NUMBER := 1;
fib2 NUMBER := 1;
fib3 NUMBER := 1;
fib4 NUMBER;
BEGIN
FOR fib4 IN 3 .. in_number
LOOP
fib3 := fib1 + fib2;
fib1 := fib2;
fib2 := fib3;
END LOOP;
RETURN fib3;
END;

The advantages and disadvantages of the two are not always obvious, and you should really take it on a case-by-case basis. When in doubt: test it. But generally:

1. Recursion may be slower, and use greater resources, because of the extra function calls.

2. Recursion may lead to simpler, shorter, easier-to-understand functions, especially for mathematicians who are comfortable with induction formulae.

Either way, one of the most critical aspects of writing either a recursive or an iterative function is to have an exit clause (or "base case" for recursions) that is checked at every recursion/iteration and is guaranteed to be reached at some finite point, given any input. Otherwise you will get either:

Infinite Recursion: More and more functions being spawned, never closing, using up resources until there are none left, possibly crashing the system.

Infinite loop: A loop that cycles forever, burning CPU and never completing.

I can illustrate this point with my recursive example above. Do NOT try this, but if you inserted a negative number, what do you think would happen? Infinite recursion.

Now you have not only the basics on iteration and recursion, but, based on the topic I chose for the sample code, knowledge that I saw "Da Vinci Code" recently.