Oracle有连加的聚集函数SUM,但是没有一个连乘的函数。
首先要说的是,利用PLSQL代码实现连乘异常简单,这里就不描述了,下面看看能否通过SQL来实现这个功能。
首先想到的是,既然没有连乘的聚集函数,而且Oracle提供自定义聚集函数的功能,那么最直接的思路就是自己构造一个连乘的聚集函数。
SQL> CREATE OR REPLACE TYPE T_MULTI AS OBJECT (
2 RES NUMBER,
3 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_MULTI) RETURN NUMBER,
4 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_MULTI, VALUE IN NUMBER) RETURN NUMBER,
5 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_MULTI, RETURNVALUE OUT NUMBER, FLAGS IN NUMBER
) RETURN NUMBER,
6 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_MULTI, CTX2 IN T_MULTI) RETURN NUMBER
7 )
8 /
类型已创建。
SQL> CREATE OR REPLACE TYPE BODY T_MULTI IS
2 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_MULTI) RETURN NUMBER IS
3 BEGIN
4 SCTX := T_MULTI(1);
5 RETURN ODCICONST.SUCCESS;
6 END;
7
8 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_MULTI, VALUE IN NUMBER) RETURN NUMBER IS
9 BEGIN
10 SELF.RES := SELF.RES * VALUE;
11 RETURN ODCICONST.SUCCESS;
12 END;
13
14 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_MULTI, RETURNVALUE OUT NUMBER, FLAGS IN NUMBER
) RETURN NUMBER IS
15 BEGIN
16 RETURNVALUE := SELF.RES;
17 RETURN ODCICONST.SUCCESS;
18 END;
19
20 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_MULTI, CTX2 IN T_MULTI) RETURN NUMBER IS
21 BEGIN
22 NULL;
23 RETURN ODCICONST.SUCCESS;
24 END;
25 END;
26 /
类型主体已创建。
SQL> CREATE OR REPLACE FUNCTION F_MULTI(P_RES NUMBER) RETURN NUMBER
2 AGGREGATE USING T_MULTI;
3 /
函数已创建。
函数的创建很简单,关于自定义聚集函数的更多描述,可以参考:http://yangtingkun.itpub.net/post/468/3380
下面检查一下这个函数的功能:
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST_LOB TABLE
T TABLE
T_NO_EXISTS_BEFORE TABLE
T_CHAR TABLE
T_LOAD_SPACE TABLE
T_LEVEL TABLE
CHAINED_ROWS TABLE
已选择7行。
SQL> SELECT F_MULTI(ROWNUM) FROM TAB;
F_MULTI(ROWNUM)
---------------
5040
通过聚集函数轻易了实现了这个功能,那么现在有两个问题,一个是这么简单的功能,Oracle为什么不去实现,二是这里仍然借用了PLSQL代码,那么能否仅利用SQL,就解决上面的问题呢。
其实上面两个问题的答案是一样的,因为通过SQL实现是很简单的,只需要一点的数学知识,不用太多,高中水平就足够了:
SQL> SELECT POWER(10, SUM(LOG(10, ROWNUM))) FROM TAB;
POWER(10,SUM(LOG(10,ROWNUM)))
-----------------------------
5040
这种方法唯一的缺点是,在处理大数据量计算的时候,可能由于LOG和POWER运算,而引入一些小的误差。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-399608/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-399608/