Oracle 노하우 및 팁

사용자 그룹함수 만들기 3 4 7,670

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
사용자지정 두번째 큰값찾기 그릅함수
처리순서
- 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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
-- 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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
  -- 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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
-- 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 ;
 
<br>-- 테스트
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() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입