메뉴 건너뛰기

tnt_db

Oracle PARAMETER값에 따라 변경이 일어나는 문장

운영자 2002.09.17 20:38 조회 수 : 15808 추천:16

조건
    1.Parameter P_var1 은 한자리 값을 가지는 Number 또는 Char 이다.
    2.Sample Table 인 SAM_TB1은
        사번  직급   급여     호봉
      ------  ----  -----   -----
      980101    01   1000       1
      970102    02   1500       4
      980103    01   1100       2
      960104    03   1800       8
      980105    01   1300       3
      970106    02   1500       5
      980107    01   1200       2
      950108    04   2200      12
      960109    03   1700       7
      950110    04   2100      11
      980111    01   1000       1
      930112    05   2500      15
      940113    02   1700       5
      940114    01   1700       4
      940115    02   1700       7
      와 같은 Data를 가진다.
    3.P_var1의 값에 따라
      '1' : 직급에 상관없는    사원의 직급별 급여평균
      '2' : 직급이 '01'인        사원의 급여평균
      '3' : 직급이 '02'인        사원의 급여평균
      '4' : 직급이 '03'인        사원의 급여평균
      '5' : 직급이 '04','05'인  사원의 직급별 급여평균
      위와 같은 경우에 따라 각 값을 가질 수 있다.
      가상으로 만들어낸 경우지만 충분히 있을 수 있는 경우다.

문제 .
      각 경우에 따라 SQL문을 하나씩 작성하는 번거로움을 덜고
      위의 경우를 모두 만족 시킬 수 있는 SQL을 만들고 싶다.

생각 .
      가장쉽게 생각할 수 있는 해결방안은 ?
      해법을 보기전에 잠간 생각을 해보자.
      엿보기1. 각 경우에 따라 조건절이 DYNAMIC하게 작용하면된다.
            2. 각 경우를 IF문으로 분리하면된다.
            3. ORACLE에는 IF문을 대신할 수 있는 DECODE 함수가 있다.
               물론 조건절에도 사용 가능하다.
해법 .
      단계1. 먼저 IF를 이용해 보면 문장이 다음과 같이 분리된다.
          '1'인경우 :
             SELECT 직급,AVG(급여)
             FROM   SAM_TB1
             GROUP BY 직급;
        
      
      
          '2'인경우 :
             SELECT 직급,AVG(급여)
             FROM   SAM_TB1
             WHERE  직급 = '01'
             GROUP BY 직급;

            

          '3'인경우
             SELECT 직급,AVG(급여)
             FROM   SAM_TB1
             WHERE  직급 = '02'
             GROUP BY 직급;

              

          '4'인경우
             SELECT 직급,AVG(급여)
             FROM   SAM_TB1
             WHERE  직급 = '03'
             GROUP BY 직급;

              

          '5'인경우
             SELECT 직급,AVG(급여)
             FROM   SAM_TB1
             WHERE  직급 = '04'
             OR     직급 = '05'
             GROUP BY 직급;

              


      단계2. 첫번째 문제는 WHERE 여부의 차이를 어떻게 해결하는가
             하는것인데, WHERE절만 차이가 있으니 문제를 단순화 시켜보자..
          그렇다면
             SELECT 직급,AVG(급여)
             FROM   SAM_TB1
             WHERE  어쩌구저쩌구
             GROUP BY 직급;
          이렇게 될것이다.
      단계3. 이제 [어쩌구저쩌구] 만 해결하면된다.
             그런데 '1'인 경우는 조건이 필요 없고, '5'인 경우는 2개, 나머지는
             하나의 조건이 필요하다.
             SELECT 직급,AVG(급여)
             FROM   SAM_TB1
             WHERE  직급 = '1'인 경우는 모두다, 나머지는 해당조건만 필요 하다.
             '5'인 경우는 직급='4' OR  직급 = '5' 가 필요하다 나머지는 필요 없다.
      단계4. 첫번째 조건의 '모두다'는 그 조건이 전체 RECORD에 대해서 항상
             '참'이면 되고 ('1'인 경우) 나머지는 해당조건만을 만족하면 된다.
             SELECT 직급,AVG(급여)
             FROM   SAM_TB1
             WHERE  직급 = DECODE(P_var1,'1',직급,
                                         '2','01',
                                         '3','02',
                                         '4','03',
                                         '5','04')
             GROUP BY 직급;
             이렇게 되면 '5'인 경우를 제외한 나머지 경우를 모두 만족시킨다.
      단계5  이제는 '5'인 경우만 남았다.
             두번째 조건을 달면 된다.  
          
             SELECT 직급,AVG(급여)
             FROM   SAM_TB1
             WHERE  직급 = DECODE(P_var1,'1',직급,
                                         '2','01',
                                         '3','02',
                                         '4','03',
                                         '5','04')
             OR     직급 = DECODE(P_var1,'5','05',' ')
             GROUP BY 직급;
      이게 답이다.

      자이제 실제로 PRAMETER(P_var1)를 변화시키면서 결과를 알아 보기로 하자.
      다음의 세가지 유형에 대해 테스트 해보기로 한다.
