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

3.1 개념 및 특징

3.1.1 절차형 처리

{section}{column:width=33%}

 
      ① 데이터베이스가 제공하는 절차형 SQL을 이용해 생성한다.
     ② 하나 이상의 SQL이 존재할 수 있다.
     ③ 다양한 연산이나 조건처리 가능하다.
     ④ 외부에서 받은 값이나 고정된 상수값을 이용하여 필요한 처리를 하여 단하나의 결과값만 리턴한다.

     - 제공받은 값을 이용해 가공 후 리턴할 값을 만들 때까지만 의미.
     - 단순히 단 하나의 값을 만드는 처리
      - 사용자가 마음대로 작성할 수 있다는 것 외에는 데이터베이스가 제공한 SUM, SUBSTR 등과 같은 함수
      - 사용자가 자신의 의지대로 필요한 처리를 할 수 있다는 장점

{column}{section}

3.1.2 독립적인 오브젝트

{section}{column:width=33%}

 

    - 테이블이나 뷰와 같이 독자적인 기능을 가지는 독립적인 오브젝트이다.
    - 다른 오브젝트의 도움 없이도 결과를 낼수 있다.
    - 하나의 공통 모듈로서 여러 곳에서 공유 할수 있다.
    - 저장형 함수는 매우 가벼워야 한다.

{column}{section}

3.1.3 단일값을 리턴

{section}{column:width=33%}

 
     - 단한개의 컬럼만 리턴
      - 추출한 컬럼값들을 결합하여 하나의 상수값으로 만들어 리턴
         -> 이것을 SQL에서 SUBSTR으로 다시 문할하여 최종 출력 

{column}{section}

3.1.4 로우단위별 실행

• 자신이 속한 집합의 로우 단위별로 실행

조인과의 비교

{section}{column:width=33%}

 

    - 특정한 경우 조인에 비해 매우 효과적일 수 있다.
    - 저장형 함수가 조인과 다른점은 절차형 처리를 할 수 있다는 것과 단일값을 리턴한다는 특성.
    - 어떤 집합의 로우들이 그 값에 따라 서로 다른 집합과 연결하는 경우도 쉽게 처리할 수 있다.

{column}{section}

유형별 활용

3.3.1 1:M 조인을 1:1 조인

• 복잡한 query 를 저장항 함수 이용함.
• 1개의 값을 리턴한다.

3.3.2 M:M 조인의 해결

{section}{column:width=33%}

 

    -  M:M 관계를 억지로 조인으로 풀려고 해서는 안된다.
    -  앞에서본 UNION, GROUP BY 이용 할수도 있다.
    -  서브쿼리를 활용할수 있다.
    -  M:M조인을 쓰는 이유는 모든집합의 처리를 완료한 후에 출력하는 것이 아니라 소량의 집합만 전체를 처리하고 다량의 집합은 일부씩 처리되므로 온라인 화면 처리에서느 효과를 불수 있다.

{column}{section}

3.3.3 부분범위처리로의 유도

{section}{column:width=33%}

 
 
   ① 자신과 1:M 관계를 갖는 집합에서 조건으로 체크하고자 하거나 조건 체크와 그 결과값도 같이 출력하고자 할 때 사용하는 방법.
   ② EXISTS로 인해 전체범위처리로 실행되는 필터 처리를 부분범위처리 방식으로 바꾸는 경우에 사용하는 방법.
   ③ 부분범위처리로 유도할 수 없는 상태일 때 그 원인 부분만 전체범위처리하고 나머지 처리를 부분범위처리로 바꾸는 방법.

{column}{section}

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

{section}{column:width=33%}

 

 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 

{column}{section}

{section}{column:width=33%}

 

 50만 고객중에 2000명만 선택하여 추출하는것이다. 
조인을 하면 GROUP BY 를 하기 전에 먼저 모든 처리범위를 처리하는 '전체범위처리' 방식으로 수행 되므로 2000명을 골라내기전에
50만명에 대한 전체범위를 처리된다는게 문제이다.

해결방법 : EXISTS를 이용하여 해결

{column}{section}

{section}{column:width=33%}

 
 SELECT 고객번호, 고객명, 연락처.......
  FROM 고객 x
  WHERE 고객상태 = '연체'
    AND  EXISTS ( SELECT ' ' 
                    FROM 청구 y
                   WHERE y.고객번호 = x.고객번호
                        AND y.납입구분 = 'N'
                   GROUP BY y.고객번호
                      HAVINGsum(y.미납금)  between   :VAL1  and  :VAL2 )
  and ROWNUM <=2000

{column}{section}

{section}{column:width=33%}

 

 전체범위처리 방식으로 수행 했던 원인 group by가 체크하는걸 서브쿼리로 넣는다.
그러므로 고객 테이블은 부분범위 처리를 하게된다.

단점이 있다.
     서브쿼리의 수행결과를 메인쿼리에서 추출 할수 없다.  
     미납액 합계 추출 안된다.

{column}{section}

{section}{column:width=33%}

 

  EXISTS를 사용했을때 '미납액' 을 추출할수 있으며 부분범위 처리 수행된다.

  위에 EXISTS의 부분을 FUNCTION 를 이용했다.

{column}{section}

나. 전체범위처리로 수행되는 필터 처리 해결

{section}{column:width=33%}

 

 필터처리는 NESTED LOOPS 조인과 유사한 방법으로 수행되거나 경우에따라서 SORT MERGE 조인과 유사한 방법으로 수행된다.

{column}{section}

다. 득정 부분만 부분범위 처리 유도

3.3.4 베타적 논리합(Exclusive OR) 관계의 조인

{section}{column:width=33%}

 

   -  어떤 엔터티의 틀징 관계가 두개 이상 엔터티의 합집학과 절대적 관계를 가지는것 이다.
   -  참조 엔터티가 그 중 하나의 참조되는 엔터티와 반드시 관계를 가져야 한다.

{column}{section}

문서에 대하여

• 최초작성자 : 4조 김도희
• 최초작성일 : 2007년 11월 27일
• 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• 이 문서의 내용은 대용량 데이터베이스 솔루션2 을 참고했습니다.