CREATE OR REPLACE TYPE f_compeletev_type AS OBJECT ( compelete_var VARCHAR2(20), yesfinish_count NUMBER, sum_count NUMBER, compeleterate Float(8), STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT f_compeletev_type) RETURN VARCHAR2, MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT f_compeletev_type, VALUE IN VARCHAR2) RETURN VARCHAR2, MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT f_compeletev_type, ctx2 IN f_compeletev_type) RETURN VARCHAR2, MEMBER FUNCTION ODCIAggregateTerminate(SELF IN OUT f_compeletev_type, returnValue OUT NUMBER, flags IN NUMBER) RETURN VARCHAR2 ) /CREATE OR REPLACE TYPE BODY f_compeletev_type ISSTATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT f_compeletev_type) RETURN VARCHAR2 IS BEGIN sctx := f_compeletev_type(0,0,0,0); RETURN ODCICONST.Success; END;
MEMBER FUNCTION ODCIAggregateIterate (SELF IN OUT f_compeletev_type, VALUE IN VARCHAR2) RETURN VARCHAR2 IS BEGIN if VALUE is not null then SELF.sum_count := SELF.sum_count + 1; end if; if 'M_0' = VALUE then SELF.yesfinish_count := SELF.yesfinish_count + 1; end if; RETURN ODCIConst.Success; END;
MEMBER FUNCTION ODCIaggregateMerge(SELF IN OUT f_compeletev_type, ctx2 IN f_compeletev_type) RETURN VARCHAR2 IS BEGIN SELF.sum_count := SELF.sum_count + ctx2.sum_count; SELF.yesfinish_count := SELF.yesfinish_count + ctx2.yesfinish_count; RETURN ODCIConst.Success; END;
MEMBER FUNCTION ODCIAggregateTerminate(SELF IN OUT f_compeletev_type, returnValue OUT NUMBER, flags IN NUMBER) RETURN VARCHAR2 IS BEGIN compeleterate := (SELF.yesfinish_count * 100) / SELF.sum_count; compelete_var := TO_CHAR(compeleterate,'999.9%'); returnValue := compelete_var; RETURN ODCIConst.Success; END;
END; /CREATE OR REPLACE FUNCTION f_compeletev(input VARCHAR2 ) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING f_compeletev_type; /大家看看这段代码,怎么才能返回% 编译通过,可是一Select就出错,不出结果!
(
compelete_var VARCHAR2(20), yesfinish_count NUMBER, sum_count NUMBER, compeleterate Float(8),
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT f_compeletev_type) RETURN VARCHAR2,
MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT f_compeletev_type, VALUE IN VARCHAR2) RETURN VARCHAR2,
MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT f_compeletev_type, ctx2 IN f_compeletev_type) RETURN VARCHAR2,
MEMBER FUNCTION ODCIAggregateTerminate(SELF IN OUT f_compeletev_type, returnValue OUT NUMBER, flags IN NUMBER) RETURN VARCHAR2
)
/CREATE OR REPLACE TYPE BODY f_compeletev_type ISSTATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT f_compeletev_type)
RETURN VARCHAR2 IS
BEGIN
sctx := f_compeletev_type(0,0,0,0);
RETURN ODCICONST.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate (SELF IN OUT f_compeletev_type, VALUE IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
if VALUE is not null then
SELF.sum_count := SELF.sum_count + 1;
end if;
if 'M_0' = VALUE then
SELF.yesfinish_count := SELF.yesfinish_count + 1;
end if;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIaggregateMerge(SELF IN OUT f_compeletev_type, ctx2 IN f_compeletev_type)
RETURN VARCHAR2 IS
BEGIN
SELF.sum_count := SELF.sum_count + ctx2.sum_count;
SELF.yesfinish_count := SELF.yesfinish_count + ctx2.yesfinish_count;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(SELF IN OUT f_compeletev_type, returnValue OUT NUMBER, flags IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
compeleterate := (SELF.yesfinish_count * 100) / SELF.sum_count;
compelete_var := TO_CHAR(compeleterate,'999.9%');
returnValue := compelete_var;
RETURN ODCIConst.Success;
END;
END;
/CREATE OR REPLACE FUNCTION f_compeletev(input VARCHAR2 )
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING f_compeletev_type;
/大家看看这段代码,怎么才能返回%
编译通过,可是一Select就出错,不出结果!