꿈꾸는 개발자, DBA 커뮤니티 구루비

사용자 그룹함수 만들기

by 손님 [기타] 함수 사용자지정 [2012.10.26 18:01:02]

사용자지정 두번째 큰값찾기 그릅함수
처리순서
- ODCIAggregateInitialize (초기화)
- ODCIAggregateIterate (ROW수만큼 반복)
- ODCIAggregateMerge (병합)
- ODCIAggregateTerminate (종료처리, 결과 리턴)


-- OBJECT SPEC 생성
CREATE OR REPLACE TYPE XX_SecMax
IS
OBJECT (
  MAX     NUMBER -- 함수전역변수
  ,SECMAX   NUMBER -- 함수전역변수

  ,STATIC FUNCTION ODCIAggregateInitialize(
        SCTX  IN OUT XX_SecMax
        ) RETURN NUMBER

  ,MEMBER FUNCTION ODCIAggregateIterate(
        SELF  IN OUT XX_SecMax
        ,VALUE  IN   NUMBER
        ) RETURN NUMBER

  ,MEMBER FUNCTION ODCIAggregateMerge(
        SELF  IN OUT XX_SecMax
        ,CTX2  IN   XX_SecMax
        ) RETURN NUMBER

  ,MEMBER FUNCTION ODCIAggregateTerminate(
        SELF  IN OUT XX_SecMax
        ,RTN_VAL OUT   NUMBER
        ,FLAGS  IN   NUMBER
        ) RETURN NUMBER
) ;

-- OBJECT BODY 생성
CREATE OR REPLACE TYPE BODY XX_SecMax
IS
  STATIC FUNCTION ODCIAggregateInitialize(
        SCTX  IN OUT XX_SecMax
        ) RETURN NUMBER 
  IS
  BEGIN    -- 초기화
    SCTX  := XX_SecMax(NULL,NULL) ;  -- 초기변수값(MAX,SECMAX)
    RETURN ODCIConst.Success ;
  END ;

  MEMBER FUNCTION ODCIAggregateIterate(
        SELF  IN OUT XX_SecMax
        ,VALUE  IN   NUMBER
        ) RETURN NUMBER
  IS
  BEGIN    -- 반복처리
    IF SECMAX IS NULL
     THEN
      SELF.SECMAX := VALUE ;
      SELF.MAX  := VALUE ;
      RETURN ODCIConst.Success ;
    END IF ;
    IF VALUE > SELF.MAX
     THEN
      SELF.SECMAX := SELF.MAX ;
      SELF.MAX  := VALUE ;
     ELSE
      IF VALUE <> SELF.MAX AND
        VALUE > SELF.SECMAX
       THEN
        SELF.SECMAX := VALUE ;
      END IF ;
    END IF ;
    RETURN ODCIConst.Success ;
  END ;

  MEMBER FUNCTION ODCIAggregateMerge(
        SELF  IN OUT XX_SecMax
        ,CTX2  IN   XX_SecMax
        ) RETURN NUMBER
  IS
  BEGIN    -- 병렬처리시 병합
    IF CTX2.MAX > SELF.MAX
     THEN
      IF CTX2.SECMAX > SELF.SECMAX
       THEN 
        SELF.SECMAX := CTX2.SECMAX ;
       ELSE
        SELF.SECMAX := SELF.MAX ;
      END IF;
      SELF.MAX  := CTX2.MAX ;
     ELSE
      IF CTX2.MAX <> SELF.MAX AND
        CTX2.MAX > SELF.SECMAX
       THEN
        SELF.SECMAX := CTX2.MAX ;
      END IF ;
    END IF ;
    RETURN ODCIConst.Success ;
  END ;

  MEMBER FUNCTION ODCIAggregateTerminate(
        SELF  IN OUT XX_SecMax
        ,RTN_VAL OUT   NUMBER
        ,FLAGS  IN   NUMBER
        ) RETURN NUMBER
  IS
  BEGIN    -- 결과 리턴
    RTN_VAL := SELF.SECMAX ;
    RETURN ODCIConst.Success ;
  END ;
END ;

-- 함수 생성
CREATE OR REPLACE FUNCTION XX_FnSecMax (
            IN_DATA IN NUMBER
            ) RETURN NUMBER
            AGGREGATE USING XX_SecMax
;

