SQL & PL/SQL

Do you know what makes numbers prime? Do you know how to write a PL/SQL code, then there you are!!! (Or search for the codes online). Please don't treat the forum as class room training providing institution.

*009*

Edited by: 009 on Aug 25, 2010 10:50 PM

 declare
   Flag Boolean:=FALSE;
   begin
   for i in 2..10 LOOP
     for j in 2..i-1 LOOP
      if mod(i,j)=0 then
          DBMS_OUTPUT.PUT_LINE(i||' Not a PRIME');
          Flag:=TRUE;
          exit;
      end if;
     end loop;
        IF Flag != TRUE THEN
          DBMS_OUTPUT.PUT_LINE(i||' PRIME');
        END IF;
        Flag:=FALSE;
     end loop;
  end;
/
2 PRIME
3 PRIME
4 Not a PRIME
5 PRIME
6 Not a PRIME
7 PRIME
8 Not a PRIME
9 Not a PRIME
10 Not a PRIME

Try this:

SQL> ed
Wrote file afiedt.buf
 
  1  DECLARE
  2  v_num NUMBER := &1;
  3  v_prime_flag NUMBER := 0;
  4  BEGIN
  5  FOR I IN 1..v_num LOOP
  6  FOR J IN 2..TRUNC(I/2) LOOP
  7  IF (MOD(I,J) = 0) THEN
  8  v_prime_flag := 1;
  9  EXIT;
 10  END IF;
 11  END LOOP;
 12  CASE (v_prime_flag)
 13  WHEN 0 THEN
 14  DBMS_output.put_line(I ||' is a prime Number');
 15  WHEN 1 THEN
 16  DBMS_output.put_line(I ||' is not a prime Number');
 17  END CASE;
 18  v_prime_flag := 0;
 19  END LOOP;
 20* END;
SQL> / 
Enter value for 1: 10
old   2: v_num NUMBER := &1;
new   2: v_num NUMBER := 10;
1 is a prime Number
2 is a prime Number
3 is a prime Number
4 is not a prime Number
5 is a prime Number
6 is not a prime Number
7 is a prime Number
8 is not a prime Number
9 is not a prime Number
10 is not a prime Number
 
PL/SQL procedure successfully completed.
 
SQL> / 
Enter value for 1: 20
old   2: v_num NUMBER := &1;
new   2: v_num NUMBER := 20;
1 is a prime Number
2 is a prime Number
3 is a prime Number
4 is not a prime Number
5 is a prime Number
6 is not a prime Number
7 is a prime Number
8 is not a prime Number
9 is not a prime Number
10 is not a prime Number
11 is a prime Number
12 is not a prime Number
13 is a prime Number
14 is not a prime Number
15 is not a prime Number
16 is not a prime Number
17 is a prime Number
18 is not a prime Number
19 is a prime Number
20 is not a prime Number
 
PL/SQL procedure successfully completed.
 
SQL> / 
Enter value for 1: 12
old   2: v_num NUMBER := &1;
new   2: v_num NUMBER := 12;
1 is a prime Number
2 is a prime Number
3 is a prime Number
4 is not a prime Number
5 is a prime Number
6 is not a prime Number
7 is a prime Number
8 is not a prime Number
9 is not a prime Number
10 is not a prime Number
11 is a prime Number
12 is not a prime Number
 
PL/SQL procedure successfully completed.
 

a prime number (or a prime) is a natural number that has exactly two distinct natural number divisors: 1 and itself.
so 1 is not a prime number.

This is for your info.

Edited by: Lokanath Giri on २६ अगस्त, २०१० १२:४२ अपराह्न

Anyone for a bit of recursion? :D

create or replace function test_prime(p_prime in number) return varchar2 is
  function f_prime(p_prime in number, p_divisor in number) return number is
  begin
    return case when p_divisor >= p_prime then 0 else case when mod(p_prime, p_divisor) = 0 then 1 else 0 end + f_prime(p_prime, p_divisor+1) end;
  end;
begin
  return case when f_prime(p_prime, 2) > 0 or p_prime = 1 then 'Not ' else null end || 'Prime';
end;
/


SQL> select rownum, test_prime(rownum) as prime from dual connect by rownum <= 50;

    ROWNUM PRIME
