prime2nd

    SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 4 15:06:58 2009    Copyright (c) 1982, 2005, Oracle.  All rights reserved.      Connected to:  Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production    

Table created.

Table created.

Function created.

MODLIST(11,CURSOR(SELECT2FROMDUALUNIONSELECT11FROMDUAL))
1

Type created.

Function created.

Function created.

Procedure created.

1 row created.

1 row created.

PL/SQL procedure successfully completed.

P
2
3
5
7

PL/SQL procedure successfully completed.

P
2
3
5
7
11
13
17
19
23

9 rows selected.

PL/SQL procedure successfully completed.

COUNT(*)
61

1 create global temporary table tmp (p number)
2 /
3 create global temporary table prime (p number) on commit preserve rows
4 /
5 create or replace function
6 modlist (i number, rc sys_refcursor) return number as
7 d number;
8 begin loop
9 fetch rc into d;
10 exit when rc%notfound;
11 if mod(i,d)=0 then begin close rc; return 1; end; end if;
12 end loop; close rc; return 0; end;
13 /
14 select modlist(11, cursor(
15 select 2 from dual union select 11 from dual
16 )) from dual
17 /
18 create or replace type ton is table of number;
19 /
20 create or replace function seqr(l number, h number) return ton pipelined as begin for i in l+1 .. h-1 loop pipe row(i); end loop; end;
21 /
22 create or replace function
23 pth(r number) return number as
24 begin
25 for i in (
26 select max(p) c from (
27 select * from prime where rownum<=r
28 )
29 ) loop return i.c; end loop;
30 end;
31 /
32 create or replace procedure
33 prime_loop(k number) as begin
34 insert into tmp
35 select * from table(
36 select seqr(l,h) from (
37 select min(p*p) l, max(p*p) h from (
38 select * from prime where p>=pth(k) order by p
39 ) where rownum<=2)
40 )
41 where modlist(column_value,cursor(
42 select * from prime where p<=pth(k)
43 ))=0;
44 insert into prime select * from tmp;
45 commit;
46 end;
47 /
48 insert into prime values (2)
49 /
50 insert into prime values (3)
51 /
52 exec prime_loop(1)
53 select * from prime;
54 exec prime_loop(2)
55 select * from prime;
56 exec for i in 3..6 loop prime_loop(i); end loop
57 select count(*) from prime
58 /
59* get prime
SQL>

  Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production