-- 테스트
WITH TBL AS (
SELECT 1 CD, 3 DT FROM DUAL UNION ALL
SELECT 1 CD, 5 DT FROM DUAL UNION ALL
SELECT 1 CD, 4 DT FROM DUAL UNION ALL
SELECT 2 CD, 5 DT FROM DUAL UNION ALL
SELECT 3 CD, 4 DT FROM DUAL UNION ALL
SELECT 3 CD, 7 DT FROM DUAL UNION ALL
SELECT 3 CD, 9 DT FROM DUAL UNION ALL
SELECT 3 CD, 9 DT FROM DUAL UNION ALL
SELECT 3 CD, 2 DT FROM DUAL
)
SELECT CD
   ,XX_FnSecMax(DT) SECMAX
 FROM TBL
GROUP BY CD ;

결과
1	4
2	5
3	7

-- PARTITION BY
SELECT CD
   ,XX_FnSecMax(DT) OVER (PARTITION BY CD) SECMAX
 FROM TBL ;

결과
1	4
1	4
1	4
2	5
3	7
3	7
3	7
3	7
3	7

by 손님 [2012.10.29 11:19:21]

-- WM_CONCAT기능 함수(오름차순)

-- TABLE ARRAY 생성
CREATE OR REPLACE TYPE XX_VC2000TB
IS TABLE OF VARCHAR2(2000) ;

-- OBJECT SPEC 생성
CREATE OR REPLACE TYPE XX_WmConcat
IS
OBJECT (
  RTN_STR   VARCHAR(30000)
  ,VAL_TAB   XX_VC2000TB

  ,STATIC FUNCTION ODCIAggregateInitialize(
        SCTX  IN OUT XX_WmConcat
        ) RETURN NUMBER

  ,MEMBER FUNCTION ODCIAggregateIterate(
        SELF  IN OUT XX_WmConcat
        ,VALUE  IN   VARCHAR2
        ) RETURN NUMBER

  ,MEMBER FUNCTION ODCIAggregateMerge(
        SELF  IN OUT XX_WmConcat
        ,CTX2  IN   XX_WmConcat
        ) RETURN NUMBER

  ,MEMBER FUNCTION ODCIAggregateTerminate(
        SELF  IN OUT XX_WmConcat
        ,RTN_VAL OUT   VARCHAR2
        ,FLAGS  IN   NUMBER
        ) RETURN NUMBER
) ;

