使用遞歸階乘函式填充數據庫

我想計算表的所有行的階乘值。然後我想插入表中每一行的階乘值。所以我創建如下表：

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

首先，表格只有數字。我用呼叫遞歸函式事實填充階乘欄位。階乘函式如下。

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

階乘函式有效。我想選擇數組中的所有表號。然後用數組的元素呼叫階乘函式。畢竟我想填寫表階乘的欄位。對於他們所有人，我寫 sp 如下：

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

但是這段程式碼用相同的值填充所有階乘欄位。這個相同的值是表格的最後一個元素的階乘值。表格如下：

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

FACTORIAL 表的最後一個元素 1. 程式碼插入所有欄位最後一個元素的階乘值，即 1。如何插入我在 FACTORIALS 表中計算正確位置的階乘值。

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

問題解決了。我有兩個在 PL/SQL 程式碼中顯示的解決方案。當我執行此程式碼然後執行 FACTORIALS 表時，如下所示：

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

引用自：https://dba.stackexchange.com/questions/123149