 구해보기 (function)){kind=link}
함수의 사용에 대해 알아봅시다. 예제 자료는 '소비자 가격'에 대해 공급가액(부가가치세 10% 를 제외한 금액)을 구하기이며, 함수로 값을 계산해보겠습니다. 예시 테이블은 직원정보, EMP 테이블로 진행하도록 하겠습니다. 편의상 SAL 컬럼을 '소비자 가격'이라고 가정하겠습니다~
부가가치세에 대한 간단한 정보는 다음과 같습니다.
- 소비자가격은 부가가치세와 공급가액으로 이루어집니다.
- 부가가치세는 공급가액의 10% 입니다.
이것을 전제로 코딩을 하여 프로그램 이곳저곳에 적용을 하였을 때에는 다음과 같은 쿼리를 작성할 수 있을 것입니다.
SELECT SAL 소비자가격,
SAL - TRUNC (SAL / 1.1) 부가가치세,
TRUNC (SAL / 1.1) 공급가액
FROM EMP;
소비자가격 부가가치세 공급가액
---------- ---------- ----------
2850 260 2590
2450 223 2227
2975 271 2704
3000 273 2727
3000 273 2727
800 73 727
1600 146 1454
1250 114 1136
1250 114 1136
1500 137 1363
1100 100 1000
950 87 863
1300 119 1181
5000 455 4545
14 rows selected.그런데 만약 부가가치세율이 10% 에서 다른 요율로 변경이 된다면 어떻게 될까요? 그에 따라 이전에 작업하였던 부분을 모두 찾아서 요율을 수정하는 불상사가 생길 것입니다. 그리하여 유연하게 요율을 계산할 수 있도록 함수를 이용하는 방법이 있습니다. 함수 사용 방법을 살펴보겠습니다. 물론 쿼리 자체를 하나의 메서드에 통합했다면 큰 문제는 없겠지만, 이 상황은 제외할게요...!
공급가액을 구하는 함수를 하나 만들어 보았습니다. 고시일자(startDat), 소비자가격(price)를 매개로 하여 숫자형태의 반환값을 취합니다. 지역변수는 부가가치세율(vatRat), 공급가액(valueOfSupply) 입니다. 예시자료는 2030년 01월 01일부터 요율을 0.05%로 변경되는 것으로 가정하였습니다.
CREATE OR REPLACE FUNCTION FN_VALUEOFSUPPLY (
P_V_STARTDAT VARCHAR2 DEFAULT TO_CHAR (SYSDATE, 'YYYYMMDD'),
P_N_PRICE NUMBER)
RETURN NUMBER
IS
L_N_VATRAT NUMBER := 0;
L_N_VALUEOFSUPPLY NUMBER := 0;
BEGIN
--2030년 1월 1일부터 부가가치세가 5% 라고 가정합시다(예시)
IF P_V_STARTDAT >= '20300101'
THEN
L_N_VATRAT := 0.05;
ELSE
L_N_VATRAT := 0.10;
END IF;
L_N_VALUEOFSUPPLY := TRUNC (P_N_PRICE / (1 + L_N_VATRAT));
RETURN (L_N_VALUEOFSUPPLY);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('OTHERS');
RETURN (-1);
END FN_VALUEOFSUPPLY;
/
함수를 통한 공급가액 조회를 살펴보겠습니다.
SELECT SAL 소비자가격,
SAL - TRUNC (SAL / 1.1) 부가가치세,
TRUNC (SAL / 1.1) 공급가액,
FN_VALUEOFSUPPLY ('20201207', SAL) 함수_공급가액_20201207,
FN_VALUEOFSUPPLY ('20300101', SAL) 함수_공급가액_20300101 --2030년 1월 1일부터 부가가치세가 5% 라고 가정합시다(예시)
FROM EMP;
소비자가격 부가가치세 공급가액 함수_공급가액_20201207 함수_공급가액_20300101
---------- ---------- ---------- ---------------------- ----------------------
2850 260 2590 2590 2714
2450 223 2227 2227 2333
2975 271 2704 2704 2833
3000 273 2727 2727 2857
3000 273 2727 2727 2857
800 73 727 727 761
1600 146 1454 1454 1523
1250 114 1136 1136 1190
1250 114 1136 1136 1190
1500 137 1363 1363 1428
1100 100 1000 1000 1047
950 87 863 863 904
1300 119 1181 1181 1238
5000 455 4545 4545 4761
14 rows selected.