참조사이트 : http://www.supinfo-projects.com/en/2006/group_func_2006_en/

그룹함수 만들일이 있어서 찾아보았더니 위의 사이트에러 아래와 같은 샘플을 제공하더군요.
혹시 만들일이 생기시면 아래 샘플을 참조해서.. 복사 & 붙이기
max2 로 max 함수를 구현한듯 합니다.

참조사이트에 가시면 영문이지만..
대략 그림으로 설명되어 있어서 이해가 가능할 듯 합니다.
즐프하세요~

도움되시기를..

CREATE OR REPLACE TYPE max2obj AS OBJECT
(
max NUMBER,
secmax NUMBER,
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT max2obj)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT max2obj,
value IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN max2obj,
returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT max2obj,
ctx2 IN max2obj) RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY max2obj IS
STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT max2obj)
RETURN NUMBER IS
BEGIN
sctx := max2obj(0, 0);
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate (self IN OUT max2obj, value IN NUMBER)
RETURN NUMBER IS
BEGIN
IF value > self.max THEN
self.secmax := self.max;
self.max := value;
ELSIF value > self.secmax THEN
self.secmax := value;
END IF;
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN max2obj, returnValue OUT
NUMBER, flags IN NUMBER) RETURN NUMBER IS
BEGIN
returnValue := self.secmax;
return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT max2obj, ctx2 IN
max2obj) RETURN NUMBER IS
BEGIN
IF ctx2.max > self.max THEN
IF ctx2.secmax > self.max THEN
self.secmax := ctx2.secmax;
ELSE
self.secmax := self.max;
END IF;
self.max := ctx2.max;
ELSIF ctx2.max > self.secmax THEN
self.secmax := ctx2.max;
END IF;
RETURN ODCIConst.Success;
END;
END;
/

CREATE OR REPLACE FUNCTION max2 (input NUMBER)
RETURN NUMBER
PARALLEL_ENABLE
AGGREGATE USING max2obj;
/

select max2( field1 ) from dual1
group by field2;



제가 직접 작성한 함수

/*
CREATE OR REPLACE TYPE OM_DATELIST AS OBJECT
(
DATELIST VARCHAR2(31),
-- DATELIST2 VARCHAR2(31),
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT OM_DATELIST)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT OM_DATELIST,
value IN DATE) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT OM_DATELIST,
ctx2 IN OM_DATELIST) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN OM_DATELIST,
returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER
);
/--*/

CREATE OR REPLACE TYPE BODY OM_DATELIST IS

STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT OM_DATELIST)
RETURN NUMBER IS
BEGIN
sctx := OM_DATELIST("0000000000000000000000000000000"); -- 순서대로 값을 리샛
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate (self IN OUT OM_DATELIST, value IN DATE)
RETURN NUMBER IS
PDAY NUMBER;
BEGIN
-- DATE 에서 날자를 가져온다.
-- SELF 변수에 날자번째의 값을 1로 설정한다.
SELECT TO_NUMBER(TO_CHAR(value, "DD"))
INTO PDAY
FROM DUAL;

DBMS_OUTPUT.PUT_LINE(PDAY);

IF(PDAY < 2)THEN
SELECT "1"||SUBSTR(SELF.DATELIST, 2, 31-1)
INTO SELF.DATELIST
FROM DUAL;
-- SELF.DATELIST := "1"+SUBSTR(SELF.DATELIST, 2, 31-1);
ELSIF(PDAY > 30)THEN
SELECT SUBSTR(SELF.DATELIST, 1, 31-1)||"1"
INTO SELF.DATELIST
FROM DUAL;--/*
ELSE
SELECT SUBSTR(SELF.DATELIST, 1, PDAY-1)||"1"||SUBSTR(SELF.DATELIST, PDAY+1, 31-PDAY)
INTO SELF.DATELIST
FROM DUAL;
SELF.DATELIST := SELF.DATELIST;--*/
END IF;
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT OM_DATELIST, ctx2 IN OM_DATELIST) RETURN
NUMBER IS
PDATE VARCHAR(31);
BEGIN
-- 병렬 처리시에 완료된 값을 가지고 병합 처리를 한다. 인것 같은데..
-- 두개의 값을 병렬 처리하는 것인가?
-- 사실은 호출이 안되는 군요.. 뭘까.. ㅡ.ㅡ;;
PDATE := "";
FOR CNT IN 1..31 LOOP
DBMS_OUTPUT.PUT_LINE("TEST");
END LOOP;
SELF.DATELIST := PDATE;
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN OM_DATELIST, returnValue OUT
VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
BEGIN
returnValue := self.DATELIST; -- 최종적으로 나오는 값.
return ODCIConst.Success;
END;

END;
/

CREATE OR REPLACE FUNCTION GET_SERIAL_ACTDATE2 (INPUT DATE)
RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING OM_DATELIST;
/

SELECT HOSPITAL_ID, MAX(ACTDATE)
, GET_SERIAL_ACTDATE2(ACTDATE)
--, LENGTH(GET_SERIAL_ACTDATE2(ACTDATE))
, COUNT(*)
--SELECT HOSPITAL_ID, ACTDATE
FROM OM_CLINIC
WHERE HOSPITAL_ID = "test9" AND CHART_ID = 3
GROUP BY HOSPITAL_ID
;