使用SQL判断一个数是否质数

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

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

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

SQL> UNDEF NUM
5lSi
{*P2][H\(_5D0SQL> WITHITPUB个人空间vC2E@e q@Q^
  2  T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= &&NUM/2)ITPUB个人空间:I]? xToV
  3  SELECT &NUMITPUB个人空间0YEJk+`w&Mfq
  4   || DECODEITPUB个人空间%zN#H%z1S2Sx
  5    (ITPUB个人空间Q"Q"l(`#['U4N1tM_
  6     (ITPUB个人空间(G#aZFt7a
  7      SELECT &NUM FROM DUALITPUB个人空间$r9u.N;z7fA
  8      MINUS
I'z*x8US'Ec!C0  9      SELECT A.RN * B.RN FROM T A, T BITPUB个人空间K Xt%JT2V@
 10      WHERE A.RN <= ROUND(POWER(&NUM, 0.5))
t3Clz |Ht0 11      AND B.RN >= ROUND(POWER(&NUM, 0.5))ITPUB个人空间
Gm:jS#l aW _
 12     ),ITPUB个人空间uh/p/T \~3~
 13     NULL,ITPUB个人空间K*uK?4bol
 14     '不'
6LD+Y~9VC0 15    )
O0o3xu^
Tg0 16   || '是质数'
xY6Ts
j1HsH6^:dX0 17  FROM DUAL;
!|1az;u9}0输入num的值:  23
v;z]3z~bX0原值    2: T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= &&NUM/2)
a [;V,F&Uad6sy0新值    2: T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= 23/2)
s5iZQ wjO0原值    3: SELECT &NUM
{+Z&KO:L1X#f6R0新值    3: SELECT 23
/jkMv$Kj3fN0原值    7:     SELECT &NUM FROM DUAL
u+K?2fk9AX3K;oX1x:B0新值    7:     SELECT 23 FROM DUAL
,P!t|&r;My0原值   10:     WHERE A.RN <= ROUND(POWER(&NUM, 0.5))
6t-lSXZx7N0新值   10:     WHERE A.RN <= ROUND(POWER(23, 0.5))
OJ ?
rU#~(w9i0原值   11:     AND B.RN >= ROUND(POWER(&NUM, 0.5))
3Jhu0m:Y
W3L)Z1Y0新值   11:     AND B.RN >= ROUND(POWER(23, 0.5))

