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

3. 저장형함수 이용한 데이터 연결




2.3. 저장형 함수를 이용한 데이터연결

3.1 특징

      3.1.1.... 절차형 처리

            - SQL내에 절차형 처리를 삽입할 수 있다는 가장 중요한 특성이다. 즉, DBMS에 더 많은 역할을 넘길 수 있다는 것을 의미한다. 그러나 시스템에 부하를 줄 수 있으므로 사용에 주의하도록 한다.

              SELECT FUNC_NAME(:date_value) from TABLE_NAME

      3.1.2.... 독립적인 오브젝트

            - 별도의 독립적인 오브젝트이며 공유가 가능하다

            - CREATE or REPLACE FUNCTION AVG_SAL_F (v_empno    varchar2)

              RETURN   varchar2 

              IS  V_avg_amt varchar2(30);

              BEGIN

                       SELECT avg(급여총액) into v_avg_amt

                          FROM  급여

                       WHERE 사번 = v_empno

                            AND 년월 between  '199801' and '199803' ;

              RETURN  v_avg_amt;

              END AVG_SAL_F ;

      3.1.3.... 단일값을 리턴

            - 단 하나의 값만을 return한다

            - 여러값을 리턴하기 위해서는 컬럼들을 묶어서 리턴하는 방법을 사용하여 리턴후 다시substring한다.

              SELECT item,

                           ITEM_AMT_FUNC(item,sysdate)     당일매출,

                           ITEM_AMT_FUNC(item,sysdate-1)  전일매출,

                           (ITEM_AMT_FUNC(item,sysdate) -  ITEM_AMT_FUNC(item,sysdate-1)) * 100 /

                           ITEM_AMT_FUNC(item,sysdate)      증감율

              FROM  재고자산

           WHERE  분류코드 = '110' ;

           - 인라인뷰를 사용해도 FUNCTION은 반복 수행한다

             SELECT item, 당일매출, 전일매출,  (당일매출-전일매출)*100 / 당일매출 증감율

                FROM ( SELECT item,

                                          ITEM_AMT_FUNC(item, sysdate)    당일매출,

                                          ITEM_AMT_FUNC(item, sysdate-1) 전일매출

                               FROM  재고자산

                            WHERE  분류코드 = '110' ) ;

           - GROUP BY를 사용하면 수행결과가 내부적으로 저장되었다가 제공되므로 한번씩만 수행된다

             SELECT item, 당일매출, 전일매출, (당일매출-전일매출)*100 / 당일매출 증감율

                FROM ( SELECT item, ITEM_AMT_FUNC(item, sysdate) 당일매출,

                                                  ITEM_AMT_FUNC(item, sysdate-1) 전일매출

                               FROM 재고자산

                             WHERE 분류코드 = '110'

                             GROUP BY ITEM ) ;

      3.1.4.... 로우단위별로 실행

            - 해당 집합의 row단위마다 수행된다

            - 단일 테이블 select인 경우, 조인한 경우, 인라인뷰내에 사용된 경우, GROUP BY절에 사용된 경우 등에 따라서 다르다.

              SELECT 사번,

                            FUNC_6(사번, col3)   -- (1)

                 FROM ( SELECT x.사번,

                                           FUNC_1(x.부서)      col1,  -- (2)

                                           Max(FUNC_2(y.col2)) col2, -- (3)

                                           SUM(y.본봉)           col3

                                FROM  사원 x, 급여 y

                             WHERE  x.사번 = y.사번

                                   AND x.부서 = '1100'

                                   AND y.년월 between '199801'and '199806' 

                                   AND FUNC_3(x.사번) > 1000  -- (4)

                                   AND FUNC_4(y.항목) = 'ABC' -- (5)

                                   AND y.COL4 = FUNC_5(sysdate)   -- (6)

                                   AND y.COL5 > 100  

                              GROUP BY x.사번, FUNC_1(x.부서) ) ;   -- (7)

                   (1)    Group by 결과 로우마다 수행

                   (2)    수행하지 않는다. SELECT LIST 추출 작업은 마지막에 수행되는데, 이 항목은 GROUP BY에 포함된 항목이어서 이미 내부적으로 저장된 로우값이기 때문

                   (3)    Where 절에서 성공한 모든 로우마다 수행. 즉, group by 의 함수가 수행되는 수 만큼 수행

                   (4)    조인방향과 드라이빙 여부에 따라 1번, 혹은 사원 테이블에서 성공한 횟수 혹은 급여 테이블 액세스 로우마다  수행,  또는 부서조건에서 체크된 결과만큼수행

                   (5)    급여 테이블을 액세스한 로우마다 수행, 인덱스에 따라서 인덱스를 경유한 수만큼만 수행. 드라이빙과 조인방식에 따라서 수행내용이 다름.

                   (6)    y.COL4가 체크조건으로 사용된다면 테이블을 엑세스한 수만큼 수행. 그러나 이 컬럼이 드라이빙 인덱스로 사용된다면 이 함수는 먼저 한번만 수행되어서 y.COL4에게 상수값을 제공하게 됨.

                   (7)    Where 절에서 성공한 모든 결과 로우에 대해서 수행

                   * 조건의 체크기능으로 사용된 함수는 조건이 기술된 위치에 따라서 수행 횟수가 달라진다. 즉, AND y.COL5 > 100  를 (4)나 (8)의 앞으로 이동시키면 함수의 수행횟수가 달라진다.

                     이런 사실은 매우 중요한 의미를 가지게 되는데,  논리적인 측면이나 드라이빙 결정에 대해서는 WHERE절에 기술한 조건의 순서는 전혀 의미가 없지만

                     같은 체크 조건들 사이에는 그들만의 순서가 존재한다는 것이다.

                     이런 사실은 함수는 가능한 앞에 기술하는 것이 유리하며, 상수값과 비교되는 조건들을 나중에 기술하는 것이 유리하다는 것을 말해준다.

                     더불어 실패할 확률이 높은,  범위를 빨리 좁혀줄 수 있는 조건들을 나중에 기술하는 것이 바람직하다는 것을 알 수 있다.

