用SQL计算100以内的质数

PLSQL

SQL> WITH T
2  AS
3  (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL < 100)
4  SELECT RN FROM T
5  WHERE RN > 1
6  MINUS
7  SELECT A.RN * B.RN FROM T A, T B
8  WHERE A.RN <= B.RN
9  AND A.RN > 1
10  AND B.RN > 1;

RN
----------
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

SQL> SET AUTOT TRACE STAT
SQL> WITH T
2  AS
3  (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL < 10000)
4  SELECT RN FROM T
5  WHERE RN > 1
6  MINUS
7  SELECT A.RN * B.RN FROM T A, T B
8  WHERE A.RN <= B.RN
9  AND A.RN > 1
10  AND B.RN > 1;

----------------------------------------------------------
511  recursive calls
81  db block gets
180002  consistent gets
648  redo size
17139  bytes sent via SQL*Net to client
1276  bytes received via SQL*Net from client
83  SQL*Net roundtrips to/from client
2  sorts (memory)
1  sorts (disk)
1229  rows processed

SQL> WITH T
2  AS
3  (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL < 10000)
4  SELECT RN FROM T
5  WHERE RN > 1
6  MINUS
7  SELECT A.RN * B.RN FROM T A, T B
8  WHERE A.RN <= B.RN
9  AND A.RN > 1
10  AND A.RN <= 100
11  AND B.RN > 1
12  AND B.RN <= 5000;

----------------------------------------------------------
2  recursive calls
23  db block gets
1820  consistent gets
692  redo size
17139  bytes sent via SQL*Net to client
1276  bytes received via SQL*Net from client
83  SQL*Net roundtrips to/from client
3  sorts (memory)
0  sorts (disk)
1229  rows processed

SQL> WITH T
2  AS
3  (SELECT ROWNUM * 2 + 1 RN FROM DUAL CONNECT BY LEVEL < 4999)
4  SELECT 2 FROM DUAL
5  UNION ALL
6  (
7   SELECT RN FROM T
8   WHERE RN > 1
9   MINUS
10   SELECT A.RN * B.RN FROM T A, T B
11   WHERE A.RN <= B.RN
12   AND A.RN > 1
13   AND A.RN <= 100
14   AND B.RN > 1
15   AND B.RN <= 5000
16  )
17  ;

----------------------------------------------------------
2  recursive calls
15  db block gets
512  consistent gets
648  redo size
17138  bytes sent via SQL*Net to client
1276  bytes received via SQL*Net from client
83  SQL*Net roundtrips to/from client
3  sorts (memory)
0  sorts (disk)
1229  rows processed

SQL> DECLARE
2   TYPE T_RECORD IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3   V_RESULT T_RECORD;
4   I NUMBER DEFAULT 3;
5   N NUMBER DEFAULT 0;
6  BEGIN
7   --DBMS_OUTPUT.PUT_LINE(2);
8   V_RESULT(1) := 3;
9   WHILE(I < 10000) LOOP
10    FOR J IN 1..V_RESULT.COUNT LOOP
11     IF V_RESULT(J) * V_RESULT(J) > I THEN
12      --DBMS_OUTPUT.PUT_LINE(I);
13      V_RESULT(V_RESULT.COUNT + 1) := I;
14      EXIT;
15     END IF;
16     IF TRUNC(I/V_RESULT(J)) = I/V_RESULT(J) THEN
17      EXIT;
18     END IF;
19    END LOOP;
20    IF N = 2 THEN
21     I := I + 4;
22     N := 1;
23    ELSE
24     I := I + 2;
25     N := N + 1;
26    END IF;
27   END LOOP;
28   V_RESULT(0) := 2;
29  END;
30  /

PL/SQL 过程已成功完成。

SQL>

• 博文量
1954
• 访问量
10903468