数值超过NUMBER最大表示范围的问题（五）

昨天同事给我出了一道简单的题——计算1阶乘到100阶乘的累加。问题似乎很简单，但是最终引出的问题并不简单。

数值超过NUMBER最大表示范围的问题（一）：http://yangtingkun.itpub.net/post/468/234035

数值超过NUMBER最大表示范围的问题（二）：http://yangtingkun.itpub.net/post/468/239478

数值超过NUMBER最大表示范围的问题（三）：http://yangtingkun.itpub.net/post/468/240683

数值超过NUMBER最大表示范围的问题（四）：http://yangtingkun.itpub.net/post/468/241044

t4Z!{5Y"f ^2E8vz8Z0

上面一篇文章给出了利用字符串的“加”和“乘”的方法，在ORACLE的PL/SQL中解决这个问题。其中，由于要保证精度，加法和乘法都对输入值进行限制，将计算范围缩短到ORACLE的NUMBER精度范围之内，通过函数的多次递归来得到最终结果。

不过，两个函数相对比较复杂，可读性较差，于是这里给出一个利用循环算法的解决方案，并利用Oracle的对象功能，将数据和方法封装起来。

SQL> CREATE OR REPLACE TYPE T_BIG_NUMBER IS OBJECT ITPUB个人空间W4I8@Pn~2bMpG
2 (ITPUB个人空间zWa K:yg
3 BIG_NUMBER VARCHAR2(32767),
:vHl;g\0 4 NUMBER_LENGTH NUMBER, ITPUB个人空间7x'~1E8^ R;r
5 STATIC FUNCTION F_T_BIG_NUMBER (P_BIG_NUMBER IN VARCHAR2) RETURN T_BIG_NUMBER, ITPUB个人空间d"{fg6?"u
6 MEMBER FUNCTION ADDS(P_ADD IN T_BIG_NUMBER) RETURN T_BIG_NUMBER,
-i/e,as k0 7 MEMBER FUNCTION MULTIS(P_MULTI IN T_BIG_NUMBER) RETURN T_BIG_NUMBER, ITPUB个人空间pM9Y_"^/C9V%bR(|
8 MEMBER FUNCTION SCIENT_OUTPUT(P_VALUE IN NUMBER) RETURN VARCHAR2,
7xoK&Y_2w/Y0 9 STATIC FUNCTION F_MULTI_SUM_OBJECT(P_IN IN NUMBER, P_SIENCT IN NUMBER DEFAULT NULL) RETURN VARCHAR2 ITPUB个人空间\Z%G+fpW!G(~!EsL
10 );
Ak7JAVv0 11 /

类型已创建。

SQL> CREATE OR REPLACE TYPE BODY T_BIG_NUMBER ASITPUB个人空间MX,kR3E
2 STATIC FUNCTION F_T_BIG_NUMBER (P_BIG_NUMBER IN VARCHAR2) RETURN T_BIG_NUMBER AS
r
o,tt+lc4q__0 3 V_RESULT T_BIG_NUMBER := T_BIG_NUMBER('0', 1);
xO/W#HDQ'T0 4 BEGIN
'k_KM8V[0 5 V_RESULT.BIG_NUMBER := P_BIG_NUMBER;
,F7M+A_$^Z@0 6 V_RESULT.NUMBER_LENGTH := LENGTH(P_BIG_NUMBER);ITPUB个人空间B`:p.m:m y*C
7 RETURN V_RESULT;ITPUB个人空间.z\R/q h|N
8 END; ITPUB个人空间VY&p BOZ
9
+c![qC
ZG{0 10 MEMBER FUNCTION ADDS (P_ADD IN T_BIG_NUMBER) RETURN T_BIG_NUMBER ASITPUB个人空间3jcwVo
11 V_FLAG NUMBER(1) DEFAULT 0;
#m9?Ou@ T|9uEr8J0 12 V_RESULT T_BIG_NUMBER := T_BIG_NUMBER(NULL, 0);ITPUB个人空间(I,n%YP7w!f
13 V_SUM NUMBER;ITPUB个人空间?Z0r:Nn$g/~e8r
14 V_MAX_LENGTH NUMBER DEFAULT GREATEST(NUMBER_LENGTH, P_ADD.NUMBER_LENGTH);
u+F5@T#e&j-f2_/_0 15 BEGINITPUB个人空间;Nc([(v(X;F
16 FOR I IN 1..V_MAX_LENGTH LOOP
3N:B
pvS4i
g(CISi7?0 17 V_SUM := TO_NUMBER(NVL(SUBSTR(BIG_NUMBER, -I, 1), '0'))
;^k.S@OR0 18 + TO_NUMBER(NVL(SUBSTR(P_ADD.BIG_NUMBER, -I, 1), '0'))
9~7f$VTOt#c0 19 + V_FLAG;ITPUB个人空间:p7|X uE f!`
h
20 V_RESULT.BIG_NUMBER := SUBSTR(TO_CHAR(V_SUM), -1, 1) || V_RESULT.BIG_NUMBER;ITPUB个人空间n4T@Dy2`k5]A+Z2]
21 V_FLAG := TRUNC(V_SUM/10);
4E#W:e~zS}0 22 END LOOP;
&UM ] awb!zI't0 23 IF V_FLAG != 0 THENITPUB个人空间S&L3s'xz@
24 V_RESULT.BIG_NUMBER := TO_CHAR(V_FLAG) || V_RESULT.BIG_NUMBER;ITPUB个人空间[3Tz{.n@?
25 END IF;
sx+}dF6{-Q0 26 V_RESULT.NUMBER_LENGTH := LENGTH(V_RESULT.BIG_NUMBER);
-z I3Oe#E,p"j0 27 RETURN V_RESULT;
2M&v+{ c1p"^3kd&|8LP7E0 28 END;
9?h,aY,k;]q0 29 ITPUB个人空间`3Jq'Jp(Z.[V
30 MEMBER FUNCTION MULTIS(P_MULTI IN T_BIG_NUMBER) RETURN T_BIG_NUMBER AS
%NP^ UL0r0 31 V_RESULT T_BIG_NUMBER := T_BIG_NUMBER('0', 1);ITPUB个人空间s9P!IR3_%nOr*e
32 V_TEMP T_BIG_NUMBER := T_BIG_NUMBER(NULL, 0);
$AX:u']u6kno0 33 V_MUL NUMBER;ITPUB个人空间i^DTJ!A"ABAu
34 BEGINITPUB个人空间1Zet2p(v4{1m
35 FOR I IN 1..P_MULTI.NUMBER_LENGTH LOOP
u2A.N*a8]%I}0 36 FOR J IN 1..NUMBER_LENGTH LOOP
X$c&CM*\\0 37 V_MUL := TO_NUMBER(SUBSTR(BIG_NUMBER, -J, 1)) * TO_NUMBER(SUBSTR(P_MULTI.BIG_NUMBER, -I, 1));
(vJEng*?O7M0 38 V_TEMP := T_BIG_NUMBER.F_T_BIG_NUMBER(TO_CHAR(V_MUL) || RPAD('0', I + J - 2, '0'));
H XL| {?.|"s0 39 V_RESULT := V_RESULT.ADDS(V_TEMP);
5w/a!HPCpb%I0 40 END LOOP;
@U5X4a2Ct0 41 END LOOP;ITPUB个人空间5iZ-[*W?Mr9ap
42 RETURN V_RESULT;ITPUB个人空间|[JH{t6W,J
43 END;ITPUB个人空间"~@(Q8ETySM
44
/r2b,i B)JBSks0 45 MEMBER FUNCTION SCIENT_OUTPUT(P_VALUE IN NUMBER) RETURN VARCHAR2 AS
~,M|,P\+HV+Nq D0 46 V_VALUE NUMBER DEFAULT LEAST(P_VALUE, NUMBER_LENGTH, 38);
p(} _XCSaZb.z0 47 V_RETURN VARCHAR2(32767);
K/vq(` t3g `0 48 BEGIN
x2gN?!?7k(CK0 49 IF V_VALUE <= 0 THEN
1S:?3JUv0Lj'L0 50 RAISE_APPLICATION_ERROR(-20000, 'INPUT NUMBER IS NOT CORRECT!');ITPUB个人空间(o,~
M"Kx
51 RETURN '0';
ss-IkOjZ%z0 52 ELSEITPUB个人空间&V'N2avAw,d2Rf
53 V_RETURN := TO_CHAR(TO_NUMBER(SUBSTR(BIG_NUMBER, 1, V_VALUE)) ITPUB个人空间y[eKk%l&h!g
54 + ROUND(TO_NUMBER(NVL(SUBSTR(BIG_NUMBER, V_VALUE + 1, 1), '0')) / 10));ITPUB个人空间l~2R#a*xpjn&B
55 RETURN RTRIM(SUBSTR(V_RETURN, 1, 1) || '.' || SUBSTR(V_RETURN, 2), '.') ITPUB个人空间mX9k"T$Z~N
56 || 'E' || TO_CHAR(NUMBER_LENGTH - 1);
I,T)_$YTU
b0 57 END IF;ITPUB个人空间+{b;Oy+C$^(^/z
58 END;
:s2Vf;}(e+Iy0 59 ITPUB个人空间j4D}MS5t
60 STATIC FUNCTION F_MULTI_SUM_OBJECT(P_IN IN NUMBER, P_SIENCT IN NUMBER DEFAULT NULL) RETURN VARCHAR2 ASITPUB个人空间O%wFpt"J(bT'o
61 V_MULTI T_BIG_NUMBER := T_BIG_NUMBER('1', 1);
y5{S'I6_FW Wr0 62 V_SUM T_BIG_NUMBER := T_BIG_NUMBER('0', 1);ITPUB个人空间~!VmQ,wI%_
63 BEGIN
S0IRx8j`-fRP0 64 FOR I IN 1..P_IN LOOP
`,Nq$OfE.v/U!s0 65 V_MULTI := V_MULTI.MULTIS(T_BIG_NUMBER(TO_CHAR(I), LENGTH(TO_CHAR(I))));
q;MA:X(jw,x0 66 V_SUM := V_SUM.ADDS(V_MULTI);
@gh\)Ad Vuu&M-P.z0 67 END LOOP;
ChN6@pSu9yl0 68 IF P_SIENCT IS NULL THENITPUB个人空间#c']m h(D(u8I
69 RETURN V_SUM.BIG_NUMBER;
p8{)SbP[m:hU-w]0 70 ELSE
2{gBZ(Z9i0 71 RETURN V_SUM.SCIENT_OUTPUT(P_SIENCT);ITPUB个人空间h ?{!IVDe-h
72 END IF;
j7}4SRzZ2T vU0 73 END;
S`[9r2a'{x#L0 74
wlVR.I#S2{"QR0 75 END;ITPUB个人空间 |Q#l*tv c$\ Aj+p r
76 /

类型主体已创建。

下面就可以调用对象的函数进行计算了：

SQL> SELECT T_BIG_NUMBER.F_MULTI_SUM_OBJECT(5) FROM DUAL;

T_BIG_NUMBER.F_MULTI_SUM_OBJECT(5)ITPUB个人空间"X4c D5nltCgX
----------------------------------------------------------------------------ITPUB个人空间l[myS
153

SQL> SELECT T_BIG_NUMBER.F_MULTI_SUM_OBJECT(100) FROM DUAL;

T_BIG_NUMBER.F_MULTI_SUM_OBJECT(100)
8y$Aj c5x-sT0------------------------------------------------------------------------------ITPUB个人空间+H.s)kCa6G+CHG
94269001683709979260859834124473539872070722613982672442938359305624678223479506023400294093599136466986609124347432647622826870038220556442336528920420940313

现在已经得到了正确的结果。这个对象还增加了一个科学计数法表示的功能：

SQL> SELECT T_BIG_NUMBER.F_MULTI_SUM_OBJECT(100, 5) FROM DUAL;

T_BIG_NUMBER.F_MULTI_SUM_OBJECT(100,5)ITPUB个人空间f ~g$Qs!l#\"C6k
------------------------------------------------------------------------ITPUB个人空间 h3b3BV#S)I4P
9.4269E157

SQL> SELECT T_BIG_NUMBER.F_MULTI_SUM_OBJECT(100, 4) FROM DUAL;

T_BIG_NUMBER.F_MULTI_SUM_OBJECT(100,4)
]C3_)L$Zc0------------------------------------------------------------------------ITPUB个人空间#[r#Hc1P
9.427E157

SQL> SELECT T_BIG_NUMBER.F_MULTI_SUM_OBJECT(100, 15) FROM DUAL;

T_BIG_NUMBER.F_MULTI_SUM_OBJECT(100,15)
}/U'c|4\3R*Mw0------------------------------------------------------------------------ITPUB个人空间p,|!sXFj?;{*[
9.42690016837100E157