## Forum Stats

• 3,770,167 Users
• 2,253,079 Discussions

Discussions

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

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

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

```create or replace function PerfectNumber( ignoredInput number ) return number is
begin
return(73);
end;
```
• 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
```
• @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;

• 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;
/
```
• @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;

• 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

• 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.
```
• @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.

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

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