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(사원번호);

문서에 대하여

• 최초작성자 : 임주영
• 최초작성일 : 2007년 12월 15일
• 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 이화식님의 대용량 데이터베이스 솔루션2 을 참고했습니다.*