2

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
  • 1
    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
0
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             |
------------------------------
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.