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運算，而引入一些小的誤差。