使用SQL判斷一個數是否質數

前兩天用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本身還可以進行部分最佳化，這裡就不描述了。