3.2 활용

      3.2.1.... 1:M 조인을 1:1 조인으로

               SELECT x.사번, x.성명, x.직급, x.직책, ...,

                            AVG(y.급여총액) 평균급여

                  FROM  사원 x,  급여 y

               WHERE x.사번 = y.사번

                    AND  x.부서  = '1110'

                    AND  y.급여년월 between '199801'

                    AND  '199807'

               GROUP BY x.사번, x.성명, x.직급, x.직책, ...

               위와 같은 쿼리가 있다면 이를 아래처럼 함수를 이용하여 처리가 가능하다

               CREATE or REPLACE FUNCTION AVG_SAL_F (v_empno    varchar2)

               RETURN   varchar2 

               IS  V_avg_amt varchar2(30);

               BEGIN

                        SELECT avg(급여총액) into v_avg_amt

                           FROM 급여

                        WHERE 사번 = v_empno

                             AND 년월 between  '199801' and '199803' ;

               RETURN  v_avg_amt;

               END AVG_SAL_F ;

                    

               SELECT 사번, 성명, 직급, 직책, .........,

                            AVG_SAL_F(empno) 평균급여

                  FROM  사원

               WHERE  부서 = '1100' ;

      3.2.2.... M:M 조인을 해결

            - M:M으로 엮이게 만드는 부분을 function으로 빼내고 main SQL을 단순화하여 해결한다

      3.2.3.... 부분범위처리로의 유도

            3.2.3.1    M집합 체크시의 부분범위 처리

                   - 미납고객 50만명중에 미납금액이 부여한 조건 내에 있는 처리대상 2000명을 추출하는 예

   

                     SELECT 고객번호, 고객명, 연락처, .............

                        FROM ( SELECT x.고객번호,

                                                  max(x.고객명) 고객명,

                                                  max(x.연락처) 연락처,

                                                  .......

                                       FROM   고객 x,  청구 y.           

                                    WHERE  x.고객번호 = y.고객번호

                                          AND  x.고객상태 = '연체'

                                          AND  y.납입구분 = 'N' 

                                     GROUP  BY  x.고객번호

                                    HAVING  sum(y.미납금)  between  :VAL1 and :VAL2)

                      WHERE  ROWNUM <= 2000 ;

             

                     위의 경우 전체범위를 처리하는 예인데 이를 부분범위로 하도록 변경해본다.

                     SELECT 고객번호, 고객명, 연락처, .............

                       FROM   고객 x

                    WHERE 고객상태 = '연체'

                          AND EXISTS ( SELECT  ' '

                                                  FROM  청구 y     

                                               WHERE  y.고객번호 = x.고객번호

                                                     AND  y.납입구분 = 'N' 

                                                GROUP  BY  y.고객번호

                                                HAVING sum(y.미납금)  between  :VAL1 and :VAL2)   

                          AND ROWNUM <= 2000 ;

                    그러나 위의 경우 부분범위 처리는 되었으나 미납금액은 추출할 수가 없다 이를 다시 변경해본다.

                    CREATE or REPLACE FUNCTION  CUST_UNPAY_FUNC  (v_costno   in varchar2)

                    RETURN  number

                    IS  RET_VAL number(14);

                    BEGIN

                             SELECT sum(UNPAY) into RET_VAL

                               FROM 청구                

                            WHERE  고객번호 = v_custno

                                  AND  납입구분 = 'N' 

                             GROUP BY  고객번호 ;

                    RETURN RET_VAL ;

                    END CUST_UNPAY_FUNC ;

             

                   위와 같은 함수를 만들고 아래처럼 쿼리를 실행한다

                   SELECT 고객번호, 고객명, 연락처,

                                CUST_UNPAY_FUNC(고객번호),

                                 .............

                      FROM   고객

                   WHERE 고객상태 = '연체'

                         AND CUST_UNPAY_FUNC(고객번호)  between :VAL1 and :VAL2   and ROWNUM <= 2000 ;

                  위와 같은 형식으로 부분범위처리로 수행하도록 할 수 있다

            3.2.3.2    전체범위로 수행되는 필터처리를 해소

                  EXISTS로 인해 전체 범위처리로 실행되는 필터처리를 부분범위로 해소

                 SELECT .............

                    FROM   TAB1 x

                 WHERE COL1 = '111'

                      AND EXISTS ( SELECT ' '

                                              FROM  TAB2 y  

                                            WHERE  y.KEY = x.KEY

                                                   AND ........................  ) ;

                  위의 내용을 아래처럼 함수를 이용하여 처리한다

                  CREATE or REPLACE FUNCTION   EXISTS_CHECK_FUNC   (v_tab_key in varchar2)

                  RETURN  char

                  IS  RET_VAL char(1);

                  BEGIN

                           SELECT 1 into RET_VAL

                              FROM TAB2              

                           WHERE KEY = v_tab1_key

                                AND  ...........................

                                AND  ROWNUM = 1 ;

                 RETURN RET_VAL ;

                 END EXISTS_CHECK_FUNC ;      

       
                 위의 함수를 생성하고 아래처럼 실행한다

                 SELECT .............

                    FROM   TAB1

                 WHERE COL1 = '111'

                      AND  EXISTS_CHECK_FUNC(:v_tab1_key) = 1 ;

           3.2.3.3    전체범위로 수행되는 필터처리를 해소

3.2.4.... 배타적논리합 관계의 조인

            - 배타적 논리합 관계란 어떤 엔티티의 특정 관계가 두개 이상 엔티티의 합집합과 절대적(Mandatory)관계를 가지는 것을 말한다. 

             

            - 배타적 관계에 있는 테이블들만 따로 if로 처리하는 함수를 만들어서 사용이 가능하다.

              CREATE or REPLACE FUNCTION GET_NAME_SEL  (v_idno  in varchar2, v_type  in varchar2)

              RETURN   varchar2

              IS  RET_VAL varchar2(14);

              BEGIN

                       IF v_type = '1'

                       THEN  SELECT  성명 into  RET_VAL

                                     FROM  개인

                                  WHERE  ID = v_idno ;

                       ELSE  SELECT  법인명 into RET_VAL

                                    FROM   법인

                                 WHERE   ID = v_idno ;

             RETURN RET_VAL ;

             END GET_NAME_SEL ;

            위의 함수를 이용하여 아래와 같이 쿼리를 실행

            SELECT 계좌번호, 개설일자,

                          GET_NAME_SEL(id, 구분),  ......

               FROM  계좌

            WHERE  개설일자 LIKE :in_date||'%'  ;

문서정보

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