-- OBJECT BODY 생성
CREATE OR REPLACE TYPE BODY XX_WmConcat
IS
  STATIC FUNCTION ODCIAggregateInitialize(
        SCTX  IN OUT XX_WmConcat
        ) RETURN NUMBER
  IS
  BEGIN
    SCTX  := XX_WmConcat('',XX_VC2000TB()) ;
    RETURN ODCIConst.Success ;
  END ;

  MEMBER FUNCTION ODCIAggregateIterate(
        SELF  IN OUT XX_WmConcat
        ,VALUE  IN   VARCHAR2
        ) RETURN NUMBER
  IS
  BEGIN
    SELF.VAL_TAB.EXTEND ;
    SELF.VAL_TAB(SELF.VAL_TAB.COUNT) := VALUE ;
    RETURN ODCIConst.Success ;
  END ;

  MEMBER FUNCTION ODCIAggregateMerge(
        SELF  IN OUT XX_WmConcat
        ,CTX2  IN   XX_WmConcat
        ) RETURN NUMBER
  IS
  BEGIN
    IF CTX2.VAL_TAB IS NOT NULL AND
      CTX2.VAL_TAB.COUNT > 0
     THEN
      FOR IDX IN CTX2.VAL_TAB.FIRST .. CTX2.VAL_TAB.LAST
      LOOP
        SELF.VAL_TAB.EXTEND ;
        SELF.VAL_TAB(SELF.VAL_TAB.COUNT) := CTX2.VAL_TAB(IDX) ;
      END LOOP ;
    END IF ;
    RETURN ODCIConst.Success ;
  END ;

  MEMBER FUNCTION ODCIAggregateTerminate(
        SELF  IN OUT XX_WmConcat
        ,RTN_VAL OUT   VARCHAR2
        ,FLAGS  IN   NUMBER
        ) RETURN NUMBER
  IS
    LV_TEMP   VARCHAR2(2000) ;

    -- ASCENDING SORT
    PROCEDURE SP_SORT_QUICK(
            P_FIRST   NUMBER
            ,P_LAST   NUMBER
            )
    IS
      LV_PIVOT  VARCHAR2(2000) ;
      LV_RIGHT  NUMBER ;
      LV_LEFT   NUMBER ;
    BEGIN
      LV_PIVOT  := SELF.VAL_TAB(FLOOR((P_FIRST + P_LAST) / 2)) ;
      LV_RIGHT  := P_FIRST ;
      LV_LEFT   := P_LAST ;
      LOOP
        WHILE SELF.VAL_TAB(LV_RIGHT) < LV_PIVOT    -- ASC:< , DESC:>
        LOOP LV_RIGHT := LV_RIGHT + 1 ;  END LOOP ;
        WHILE SELF.VAL_TAB(LV_LEFT) > LV_PIVOT    -- ASC:> , DESC:<
        LOOP LV_LEFT  := LV_LEFT - 1 ;  END LOOP ;
        IF LV_RIGHT <= LV_LEFT
         THEN
          LV_TEMP         := SELF.VAL_TAB(LV_RIGHT) ;
          SELF.VAL_TAB(LV_RIGHT) := SELF.VAL_TAB(LV_LEFT) ;
          SELF.VAL_TAB(LV_LEFT)  := LV_TEMP ;
          LV_RIGHT  := LV_RIGHT + 1 ;
          LV_LEFT   := LV_LEFT - 1 ;
        END IF ;
        EXIT WHEN LV_RIGHT > LV_LEFT ;
      END LOOP ;
      IF P_FIRST < LV_LEFT
       THEN SP_SORT_QUICK(P_FIRST ,LV_LEFT) ;
      END IF ;
      IF P_LAST > LV_RIGHT
       THEN SP_SORT_QUICK(LV_RIGHT ,P_LAST ) ;
      END IF ;
    END ;
  BEGIN
    IF SELF.VAL_TAB IS NOT NULL AND
      SELF.VAL_TAB.COUNT > 1
     THEN SP_SORT_QUICK(1 ,SELF.VAL_TAB.COUNT) ;
    END IF ;
    RTN_VAL := '';
    IF SELF.VAL_TAB IS NOT NULL AND
      SELF.VAL_TAB.COUNT > 0
     THEN
      FOR IDX IN SELF.VAL_TAB.FIRST .. SELF.VAL_TAB.LAST
      LOOP
        RTN_VAL := RTN_VAL || SELF.VAL_TAB(IDX) ;
      END LOOP ;
    END IF ;
    RETURN ODCIConst.Success ;
  END ;
END ;

-- 함수 생성
CREATE OR REPLACE FUNCTION XX_FnWmConcat (
            IN_DATA IN VARCHAR2
            ) RETURN VARCHAR2
            AGGREGATE USING XX_WmConcat ;

-- 테스트
WITH TBL AS (
SELECT 1 CD, 'B' DT FROM DUAL UNION ALL
SELECT 1 CD, 'X' DT FROM DUAL UNION ALL
SELECT 1 CD, 'C' DT FROM DUAL UNION ALL
SELECT 2 CD, 'D' DT FROM DUAL UNION ALL
SELECT 3 CD, 'D' DT FROM DUAL UNION ALL
SELECT 3 CD, 'R' DT FROM DUAL UNION ALL
SELECT 3 CD, 'A' DT FROM DUAL UNION ALL
SELECT 3 CD, 'Z' DT FROM DUAL UNION ALL
SELECT 3 CD, 'X' DT FROM DUAL
)
SELECT CD
   ,SUBSTR(XX_FnWmConcat('/' || DT),2) CONC
 FROM TBL
GROUP BY CD ;

결과
1	B/C/X
2	D
3	A/D/R/X/Z

by 손님 [2012.10.29 16:00:27]

  -- SORT 다른방법
  MEMBER FUNCTION ODCIAggregateTerminate(
        SELF  IN OUT XX_WmConcat
        ,RTN_VAL OUT   VARCHAR2
        ,FLAGS  IN   NUMBER
        ) RETURN NUMBER
  IS
    TYPE TAB_TYPE IS TABLE OF VARCHAR2(2000)
              INDEX BY VARCHAR2(2100) ;
    SORT_TAB    TAB_TYPE ;
    SPACE_CHR    VARCHAR2(10) := '     ' ;
    SAVE_INDEX   VARCHAR2(2100) ;
  BEGIN
    -- INDEX TABLE ARRAY 이용
    RTN_VAL := '';
    IF SELF.VAL_TAB IS NOT NULL AND
      SELF.VAL_TAB.COUNT > 0
     THEN
      FOR IDX IN SELF.VAL_TAB.FIRST .. SELF.VAL_TAB.LAST
      LOOP
        SORT_TAB(SELF.VAL_TAB(IDX) || SPACE_CHR || IDX) := SELF.VAL_TAB(IDX) ;
        -- 중복제거
