Fill Database With Recursive Factorial Function

Question

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.

Answer 1

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             |
------------------------------