by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

7. 사용자지정 저장형 함수 사용시의 활용




7. 사용자지정 저장형 함수 사용시의 활용

문제제시

  • 'AVG_AMT_FUNC'함수를 이용하여 다음과 같은SQL을 수행함
  • 저장형 함수에서 처리한 결과를 SELECT-LIST의 여러컬럼에서 사용하고 있다는 것임
  • 중복사용은 동일한 작업이 반복해서 수행하는 문제가 생김
    SELECT 사원번호, 
                AVG_AMT_FUNC(사원번호), 
                AVG_AMT_FUNC(사원번호) * DECODE (직무,'A1',0.12,0.11),
                기본급 / AVG_AMT_FUNC(사원번호)
    FROM    사원 
    WHERE 부서코드 = '1110' ;
    

쿼리문

1) 인라인뷰 내에서 한번만 저장형 함수를 기술하고 메인 SELECT-LIST에서 그 결과를 반복수행 SQL구문

  • SELECT-LIST에 저장형 함수를 나열한 이전의 방법과 동일한 횟수만큼 수행됨
  • 인라인뷰의 내의 집합의 최종 내용이 원시집합과 동일한 레벨이라면, 즉 GROUP BY등에 의해 집합의 레벨이 변경되지 않으면, 그건 단지 표현 형태를 그렇게 한 것에 불과한것이며 실제로의 풀어서 작성된 것과 동일하다는 것을 알수있음
    SELECT 사원번호, 
                AVG_AMT, 
                AVG_AMT * DECODE (직무,'A1',0.12,0.11),
                기본급 / AVG_AMT
    FROM ( SELECT 사번, AVG_AMT_FUNC(사번) AVG_AMT
               FROM 사원 
               WHERE 부서코드 = '1110' ) ;
    

2) 별도의 집합을 만드는 형태의 SQL구문

  • 사원별로 단 한번씩만 저장형 함수가 수행
  • GROUP BY를 하면 내부적으로 처리결과가 저장되고 이것을 여러 번 사용하는 것은 관계없음
    SELECT 사원번호, 
                     AVG_AMT, AVG_AMT * DECODE (직무,'A1',0.12,0.11),
                    기본급 / AVG_AMT
    FROM ( SELECT 사번, AVG_AMT_FUNC(사번) AVG_AMT
                    FROM 사원 
                   WHERE 부서코드 = '1110' 
                   GROUP BY 사원번호 ) ;
    

3) 별도의 집합을 만드는 형태의 SQL구문중 (GROUP BY가 부담이 되는 경우)

  • SQL에서 함수의 수행 결과만 추출하고 나머지 가공은 SQL의 수행 결과를 패치(Fetch)한 후에 처리함

저장형 함수의 활용시 주의사항

문제제시

  • 함수 내에서 '평균급여' 와 기간 내의 '최대급여'를 구하고자 함
  • 하나의 함수에서 한번의 수행으로 하나이상의 상수값을 얻도록 해야함

쿼리문

1) 컬럼 값들을 결합(Concatenation)하여 하나의 상수값으로 만들어 리턴한 후 다시 분할하여 최종출력시키는 방법의 SQL 구문

  • 하나 이상의 컬럼 추출시 고정길이로 만들어 결합하여 리턴하고 결과를 SQL에서 분할
  • 컬럼을 분할시키면서 실제로는 로우마다 두번씩 함수가 실행되었음
  • 해결방법은 앞서제시했던 인라인뷰 내에 GROUP BY를 추가시키거나 패치후 분할시키는 방법을 사용
    CREATE or  REPLACE  FUNCTION  AVG_MAX_AMT_FUNC 
          (v_empno    varchar2)
           RETURN   varchar2 IS
           V_avg_amt varchar2(30);
    BEGIN
           SELECT RPAD(avg(급여총액),15) ||RPAD(max(급여총액),15) into v_avg_amt 
           FROM    급여
           WHERE 사원번호 = v_empno
                  and  년월 between  '199801'  and '199803' ;
           RETURN v_avg_amt;
    END AVG_MAX_AMT_FUNC;
    
    SELECT 사원번호,성명,substr(AMT,1,15), substr(AMT,16,15) 
       FROM  (SELECT 사원번호,성명,
                                       AVG_MAX_AMT_FUNC(사원번호) AMT 
                         FROM  사원 
                      WHERE  부서코드 = '1120') ;
    

문제제시

  • 사용자지정 저장형 함수의 사용이 GROUP BY절에 사용되는 경우 SELECT-LIST에도 모두 위치할 수 밖에 없음
  • 그렇다면 우리는 이러한 반복수행을 감수할 수 밖에 없는 것인가?
    SELECT 직무, 평균급여, 인원수, 최대기대급, 최대기대급/평균급여 * 100
    FROM ( SELECT 직무, AVG_AMT_FUNC(사원번호) 평균급여,
                           count(*) 인원수,
                           max(기본급) 최대기본급
                FROM 사원
                WHERE 부서코드 = '1120'
                GROUP BY 직무, AVG_AMT_FUNC(사원번호));
    

쿼리문

  • GROUP BY에 의해 내부적으로 집합이 저장되고 그 저장된 집합을 이용하여 SELECT-LIST의 요구사항을 처리하여 추출하므로 SELECT-LIST에 기술된 함수는 다시 수행되지 않음
  • 단 한번만 저장형 함수가 수행됨
  • 위의 SQL을 아래처리 직접반복해서 기술해도 됨
    SELECT 직무,
                AVG_MAX_AMT_FUNC(사원번호),
                count(*),
                max(기본급),
                max(기본급) / AVG_AMT_FUNC(사원번호) * 100
    FROM  사원 
    WHERE  부서코드 = '1120';
    GROUP BY 직무, AVG_AMT_FUNC(사원번호);
    

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.