23||DECODEITPUB个人空间F!AEw^ Y5eCZ#c
----------ITPUB个人空间F(F w Bv/Qhj(t
23是质数

SQL> UNDEF NUMITPUB个人空间G*E?J6D kL0P`
SQL> WITH
C~bh9Npg0  2  T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= &&NUM/2)
4Wi[%@+Zho{`.d0  3  SELECT &NUMITPUB个人空间V2o-L0{vqV
  4   || DECODE
A-v!w%oG6|\2r0  5    (ITPUB个人空间sRqDx B.YFX
  6     (ITPUB个人空间;?
tu []k,Xy
  7      SELECT &NUM FROM DUAL
r f r&hDrX3D0  8      MINUSITPUB个人空间&FbO-Btf9Y
  9      SELECT A.RN * B.RN FROM T A, T B
L[S]F*I0 10      WHERE A.RN <= ROUND(POWER(&NUM, 0.5))ITPUB个人空间T#F9Ut*uD*WZ}F
 11      AND B.RN >= ROUND(POWER(&NUM, 0.5))ITPUB个人空间~7RG
vMW(iL|1N
 12     ),
/d'X#s6ym%Q+w(AZ0 13     NULL,ITPUB个人空间wC+TM*U
 14     '不'ITPUB个人空间bQaS
f8CB4x*K1R
 15    )ITPUB个人空间2odJ&A.Xv:uL
 16   || '是质数'
%a+z%H;k4kz+Zx0 17  FROM DUAL;
"qA0H
m9iy5}0输入num的值:  25ITPUB个人空间o|^k$R.}
原值    2: T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= &&NUM/2)ITPUB个人空间&wA)F P}~ D{!_$C
新值    2: T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= 25/2)
0eXG%|&g1e&Hr0原值    3: SELECT &NUM
t4Yt},cr9aH3F
~yd0新值    3: SELECT 25ITPUB个人空间OBPQpD,L \xi
原值    7:     SELECT &NUM FROM DUALITPUB个人空间T$F'uy%^.^
新值    7:     SELECT 25 FROM DUAL
a b~(zH:Jw
cPu-O0原值   10:     WHERE A.RN <= ROUND(POWER(&NUM, 0.5))ITPUB个人空间g/@A XA\#w7?
新值   10:     WHERE A.RN <= ROUND(POWER(25, 0.5))ITPUB个人空间G4P[s `,W'm&k
原值   11:     AND B.RN >= ROUND(POWER(&NUM, 0.5))
{n8E2Yj+S(adXOA0新值   11:     AND B.RN >= ROUND(POWER(25, 0.5))

25||DECODE
)\4UQ
wo.ny-k0----------
6N&Rk8J-CnZ v025不是质数

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

SQL> UNDEF NUM
(oH\az;T0SQL> WITHITPUB个人空间*|7Z F&Q1x e.TI
  2  T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= &&NUM/2)
exidk2P:?JBs7X'vB0  3  SELECT &NUMITPUB个人空间L6d#\H%sN[%[
  4   || DECODEITPUB个人空间ub ?"M`%`0K
  5    (ITPUB个人空间*B8A#_[2E
  6     (
.T"f$mgt7h'klTG0  7      SELECT &NUM FROM DUAL
RnJ.} dBZ-a0  8      MINUS
9j [${'rd5j h
cow7m0  9      SELECT A.RN * B.RN FROM T A, T BITPUB个人空间|Is\%j:]7N
 10      WHERE A.RN <= POWER(&NUM, 0.5)
{] O d1` w(|0 11      AND B.RN >= POWER(&NUM, 0.5)ITPUB个人空间2T(GyB,^.rnBn?8{
 12     ),ITPUB个人空间)I+M,jNJ?
 13     NULL,ITPUB个人空间({!i/kr k"`
 14     '不'ITPUB个人空间Z$@2Ho.R K5`L
 15    )ITPUB个人空间lPVl;gZ
os$G
 16   || '是质数'
k!eC"dN*O'b0 17  FROM DUAL;
#M/D;R'G9Aw0输入num的值:  49ITPUB个人空间nX4tf3@c*f^
原值    2: T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= &&NUM/2)
1e hb$C!B&j0新值    2: T AS (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= 49/2)ITPUB个人空间3\zCn#qv"?7K?9k
原值    3: SELECT &NUM
(LV'A'IR,XH0新值    3: SELECT 49
Q.k*L Ma3Q:u)~
D0原值    7:     SELECT &NUM FROM DUAL
HKS2pO;wk(i`0新值    7:     SELECT 49 FROM DUAL
I+m8Upfi)[O
D0原值   10:     WHERE A.RN <= POWER(&NUM, 0.5)
UT9B
uRPU4f]0新值   10:     WHERE A.RN <= POWER(49, 0.5)
Re(e e?1y)B1Sp%]0原值   11:     AND B.RN >= POWER(&NUM, 0.5)
5nS'G8T \:QmYL*w/{0新值   11:     AND B.RN >= POWER(49, 0.5)

49||DECODEITPUB个人空间 W @C+@)aq2Q,Q
----------ITPUB个人空间j vSk/u1w9Y
49是质数

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

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

TO_CHAR(POWER(49,0.5))
#E-B9S Zd9k2I/W0----------------------------------------ITPUB个人空间#C;V!}qZU+n@
7.00000000000000000000000000000000000003

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