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

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

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

문제제시

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