前两天用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))

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)

49||DECODE
----------
49是质数

导致这个问题的原因就是Oracle的POWER函数计算精度：

SQL> SELECT TO_CHAR(POWER(49, 0.5)) FROM DUAL;

TO_CHAR(POWER(49,0.5))
----------------------------------------
7.00000000000000000000000000000000000003

这个SQL只适用于大于2的整数。SQL本身还可以进行部分优化，这里就不描述了。