3. 저장형함수 이용한 데이터 연결 (by kakita) [2009.05.08]
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절에 기술한 조건의 순서는 전혀 의미가 없지만
같은 체크 조건들 사이에는 그들만의 순서가 존재한다는 것이다.
이런 사실은 함수는 가능한 앞에 기술하는 것이 유리하며, 상수값과 비교되는 조건들을 나중에 기술하는 것이 유리하다는 것을 말해준다.
더불어 실패할 확률이 높은, 범위를 빨리 좁혀줄 수 있는 조건들을 나중에 기술하는 것이 바람직하다는 것을 알 수 있다.
h4.
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||'%' ;