CREATE OR REPLACE
TYPE "MUL_TYPE"
AS OBJECT
(
v_result NUMBER,
STATIC FUNCTION
ODCIAggregateInitialize(sctx IN OUT MUL_TYPE )
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateIterate(SELF IN OUT MUL_TYPE ,
VALUE IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateMerge(SELF IN OUT MUL_TYPE,
ctx2 IN MUL_TYPE)
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateTerminate(SELF IN MUL_TYPE,
returnValue OUT NUMBER,
flags IN NUMBER)
RETURN NUMBER
)
/
CREATE OR REPLACE
TYPE BODY MUL_TYPE
IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT MUL_TYPE)
RETURN NUMBER
IS
BEGIN
sctx := MUL_TYPE(1);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT MUL_TYPE,
VALUE IN NUMBER)
RETURN NUMBER
IS
BEGIN
INSERT INTO T1 (SELF.v_result);
COMMIT;
SELF.v_result := SELF.v_result*VALUE;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT MUL_TYPE,
ctx2 IN MUL_TYPE)
RETURN NUMBER
IS
BEGIN
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(SELF IN MUL_TYPE,
returnValue OUT NUMBER,
flags IN NUMBER)
RETURN NUMBER
IS
BEGIN
returnValue := SELF.v_result;
RETURN ODCIConst.Success;
END;
END;
/
自定义了该乘法聚集函，想要得到a/b*c这样公式的计算结果。
select mul(decode(oper,'*',amount,'/',1/amount)) from a group by...
但是遇到一个问题，当b＝0时也就是除数为零，想返回整个公式的结果为1，想了一个办法：
select nvl(mul(decode(oper,'*',amount,'/',decode(amount,0,null,1/amount)) ,1) from a group by...
也就是若遇到除数为0的时候将运算量decode成null，以期在有这种情况下整个mul函数返回null，然后再转换成1。
但发觉自定义聚集函数在迭代的时候会忽略null，所以得不到null值的结果，这个问题大家看看有何办法解决？？？
谢谢！！！ |