P_var1='1'인경우
SQL> SELECT 직급,AVG(급여)
             FROM   SAM_TB1
             WHERE  직급 = DECODE(1,'1',직급,
                                         '2','01',
                                         '3','02',
                                         '4','03',
                                         '5','04')
             OR     직급 = DECODE(1,'5','05',' ')
             GROUP BY 직급

직 AVG(급여)
-- ---------
01 1216.6667
02      1600
03      1750
04      2150
05      2500
        
P_var1='2'인경우
SQL> SELECT 직급,AVG(급여)
             FROM   SAM_TB1
             WHERE  직급 = DECODE(2,'1',직급,
                                         '2','01',
                                         '3','02',
                                         '4','03',
                                         '5','04')
             OR     직급 = DECODE(2,'5','05',' ')
             GROUP BY 직급
직 AVG(급여)
-- ---------
01 1216.6667

P_var1='5'인경우
SQL> SELECT 직급,AVG(급여)
             FROM   SAM_TB1
             WHERE  직급 = DECODE(5,'1',직급,
                                         '2','01',
                                         '3','02',
                                         '4','03',
                                         '5','04')
             OR     직급 = DECODE(5,'5','05',' ')
             GROUP BY 직급
직 AVG(급여)
-- ---------
04      2150
05      2500
      



PLAN.
      Execution Plan
      ----------------------------------------------------------
         0      SELECT STATEMENT Optimizer=CHOOSE
         1    0   SORT (GROUP BY)
         2    1     TABLE ACCESS (FULL) OF 'SAM_TB1'
      PLAN 상으로 FULL SCAN을 하는것을
      알 수있다.
      즉, 반드시 INDEX SCAN 을 해야하는 경우에는
      사용을 제한해야 한다는
      것을 알 수있다.

뒷풀이 .
      각 경우를 살펴보자
      '1' : WHERE  직급 = 직급
            OR     직급 = ' ';
      '2' : WHERE  직급 = '01'
            OR     직급 = ' ';
      '3' : WHERE  직급 = '02'
            OR     직급 = ' ';
      '4' : WHERE  직급 = '03'
            OR     직급 = ' ';
      '4' : WHERE  직급 = '04'
            OR     직급 = '05';
     위의 경우를 모두 만족하는 데이타를 찾을 수 있다.
사족 .
     물론 COLUMN에 가공이 들어가 찜찜하기는 하다
     하지만 이렇게 사용하므로 인해서 얻어지는 효과가 그 역효과를
     누르는 경우는 많을것이다.
     그렇다면 알고 있어서 나쁠건 없다고 보는데....
     대용량 데이타베이스를 고려하기전에 우리가
     흔하게 부딪히는 문제중의 하나다.
     아는게 힘이다. 알아두자.
번호 제목 글쓴이 날짜 조회 수
46 빠진이빨찾기 II 운영자 2002.09.18 3064
45 빠진이빨찾기 운영자 2002.09.18 3122
44 순환참조에서의 상위코드로집계 운영자 2002.09.18 3223
43 CONNECT BY의 SORT와 JOIN 운영자 2002.09.18 2810
42 REPORT 양식맞추기4 운영자 2002.09.18 2597
41 REPORT 양식 맞추기 III 운영자 2002.09.18 3015
40 REPORT 양식 맞추기 II 운영자 2002.09.18 2644
39 REPORT 양식 맞추기 운영자 2002.09.18 31359
38 1:1 JOIN / 1:M JOIN 운영자 2002.09.18 3000
37 TUNING-01 운영자 2002.09.18 2737
36 소계와 합계 운영자 2002.09.18 3487
35 중복DATA 찾아내기 운영자 2002.09.17 2814
34 GROUP별 DATA 분류 운영자 2002.09.17 2978
» PARAMETER값에 따라 변경이 일어나는 문장 운영자 2002.09.17 15808
32 SELF JOIN 운영자 2002.09.17 3408
31 Outer Join 운영자 2002.09.17 2918
30 HINTS (출처-Oracle8.0 Tuning Guide) 운영자 2002.09.17 2734
29 단일행 문자 함수 운영자 2002.09.17 2975
28 단일행 수치 함수 운영자 2002.09.17 2868
27 NOT IN 의 함정 운영자 2002.09.17 2712
위로