--        SORT_TAB(SELF.VAL_TAB(IDX)) := SELF.VAL_TAB(IDX) ;
      END LOOP ;
      -- 오름차순
      SAVE_INDEX := SORT_TAB.FIRST ;
      WHILE SAVE_INDEX IS NOT NULL
      LOOP
        RTN_VAL   := RTN_VAL || SORT_TAB(SAVE_INDEX) ;
        SAVE_INDEX := SORT_TAB.NEXT(SAVE_INDEX) ;
      END LOOP ;
/*
      -- 내림차순
      SAVE_INDEX := SORT_TAB.LAST ;
      WHILE SAVE_INDEX IS NOT NULL
      LOOP
        RTN_VAL   := RTN_VAL || SORT_TAB(SAVE_INDEX) ;
        SAVE_INDEX := SORT_TAB.PRIOR(SAVE_INDEX) ;
      END LOOP ;
*/
    END IF ;
    RETURN ODCIConst.Success ;
  END ;

by 손님 [2012.10.30 09:59:39]

 -- XX_WmConcat 의 다른 방법
 -- 2차원 INDEX TABLE ARRAY 이용
 -- 중복제거,NULL제거,정렬 기능포함
 MEMBER FUNCTION ODCIAggregateTerminate(
    SELF IN OUT XX_WmConcat
    ,RTN_VAL OUT  VARCHAR2
    ,FLAGS IN  NUMBER
    ) RETURN NUMBER
 IS
  TYPE TAB_1_TYPE IS TABLE OF VARCHAR2(1)
        INDEX BY PLS_INTEGER ;
  TYPE TAB_2_TYPE IS TABLE OF TAB_1_TYPE
        INDEX BY VARCHAR2(2000) ;
  WORK_TAB  TAB_2_TYPE ;
  V_KEY_1   NUMBER ;
  V_KEY_2   VARCHAR2(2000) ;
  V_UNIQ   VARCHAR2(1);
  V_NULL   VARCHAR2(1);
  V_ORDR   VARCHAR2(1);
 BEGIN
  RTN_STR := '';
  IF SELF.VAL_TAB IS NOT NULL AND
   SELF.VAL_TAB.COUNT > 0
   THEN
   -- VALUE의 1번째 : A=중복포함 ,U=중복제거
   --   2번째 : A=NULL포함 ,N=NULL제거
   --   3번째 : A=오름차순 ,D=내림차순
   --   4번째 : 구분자
   --   5번째~: DATA
   V_UNIQ := UPPER(SUBSTR(SELF.VAL_TAB(1),1,1)) ;
   V_NULL := UPPER(SUBSTR(SELF.VAL_TAB(1),2,1)) ;
   V_ORDR := UPPER(SUBSTR(SELF.VAL_TAB(1),3,1)) ;
   FOR IDX IN SELF.VAL_TAB.FIRST .. SELF.VAL_TAB.LAST
   LOOP
    IF V_NULL  'N' OR
     SUBSTR(SELF.VAL_TAB(IDX),5) IS NOT NULL
     THEN
     IF V_UNIQ  'U'
      THEN WORK_TAB(SUBSTR(SELF.VAL_TAB(IDX),4))(IDX) := '' ; -- 전체
      ELSE WORK_TAB(SUBSTR(SELF.VAL_TAB(IDX),4))(1) := '' ; -- 중복제거
     END IF ;
    END IF ;
   END LOOP ;
   IF V_ORDR  'D'
    THEN
    -- 오름차순
    V_KEY_2 := WORK_TAB.FIRST ;
    WHILE V_KEY_2 IS NOT NULL
    LOOP
     V_KEY_1 := WORK_TAB(V_KEY_2).FIRST ;
     WHILE V_KEY_1 IS NOT NULL
     LOOP
      RTN_STR := RTN_STR || V_KEY_2 ;
      V_KEY_1 := WORK_TAB(V_KEY_2).NEXT(V_KEY_1) ;
     END LOOP ;
     V_KEY_2 := WORK_TAB.NEXT(V_KEY_2) ;
    END LOOP ;
    ELSE
    -- 내림차순
    V_KEY_2 := WORK_TAB.LAST ;
    WHILE V_KEY_2 IS NOT NULL
    LOOP
     V_KEY_1 := WORK_TAB(V_KEY_2).FIRST ;
     WHILE V_KEY_1 IS NOT NULL
     LOOP
      RTN_STR := RTN_STR || V_KEY_2 ;
      V_KEY_1 := WORK_TAB(V_KEY_2).NEXT(V_KEY_1) ;
     END LOOP ;
     V_KEY_2 := WORK_TAB.PRIOR(V_KEY_2) ;
    END LOOP ;
   END IF ;
  END IF ;
  RTN_VAL := RTN_STR ;
  RETURN ODCIConst.Success ;
 END ;


