前两天用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本身还可以进行部分优化,这里就不描述了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-134196/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-134196/