---------- ----------
         1 Not Prime
         2 Prime
         3 Prime
         4 Not Prime
         5 Prime
         6 Not Prime
         7 Prime
         8 Not Prime
         9 Not Prime
        10 Not Prime
        11 Prime
        12 Not Prime
        13 Prime
        14 Not Prime
        15 Not Prime
        16 Not Prime
        17 Prime
        18 Not Prime
        19 Prime
        20 Not Prime
        21 Not Prime
        22 Not Prime
        23 Prime
        24 Not Prime
        25 Not Prime
        26 Not Prime
        27 Not Prime
        28 Not Prime
        29 Prime
        30 Not Prime
        31 Prime
        32 Not Prime
        33 Not Prime
        34 Not Prime
        35 Not Prime
        36 Not Prime
        37 Prime
        38 Not Prime
        39 Not Prime
        40 Not Prime
        41 Prime
        42 Not Prime
        43 Prime
        44 Not Prime
        45 Not Prime
        46 Not Prime
        47 Prime
        48 Not Prime
        49 Not Prime
        50 Not Prime

50 rows selected.

SQL>

SQL> with t as (    select level n
                 from dual
           connect by level <= 50)
select n,
       case
          when exists
                  (select null
                     from t t2
                    where t2.n > 1 and t2.n < t.n and t.n / t2.n = trunc (t.n / t2.n)) then 'no prime'
          else 'prime'
       end "Prime?"
  from t
/
         N Prime?  
---------- --------
         1 prime   
         2 prime   
         3 prime   
         4 no prime
         5 prime   
         6 no prime
         7 prime   
         8 no prime
         9 no prime
        10 no prime
        11 prime   
        12 no prime
        13 prime   
        14 no prime
        15 no prime
        16 no prime
        17 prime   
        18 no prime
        19 prime   
        20 no prime
        21 no prime
        22 no prime
        23 prime   
        24 no prime
        25 no prime
        26 no prime
        27 no prime
        28 no prime
        29 prime   
        30 no prime
        31 prime   
        32 no prime
        33 no prime
        34 no prime
        35 no prime
        36 no prime
        37 prime   
        38 no prime
        39 no prime
        40 no prime
        41 prime   
        42 no prime
        43 prime   
        44 no prime
        45 no prime
        46 no prime
        47 prime   
        48 no prime
        49 no prime
        50 no prime

50 rows selected.

Nice one Michael! ;)

There are many ways to create some prime numbers. Here is another one:

all output is prime

select level*level+level+41 prime
from dual
connect by level < 40;


PRIME
-----
43
47
53
61
71
83
97
113
131
151
173
197
223
251
281
313
347
383
421
461
503
547
593
641
691
743
797
853
911
971
1033
1097
1163
1231
1301
1373
1447
1523
1601

Please do not forget model clause ;-)

select *
  from dual
 model
dimension by(2 as soeji)
measures(0 as cnt)
rules(
cnt[for soeji from 2 to 25 increment 1] order by soeji=
count(*)[cv() > soeji and mod(cv(),soeji) = 0]);

SOEJI  CNT
-----  ---
    2    0
    3    0
    4    1
    5    0
    6    2
    7    0
    8    2
    9    1
   10    2
   11    0
   12    4
   13    0
   14    2
   15    2
   16    3
   17    0
   18    4
   19    0
   20    4
   21    2
   22    2
   23    0
   24    6
   25    1

As for WikiPedia..i found '1' is not prime number So the Programe is
http://en.wikipedia.org/wiki/Prime_number



DECLARE

n NUMBER;

i NUMBER;

pr NUMBER;

BEGIN

FOR n IN 2 .. 100
LOOP

pr := 1;

FOR i IN 2 .. n / 2
LOOP

IF MOD(n, i) = 0 THEN

pr := 0;

END IF;

END LOOP;

IF pr = 1 THEN

DBMS_OUTPUT.PUT_LINE(n);

END IF;

END LOOP;

END;



OUT PUT:
anonymous block completed

2
3
5
7
11
13
17
19
23
29
31
37
41
43
47
53
59
61
67
71
73
79
83
89
97



Enjoy...

Edited by: Ashok on Aug 26, 2010 5:34 AM

Edited by: Ashok on 26-Aug-2010 05:44

Regards,
Ashok Pantam

Edited by: Ashok on 26-Aug-2010 20:37

No! Please! Let me forget the model clause. ;)

I think this is a task where the model clause really shines. :)

Based on Michael`s:

with t as (    select level n
                 from dual
           connect by level <= 50)           
           
select n from t where not exists
                  (select null
                     from t t2
                    where t2.n > 1 and t2.n < t.n and t.n / t2.n = trunc (t.n / t2.n));
-------
1
2
3
5
7
11
13
17
19
23
29
31
37
41
43
47

:D

Hai..user8731258

is it my program use full to u?

Everyone's method is useful to the OP if it generates prime numbers. Anyone would think you were asking for points. Shame on you!