Forum Stats

  • 3,770,167 Users
  • 2,253,079 Discussions
  • 7,875,357 Comments

Discussions

How to generate perfect numbers between 1 to 100 Note.: If sum of the divisors is equal to given num

Albert Chao
Albert Chao Member Posts: 131 Green Ribbon
edited Jul 16, 2021 6:17AM in SQL & PL/SQL

How to generate perfect numbers between 1 to 100 Note.: If sum of the divisors is equal to given num then it is a perfect number.

e.g num = 6

number 6 has divisor as 1,2,3 whose sum is 6. So, 6 is the perfect number.

Tagged:
«13

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,596 Red Diamond

    Wrong. The perfect number is 73. So in PL/SQL:

    create or replace function PerfectNumber( ignoredInput number ) return number is
    begin
        return(73);
    end;
    
    KayKPleiadian
  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Jul 16, 2021 8:36AM

    Possibly not the most efficient way...

    with numbers(num, max_factor) as (
      select level, trunc(level / 2) 
      from dual
      connect by level <= 100
    )
    select x.num as perfect, listagg(n.num, ',') within group (order by n.num) factors
    from numbers n 
    cross join numbers x 
    where mod(x.num, n.num) = 0 and x.num > 1 and n.num <= x.max_factor
    group by x.num
    having sum(n.num) = x.num
    /
    
    
       PERFECT|FACTORS
    ----------|---------------
             6|1,2,3
            28|1,2,4,7,14
    
    Billy Verreynne
  • Albert Chao
    Albert Chao Member Posts: 131 Green Ribbon

    @Paulzip Thanks but I need PL/SQL program to return total number of perfect numbers between 1 to 100 that will start with DECLARE

    BEGIN

    END;

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Jul 16, 2021 8:55AM

    What a ridiculous requirement. SQL will pretty much always outperform PL/SQL, and you are returning a results set, so why would you do that?!

    To me, that sounds akin to

    "I want you to change the clutch on my car but by only using a pair of pliers and a banana."

    declare
      MAX_NUM constant pls_integer := 100;
    begin
      for rec in (
        with numbers(num, max_factor) as (
          select level, trunc(level / 2) 
          from dual
          connect by level <= MAX_NUM
        )
        select x.num as perfect, listagg(n.num, ',') within group (order by n.num) factors
        from numbers n 
        cross join numbers x 
        where mod(x.num, n.num) = 0 and x.num > 1 and n.num <= x.max_factor
        group by x.num
        having sum(n.num) = x.num
        order by 1
      )
      loop
        dbms_output.put_line(rec.perfect || ', factors = '|| rec.factors);
      end loop;
    end;
    /
    
  • Albert Chao
    Albert Chao Member Posts: 131 Green Ribbon

    @Paulzip Thanks for the solution but when I ran in Oracle SQL, there is no output generated for the program which you gave. I am pasting a sample program which is of generating prime numbers between 1 to 100. Could you please re-iterate with the logic to find the perfect numbers.

    DECLARE

       i NUMBER(3);

       j NUMBER(3);

    BEGIN

    dbms_output.Put_line('The prime numbers are:');

                  dbms_output.new_line;

       i := 2;

       LOOP

           j := 2;

           LOOP

               EXIT WHEN( ( MOD(i, j) = 0 )

                           OR ( j = i ) );

               j := j + 1;

           END LOOP;

           IF( j = i )THEN

             dbms_output.Put_line(i||'  ');                                                                                                  

           END IF;

           i := i + 1;

           exit WHEN i = 100;

       END LOOP;

                  dbms_output.new_line;

    END;

  • BluShadow
    BluShadow Member, Moderator Posts: 41,493 Red Diamond


    Really? When I ran Paul's code I got output

    SQL> set serverout on
    SQL> declare
      2    MAX_NUM constant pls_integer := 100;
      3  begin
      4    for rec in (
      5      with numbers(num, max_factor) as (
      6        select level, trunc(level / 2)
      7        from dual
      8        connect by level <= MAX_NUM
      9      )
     10      select x.num as perfect, listagg(n.num, ',') within group (order by n.num) factors
     11      from numbers n
     12      cross join numbers x
     13      where mod(x.num, n.num) = 0 and x.num > 1 and n.num <= x.max_factor
     14      group by x.num
     15      having sum(n.num) = x.num
     16      order by 1
     17    )
     18    loop
     19      dbms_output.put_line(rec.perfect || ', factors = '|| rec.factors);
     20    end loop;
     21  end;
     22  /
    6, factors = 1,2,3
    28, factors = 1,2,4,7,14
    
    PL/SQL procedure successfully completed.
    


    Perhaps you haven't enabled your client to fetch the buffer from the dbms_output?

    Take a read of the community document: https://community.oracle.com/tech/developers/discussion/4417579/pl-sql-101-dbms-output

    Paulzip
  • mathguy
    mathguy Member Posts: 10,164 Blue Diamond

    I assume both the theoretical level (knowledge of number theory) and the programming level (in SQL) are very basic/introductory. If so, perhaps your teacher is expecting something like I show below.

    The procedure below displays all perfect numbers up to n, where n is a given integer (input). However, note that while the procedure runs very fast for all perfect numbers up to 100, or up to 1000, it takes almost 10 seconds on my machine to return all perfect numbers up to 10,000 and I didn't have the patience to let it finish for 100,000. This is definitely not how one would search for perfect numbers. Obviously, that's not the real goal of the exercise; more likely, the real goal is to practice some very basic features of PL/SQL.

    So - here is the procedure, and then an example of how it can be invoked.


    create or replace procedure perfect_numbers_up_to_n (n number) as
      str     varchar2(4000);
      aliquot number;
    begin
      for i in 1 .. n loop
        aliquot := 0;
        for j in 1 .. i-1 loop
          if mod(i, j) = 0 then
            aliquot := aliquot + j;
          end if;
        end loop;
        if i = aliquot then
          str := case when str is not null then str || ',' end || to_char(i);
        end if;
      end loop;
      dbms_output.put_line(str);
    end;
    /
    


    set serveroutput on
    
    begin
      perfect_numbers_up_to_n(1000);
    end;
    /
    
    6,28,496
    
    
    PL/SQL procedure successfully completed.
    
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    edited Jul 17, 2021 8:28PM

    @mathguy for j in 1 .. i-1 loop

    Is very inefficient. Greatest possible divisor of i is trunc(i / 2). Changing for j in 1 .. i-1 loop to for j in 1 .. i / 2 loop would reduce number of iteration by half. And checking str for null is excessive too - we can simple display substr(str,2). Also, if we check for numbers where sum of their divisors is greater than the number itself before reaching last iteration we will see it is 7% for n = 100 and 8.2% for n = 1000, 9.16% for n=10000, so we can exit inner loop if sum of divisors exceeds the i

    create or replace procedure perfect_numbers_up_to_n (n number) as
      str     varchar2(4000);
      aliquot number;
      cnt number := 0;
    begin
      for i in 1 .. n loop
        aliquot := 0;
        for j in 1 .. i / 2 loop
          if mod(i, j) = 0 then
            aliquot := aliquot + j;
            -- Next if statement is for demonstration purpose only and can be removed
            if aliquot > i and j < trunc(i / 2)
              then
                dbms_output.put_line('exiting: i = ' || i || ', trunc( i / 2) = ' || trunc( i / 2) || ', j = ' || j || ', aliquot = ' || aliquot || ' iterations saved = ' || (trunc( i / 2) - j));
                cnt := cnt + 1;
            end if;
            exit when aliquot > i;
          end if;
        end loop;
        if i = aliquot then
          str := str || ',' || to_char(i);
        end if;
      end loop;
      dbms_output.put_line(substr(str,2));
      -- Next line is for demonstration purpose only and can be removed
      dbms_output.put_line('Early exit cnt = ' || cnt || ' or ' || (cnt * 100 / n) || '%');
    end;
    /
    
    Procedure created.
    
    SQL> exec perfect_numbers_up_to_n (100)
    exiting: i = 36, trunc( i / 2) = 18, j = 12, aliquot = 37 iterations saved = 6
    exiting: i = 48, trunc( i / 2) = 24, j = 16, aliquot = 52 iterations saved = 8
    exiting: i = 60, trunc( i / 2) = 30, j = 20, aliquot = 78 iterations saved = 10
    exiting: i = 72, trunc( i / 2) = 36, j = 24, aliquot = 87 iterations saved = 12
    exiting: i = 84, trunc( i / 2) = 42, j = 28, aliquot = 98 iterations saved = 14
    exiting: i = 90, trunc( i / 2) = 45, j = 30, aliquot = 99 iterations saved = 15
    exiting: i = 96, trunc( i / 2) = 48, j = 32, aliquot = 108 iterations saved = 16
    1,6,28
    Early exit cnt = 7 or 7%
    
    PL/SQL procedure successfully completed.
    
    SQL>
    

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    edited Jul 17, 2021 8:18PM

    Oops, 1 should be excluded since perfect number = sum of it divisors excluding the number itself:

    create or replace procedure perfect_numbers_up_to_n (n number) as
      str     varchar2(4000);
      aliquot number;
      cnt number := 0;
    begin
      for i in 2 .. n loop -- exclude 1
        aliquot := 0;
        for j in 1 .. i / 2 loop
          if mod(i, j) = 0 then
            aliquot := aliquot + j;
            if aliquot > i and j < trunc(i / 2)
              then
                dbms_output.put_line('exiting: i = ' || i || ', trunc( i / 2) = ' || trunc( i / 2) || ', j = ' || j || ', aliquot = ' || aliquot || ' iterations saved = ' || (trunc( i / 2) - j));
                cnt := cnt + 1;
            end if;
            exit when aliquot > i;
          end if;
        end loop;
        if i = aliquot then
          str := str || ',' || to_char(i);
        end if;
      end loop;
      dbms_output.put_line(substr(str,2));
      dbms_output.put_line('cnt = ' || cnt || ' or ' || (cnt *100 / n) || '%');
    end;
    /
    
    Procedure created.
    
    SQL> exec perfect_numbers_up_to_n (100)
    exiting: i = 36, trunc( i / 2) = 18, j = 12, aliquot = 37 iterations saved = 6
    exiting: i = 48, trunc( i / 2) = 24, j = 16, aliquot = 52 iterations saved = 8
    exiting: i = 60, trunc( i / 2) = 30, j = 20, aliquot = 78 iterations saved = 10
    exiting: i = 72, trunc( i / 2) = 36, j = 24, aliquot = 87 iterations saved = 12
    exiting: i = 84, trunc( i / 2) = 42, j = 28, aliquot = 98 iterations saved = 14
    exiting: i = 90, trunc( i / 2) = 45, j = 30, aliquot = 99 iterations saved = 15
    exiting: i = 96, trunc( i / 2) = 48, j = 32, aliquot = 108 iterations saved = 16
    6,28
    cnt = 7 or 7%
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL>
    


    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    edited Jul 17, 2021 8:26PM

    This might be more readable:

    create or replace procedure perfect_numbers_up_to_n (n number) as
      str     varchar2(4000);
      aliquot number;
      cnt number := 0;
    begin
      for i in 2 .. n loop -- exclude 1
        aliquot := 0;
        for j in 1 .. i / 2 loop
          if mod(i, j) = 0 then
            aliquot := aliquot + j;
            -- Next if statement is for demonstration purpose only and can be removed
            if aliquot > i and j < trunc(i / 2)
              then
                dbms_output.put_line('exiting: number = ' || i || ', max divisor = ' || trunc( i / 2) || ', iteration = ' || j || ', sum of divisors = ' || aliquot || ' iterations saved = ' || (trunc( i / 2) - j));
                cnt := cnt + 1;
            end if;
            exit when aliquot > i;
          end if;
        end loop;
        if i = aliquot then
          str := str || ',' || to_char(i);
        end if;
      end loop;
      dbms_output.put_line('Perfect numbers: ' || substr(str,2));
      -- Next line is for demonstration purpose only and can be removed
      dbms_output.put_line('Early exit cnt = ' || cnt || ' or ' || (cnt *100 / n) || '%');
    end;
    /
    
    Procedure created.
    
    SQL> exec perfect_numbers_up_to_n (100)
    exiting: number = 36, max divisor = 18, iteration = 12, sum of divisors = 37 iterations saved = 6
    exiting: number = 48, max divisor = 24, iteration = 16, sum of divisors = 52 iterations saved = 8
    exiting: number = 60, max divisor = 30, iteration = 20, sum of divisors = 78 iterations saved = 10
    exiting: number = 72, max divisor = 36, iteration = 24, sum of divisors = 87 iterations saved = 12
    exiting: number = 84, max divisor = 42, iteration = 28, sum of divisors = 98 iterations saved = 14
    exiting: number = 90, max divisor = 45, iteration = 30, sum of divisors = 99 iterations saved = 15
    exiting: number = 96, max divisor = 48, iteration = 32, sum of divisors = 108 iterations saved = 16
    Perfect numbers: 6,28
    Early exit cnt = 7 or 7%
    
    PL/SQL procedure successfully completed.
    
    SQL>
    

    SY.