Oracle中实现连乘

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/