# Fill Database With Recursive Factorial Function

I want to calculate table's all of the rows's factorials value. And then i want to insert to each row's factorial value in table. so that i create table like as below:

``````TABLE:FACTORIALS
------------------------------
| NUMBERS | FACTORIALS       |
|----------------------------|
| 3       |                  |
| 5       |                  |
| 6       |                  |
| 9       |                  |
| 7       |                  |
| 2       |                  |
| 1       |                  |
------------------------------
``````

First, table has only numbers. I fill the factorial fields with call recursive function fact. Factorial function like as below.

``````CREATE OR REPLACE FUNCTION FACT(x IN number)
RETURN NUMBER
IS
f NUMBER;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * FACT(x-1);
END IF;
RETURN f;
END;
/
``````

Factorial function works. And i want to select all table numbers in array. Then call factorial function with array's elements. After all i want to fill table factorials's field. For all of them i write sp like as below:

``````DECLARE
COUNTER INTEGER := 0;
CURSOR C_NUMBERS IS SELECT  NUMBERS FROM FACTORIALS;
TYPE N_LIST IS VARRAY(7) OF FACTORIALS.NUMBERS%TYPE;
TYPE F_LIST IS VARRAY(7) OF FACTORIALS.FACTORIALS%TYPE;
NUMBER_LIST N_LIST := N_LIST();
FACTORIAL_LIST F_LIST := F_LIST();
BEGIN

FOR N IN C_NUMBERS LOOP
COUNTER := COUNTER + 1;
NUMBER_LIST.EXTEND;
NUMBER_LIST(COUNTER) := N.NUMBERS;
END LOOP;

FOR I IN 1 .. NUMBER_LIST.COUNT LOOP
FACTORIAL_LIST.EXTEND;
FACTORIAL_LIST(I) := FACT(NUMBER_LIST(I));
END LOOP;

FOR K IN 1..FACTORIAL_LIST.COUNT LOOP
UPDATE FACTORIALS
SET FACTORIALS = FACTORIAL_LIST(K)
WHERE FACT(NUMBER_LIST(K)) = FACTORIAL_LIST(K);
END LOOP;

END;
/
``````

But this code fill all the factorials fields with same value. This same value is table's last elements's factorial value.The table like as below :

``````* FACTORIALS TABLE AFTER THE SP IS RAN
------------------------------
| NUMBERS | FACTORIALS       |
|----------------------------|
| 3       |    1             |
| 5       |    1             |
| 6       |    1             |
| 9       |    1             |
| 7       |    1             |
| 2       |    1             |
| 1       |    1             |
------------------------------
``````

FACTORIAL table's last element 1. Code insert all field last element's factorial value namely 1. How can i insert factorial values which i calculate right place in FACTORIALS table.

• You indicate that you realize that the function is getting called 4 times (with parameters of 3, 2, 1, and 0). You know that the function prints a line every time it is called. So why are you surprised that 4 lines of output are written rather than 1? – Justin Cave Dec 7 '15 at 22:39
• For example which value is returned for first value 3 and so on. I wonder how does it work and in-order operation steps. – Mert Özoğul Dec 7 '15 at 22:54
• Apparently (and not surprisingly), the recursive calls return like the innermost one first, the outermost one last. The call to `fact()` precedes the `put_line()`. – dezso Dec 8 '15 at 9:47

``````DECLARE
COUNTER INTEGER := 0;
CURSOR C_NUMBERS IS SELECT  NUMBERS FROM FACTORIALS;
TYPE N_LIST IS VARRAY(7) OF FACTORIALS.NUMBERS%TYPE;
TYPE F_LIST IS VARRAY(7) OF FACTORIALS.FACTORIALS%TYPE;
NUMBER_LIST N_LIST := N_LIST();
FACTORIAL_LIST F_LIST := F_LIST();
BEGIN
FOR N IN C_NUMBERS LOOP
COUNTER := COUNTER + 1;
NUMBER_LIST.EXTEND;
NUMBER_LIST(COUNTER) := N.NUMBERS;
END LOOP;

FOR I IN 1 .. NUMBER_LIST.COUNT LOOP
FACTORIAL_LIST.EXTEND;
FACTORIAL_LIST(I) := F_FACT_NUMBER(NUMBER_LIST(I));
END LOOP;
/***************************FIRST SOLUTION***************************/
FOR K IN 1..NUMBER_LIST.COUNT LOOP
UPDATE FACTORIALS
SET FACTORIALS = FACTORIAL_LIST(K)
WHERE NUMBERS = NUMBER_LIST(K);
END LOOP;
/***************************SECOND SOLUTION**************************/
FORALL INDX IN NUMBER_LIST.FIRST..NUMBER_LIST.LAST
UPDATE FACTORIALS SET FACTORIALS = FACTORIAL_LIST(INDX)
WHERE NUMBERS = NUMBER_LIST(INDX);
END;
/
``````

Problem is solved. I have two solutions which are showed in the PL/SQL code. When i execute this code and then the FACTORIALS table like as below:

``````* FACTORIALS TABLE AFTER THE SP IS RAN
------------------------------
| NUMBERS | FACTORIALS       |
|----------------------------|
| 3       |    6             |
| 5       |    120           |
| 6       |    720           |
| 9       |    362880        |
| 7       |    5040          |
| 2       |    2             |
| 1       |    1             |
------------------------------
``````