# Motivation

The other approach is using the CONNECT BY clause.

Actually I like to add explanation of recursion into my "SQL on Fire" talks because the topic never seems to get old.

# Fibonacci number

A Fibonacci number is defined like this:

F(0) = 0 F(1) = 1 F(n) = F(n-1) + F(n -2) where n > 1

CREATE OR REPLACE FUNCTION Fib(n INTEGER) RETURNS DECIMAL(31, 0) BEGIN DECLARE res DECIMAL(31, 0); CASE WHEN n = 0 THEN SET res = 0; WHEN n = 1 THEN SET res = 1; WHEN n > 1 THEN SET res = Fib(n - 1) + Fib(n - 2); ELSE SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Bad input'; END CASE; RETURN res; END; / ERROR near line 9: SQL0440N No authorized routine named "FIB" of type "FUNCTION" having compatible arguments was found.

One could imagine a simple RETURN with a DECODE.

But that won't change the error message.

DB2 will not insert the function into the DB schema in SYSCAT.ROUTINES until it is done parsing it.

And it won't parse successfully because the function "FIB" does not yet exist.

Far from it!

# Recursion using dynamic SQL

When the function is being executed it can call itself with a dynamic statement without trouble:

CREATE OR REPLACE FUNCTION Fib(n INTEGER) RETURNS DECIMAL(31, 0) BEGIN DECLARE res DECIMAL(31, 0); CASE WHEN n = 0 THEN SET res = 0; WHEN n = 1 THEN SET res = 1; WHEN n > 1 THEN BEGIN DECLARE stmt STATEMENT; PREPARE stmt FROM 'SET ? = Fib(? - 1) + Fib(? - 2)'; EXECUTE stmt INTO res USING n, n; END; ELSE SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Bad input'; END CASE; RETURN res; END; /

VALUES Fib(10); 1 --------------------------------- 55

Typically this means a dynamic cursor:

CREATE OR REPLACE FUNCTION Fib(n INTEGER) RETURNS DECIMAL(31, 0) BEGIN DECLARE res DECIMAL(31, 0); CASE WHEN n = 0 THEN SET res = 0; WHEN n = 1 THEN SET res = 1; WHEN n > 1 THEN BEGIN DECLARE stmt STATEMENT; DECLARE cur CURSOR FOR stmt; PREPARE stmt FROM 'SELECT Fib(? - 1) + Fib(? - 2) FROM SYSIBM.SYSDUMMY1'; OPEN cur USING n, n; FETCH cur INTO res; CLOSE cur; END; ELSE SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Bad input'; END CASE; RETURN res; END; / VALUES Fib(20); 1 --------------------------------- 6765/

CREATE OR REPLACE PROCEDURE Fib(IN n INTEGER, OUT res DECIMAL(31, 0)) BEGIN DECLARE FibNMinus1, FibNMinus2 DECIMAL(31, 0); CASE WHEN n = 0 THEN SET res = 0; WHEN n = 1 THEN SET res = 1; WHEN n > 1 THEN BEGIN DECLARE stmt STATEMENT; PREPARE stmt FROM 'CALL Fib(?, ?)'; EXECUTE stmt INTO FibNMinus1 USING n - 1; EXECUTE stmt INTO FibnMinus2 USING n - 2; SET res = FibNMinus1 + FibNMinus2; END; ELSE SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Bad input'; END CASE; END; / CALL Fib(20, ?); Value of output parameters -------------------------------- RES = 6765

# Recursion using modules (or PL/SQL Packages)

This is only possible for routines defined in modules or in PL/SQL Packages.

Here is an example using a module.

First we create the module and add the function prototype without the body.

Since we want to reference the function globally we need to PUBLISH the function.

CREATE OR REPLACE MODULE M; ALTER MODULE m PUBLISH FUNCTION Fib(n INTEGER) RETURNS DECIMAL(31, 0); /

SELECT ROUTINENAME FROM SYSCAT.ROUTINES WHERE ROUTINEMODULENAME = 'M'; ROUTINENAME -------------------------------------------------- FIB

ALTER MODULE m ADD FUNCTION Fib(n INTEGER) RETURNS DECIMAL(31, 0) BEGIN DECLARE res DECIMAL(31, 0); CASE WHEN n = 0 THEN SET res = 0; WHEN n = 1 THEN SET res = 1; WHEN n > 1 THEN SET res = Fib(n - 1) + Fib(n - 2); ELSE SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Bad input'; END CASE; RETURN res; END; / VALUES M.Fib(20); 1 --------------------------------- 6765

But it's easy to write a simple wrapper.

Being defined in inline SQL PL the wrapper has no runtime overhead:

CREATE OR REPLACE FUNCTION Fib(n INTEGER) RETURNS DECIMAL(31, 0) RETURN M.Fib(n); / VALUES Fib(20); 1 --------------------------------- 6765

CREATE OR REPLACE MODULE M; ALTER MODULE m PUBLISH PROCEDURE Fib(IN n INTEGER, OUT res DECIMAL(31, 0)); / ALTER MODULE m ADD PROCEDURE Fib(IN n INTEGER, OUT res DECIMAL(31, 0)) BEGIN DECLARE FibNMinus1, FibNMinus2 DECIMAL(31, 0); CASE WHEN n = 0 THEN SET res = 0; WHEN n = 1 THEN SET res = 1; WHEN n > 1 THEN CALL Fib(n - 1, FibNMinus1); CALL Fib(n - 2, FibNMinus2); SET res = FibNMinus1 + FibNMinus2; ELSE SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Bad input'; END CASE; END; / CREATE OR REPLACE PROCEDURE Fib(IN n INTEGER, OUT res DECIMAL(31, 0)) BEGIN CALL M.Fib(n, res); END; / CALL Fib(20, ?); Value of output parameters -------------------------------- RES = 6765