-- 테스트
WITH TBL AS ( 
SELECT 1 CD, 'B' DT FROM DUAL UNION ALL
SELECT 1 CD, '' DT FROM DUAL UNION ALL
SELECT 1 CD, 'B' DT FROM DUAL UNION ALL
SELECT 1 CD, 'C' DT FROM DUAL UNION ALL
SELECT 2 CD, 'D' DT FROM DUAL UNION ALL
SELECT 2 CD, 'S' DT FROM DUAL UNION ALL
SELECT 2 CD, 'D' DT FROM DUAL UNION ALL
SELECT 3 CD, 'D' DT FROM DUAL UNION ALL
SELECT 3 CD, '' DT FROM DUAL UNION ALL
SELECT 3 CD, 'X' DT FROM DUAL UNION ALL
SELECT 3 CD, 'A' DT FROM DUAL UNION ALL
SELECT 3 CD, '' DT FROM DUAL UNION ALL
SELECT 3 CD, 'Z' DT FROM DUAL UNION ALL
SELECT 3 CD, 'X' DT FROM DUAL 
) 
SELECT CD 
  ,SUBSTR(XX_FnWmConcat('AAA,' || DT),2) DT_AAA -- 중복포함 NULL포함 오름차순
  ,SUBSTR(XX_FnWmConcat('ANA,' || DT),2) DT_ANA -- 중복포함 NULL제거 오름차순
  ,SUBSTR(XX_FnWmConcat('AAD,' || DT),2) DT_AAD -- 중복포함 NULL포함 내림차순
  ,SUBSTR(XX_FnWmConcat('AND,' || DT),2) DT_AND -- 중복포함 NULL제거 내림차순
  ,SUBSTR(XX_FnWmConcat('UAA/' || DT),2) DT_UAA -- 중복제거 NULL포함 오름차순
  ,SUBSTR(XX_FnWmConcat('UNA/' || DT),2) DT_UNA -- 중복제거 NULL제거 오름차순
  ,SUBSTR(XX_FnWmConcat('UAD/' || DT),2) DT_UAD -- 중복제거 NULL포함 내림차순
  ,SUBSTR(XX_FnWmConcat('UND/' || DT),2) DT_UND -- 중복제거 NULL제거 내림차순
 FROM TBL 
GROUP BY CD 

결과
1	,B,B,C	B,B,C	C,B,B,	C,B,B	/B/C	B/C	C/B/	C/B
2	D,D,S	D,D,S	S,D,D	S,D,D	D/S	D/S	S/D	S/D
3	,,A,D,X,X,Z	A,D,X,X,Z	Z,X,X,D,A,,	Z,X,X,D,A	/A/D/X/Z	A/D/X/Z	Z/X/D/A/	Z/X/D/A

by 손님 [2012.11.05 19:03:35]

위 오류 수정 ( '' 가 지워졌습니다.)
IF V_NULL != 'N' OR SUBSTR(SELF.VAL_TAB(IDX),5) IS NOT NULL THEN IF V_UNIQ != 'U' THEN WORK_TAB(SUBSTR(SELF.VAL_TAB(IDX),4))(IDX) := '' ; -- 전체 ELSE WORK_TAB(SUBSTR(SELF.VAL_TAB(IDX),4))(1) := '' ; -- 중복제거 END IF ; END IF ; END LOOP ; IF V_ORDR != 'D' THEN -- 오름차순

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

Oracle 노하우 및 팁