前两天用SQL计算一个数以内的所有质数，这里补一篇判断一个数是否是质数的SQL实现。

用SQL计算100以内的质数：http://yangtingkun.itpub.net/post/468/450278

这个SQL的实现方法前面那个SQL很类似：

SQL> UNDEF NUM
SQL> WITH
2 T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= &&NUM/2)
3 SELECT &NUM
4 || DECODE
5 (
6 (
7 SELECT &NUM FROM DUAL
8 MINUS
9 SELECT A.RN * B.RN FROM T A, T B
10 WHERE A.RN <= ROUND(POWER(&NUM, 0.5))
11 AND B.RN >= ROUND(POWER(&NUM, 0.5))
12 ),
13 NULL,
14 '不'
15 )
16 || '是质数'
17 FROM DUAL;输入 num 的值: 23原值 2: T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= &&NUM/2)新值 2: T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= 23/2)原值 3: SELECT &NUM新值 3: SELECT 23原值 7: SELECT &NUM FROM DUAL新值 7: SELECT 23 FROM DUAL原值 10: WHERE A.RN <= ROUND(POWER(&NUM, 0.5))新值 10: WHERE A.RN <= ROUND(POWER(23, 0.5))原值 11: AND B.RN >= ROUND(POWER(&NUM, 0.5))新值 11: AND B.RN >= ROUND(POWER(23, 0.5))

23||DECODE
----------
23是质数

SQL> UNDEF NUM
SQL> WITH
2 T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= &&NUM/2)
3 SELECT &NUM
4 || DECODE
5 (
6 (
7 SELECT &NUM FROM DUAL
8 MINUS
9 SELECT A.RN * B.RN FROM T A, T B
10 WHERE A.RN <= ROUND(POWER(&NUM, 0.5))
11 AND B.RN >= ROUND(POWER(&NUM, 0.5))
12 ),
13 NULL,
14 '不'
15 )
16 || '是质数'
17 FROM DUAL;输入 num 的值: 25原值 2: T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= &&NUM/2)新值 2: T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= 25/2)原值 3: SELECT &NUM新值 3: SELECT 25原值 7: SELECT &NUM FROM DUAL新值 7: SELECT 25 FROM DUAL原值 10: WHERE A.RN <= ROUND(POWER(&NUM, 0.5))新值 10: WHERE A.RN <= ROUND(POWER(25, 0.5))原值 11: AND B.RN >= ROUND(POWER(&NUM, 0.5))新值 11: AND B.RN >= ROUND(POWER(25, 0.5))

www.ixdba.net

25||DECODE
----------
25不是质数

需要注意一点，对于开方结果的四舍五入是很必要的，否则可能会得到错误的结果：

SQL> UNDEF NUM
SQL> WITH
2 T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= &&NUM/2)
3 SELECT &NUM
4 || DECODE
5 (
6 (
7 SELECT &NUM FROM DUAL
8 MINUS
9 SELECT A.RN * B.RN FROM T A, T B
10 WHERE A.RN <= POWER(&NUM, 0.5)
11 AND B.RN >= POWER(&NUM, 0.5)
12 ),
13 NULL,
14 '不'
15 )
16 || '是质数'
17 FROM DUAL;输入 num 的值: 49原值 2: T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= &&NUM/2)新值 2: T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= 49/2)原值 3: SELECT &NUM新值 3: SELECT 49原值 7: SELECT &NUM FROM DUAL新值 7: SELECT 49 FROM DUAL原值 10: WHERE A.RN <= POWER(&NUM, 0.5)新值 10: WHERE A.RN <= POWER(49, 0.5)原值 11: AND B.RN >= POWER(&NUM, 0.5)新值 11: AND B.RN >= POWER(49, 0.5)