금융 포털 계산식 oracle 예금 , 적금 , 주택(전세자금)대출 상환액 계산
/* AFTER_TAX 0.154 적용 세후 이자 */
CREATE OR REPLACE FUNCTION AFTER_TAX (TAX number, TARIFF number)
return NUMBER is
AfterTax NUMBER(5,4);
begin
AfterTax := TAX * ( 1 - TARIFF);
return AfterTax;
end;
/*
SELECT AFTER_TAX(3.00,0.154) FROM DUAL;
RESULT = 2.538
*/
/* DEP_MONEY 예금 단리 복리 */
CREATE OR REPLACE FUNCTION DEP_MONEY (MONEY NUMBER, PERIOD NUMBER, INTR_RATE number, TARIFF number, INTR_RATE_TYPE VARCHAR2)
RETURN NUMBER is
INTR NUMBER(20);
begin
if INTR_RATE > 0 then
INTR := CASE INTR_RATE_TYPE
WHEN 'M' THEN MONEY * POWER( 1+ AFTER_TAX(INTR_RATE,TARIFF)/100/12,PERIOD) - MONEY
WHEN 'S' THEN MONEY * (1+ AFTER_TAX(INTR_RATE,TARIFF)/100*PERIOD/12)-MONEY
ELSE 0
END;
end if;
return INTR;
end;
/* S 단리 M 복리
1,000,000원의 돈을 12개월(1년)동안 4%의 세전 금리로 세율(0.154) 적용후 단리 이자 계산
SELECT DEP_MONEY (1000000, 12, 4.00, 0.154, 'S') FROM DUAL;
RESULT 33,840원
*/
/* INTR_MONEY 적금 단리 복리 */
CREATE OR REPLACE FUNCTION INTR_MONEY (MONEY NUMBER, PERIOD NUMBER, INTR_RATE number, TARIFF number, INTR_RATE_TYPE VARCHAR2)
RETURN NUMBER is
INTR NUMBER(20);
begin
if INTR_RATE > 0 then
INTR := CASE INTR_RATE_TYPE
WHEN 'M' THEN MONEY * ( 1 + AFTER_TAX(INTR_RATE,TARIFF)/100/12 ) * (POWER((1+AFTER_TAX(INTR_RATE,TARIFF)/100/12),PERIOD) -1)/(AFTER_TAX(INTR_RATE,TARIFF)/100/12) - MONEY*PERIOD
WHEN 'S' THEN MONEY * ( PERIOD * ( PERIOD + 1) / 2 ) * ( AFTER_TAX(INTR_RATE,TARIFF) / 100 ) / 12
ELSE 0
END;
end if;
return INTR;
end;
/* S 단리 M 복리
매달 100,000원씩 12개월(1년)동안 3.20%의 세전 금리로 세율(0.154) 적용후 복리 이자 계산
SELECT INTR_MONEY (100000, 12, 3.20, 0.154, 'M') FROM DUAL;
RESULT 17,743원
*/
/* D1 = 원금 분할상환 , D2 = 원리금 분할 상환 , S = 만기일시 상환 */
/* FIRST_PAYMENT 첫째달 상환액 */
CREATE OR REPLACE FUNCTION FIRST_PAYMENT (LOAN_MONEY NUMBER, PERIOD NUMBER, LEND_RATE_AVG NUMBER, RPAY_TYPE VARCHAR2)
RETURN NUMBER is
INTR NUMBER(20);
begin
if LEND_RATE_AVG > 0 then
INTR := CASE RPAY_TYPE
WHEN 'D1' THEN LOAN_MONEY * (LEND_RATE_AVG / 100)/12 + LOAN_MONEY / (PERIOD * 12)
WHEN 'D2' THEN LOAN_MONEY * (LEND_RATE_AVG /100)/12 * POWER( (1+(LEND_RATE_AVG /100)/12),(PERIOD*12)) / (POWER( (1+(LEND_RATE_AVG /100/12)),PERIOD*12) -1)
WHEN 'S' THEN LOAN_MONEY * (LEND_RATE_AVG /100)/12
ELSE 0
END;
end if;
return INTR;
end;
/* LAST_PAYMENT 마지막달 상환액 */
CREATE OR REPLACE FUNCTION LAST_PAYMENT (LOAN_MONEY NUMBER, PERIOD NUMBER, LEND_RATE_AVG NUMBER, RPAY_TYPE VARCHAR2)
RETURN NUMBER is
INTR NUMBER(20);
begin
if LEND_RATE_AVG > 0 then
INTR := CASE RPAY_TYPE
WHEN 'D1' THEN LOAN_MONEY / (PERIOD * 12) * (LEND_RATE_AVG /100)/12 +LOAN_MONEY / (PERIOD * 12)
WHEN 'D2' THEN LOAN_MONEY * (LEND_RATE_AVG /100)/12 * POWER( (1+(LEND_RATE_AVG /100)/12),(PERIOD*12)) / (POWER( (1+(LEND_RATE_AVG /100/12)),PERIOD*12) -1)
WHEN 'S' THEN LOAN_MONEY + FIRST_PAYMENT(LOAN_MONEY,PERIOD,LEND_RATE_AVG, RPAY_TYPE)
ELSE 0
END;
end if;
return INTR;
end;
/* AVG_PAYMENT 평균 상환액 */
CREATE OR REPLACE FUNCTION AVG_PAYMENT (LOAN_MONEY NUMBER, PERIOD NUMBER, LEND_RATE_AVG NUMBER, RPAY_TYPE VARCHAR2)
RETURN NUMBER is
INTR NUMBER(20);
begin
if LEND_RATE_AVG > 0 then
INTR := CASE RPAY_TYPE
WHEN 'D1' THEN (FIRST_PAYMENT(LOAN_MONEY,PERIOD,LEND_RATE_AVG, RPAY_TYPE) + LAST_PAYMENT(LOAN_MONEY,PERIOD,LEND_RATE_AVG, RPAY_TYPE))/2
WHEN 'D2' THEN LOAN_MONEY * (LEND_RATE_AVG /100)/12 * POWER( (1+(LEND_RATE_AVG /100)/12),(PERIOD*12)) / (POWER( (1+(LEND_RATE_AVG /100/12)),PERIOD*12) -1)
WHEN 'S' THEN LOAN_MONEY / (PERIOD * 12) + FIRST_PAYMENT(LOAN_MONEY,PERIOD,LEND_RATE_AVG, RPAY_TYPE)
ELSE 0
END;
end if;
return INTR;
end;
/*
첫째달 상환액 = FIRST_PAYMENT
마지막 상환액 = LAST_PAYMENT
평균 상환액 = AVG_PAYMENT
1억의 돈을 빌려서 10년동안 3.00%의 금리로 각각 첫째, 마지막 , 평균 금액 계산
SELECT FIRST_PAYMENT (100000000, 10, 3.00, 'D2') FROM DUAL;
SELECT FIRST_PAYMENT (100000000, 10, 3.00, 'D1') FROM DUAL;
SELECT FIRST_PAYMENT (100000000, 10, 3.00, 'S') FROM DUAL;
SELECT LAST_PAYMENT (100000000, 10, 3.00, 'D2') FROM DUAL;
SELECT LAST_PAYMENT (100000000, 10, 3.00, 'D1') FROM DUAL;
SELECT LAST_PAYMENT (100000000, 10, 3.00, 'S') FROM DUAL;
SELECT AVG_PAYMENT (100000000, 10, 3.00, 'D2') FROM DUAL;
SELECT AVG_PAYMENT (100000000, 10, 3.00, 'D1') FROM DUAL;
SELECT AVG_PAYMENT (100000000, 10, 3.00, 'S') FROM DUAL;
*/
/* 코드 값 , 로 구분지어서 들어올 경우 코드값 설정
구분자 ,
*/
CREATE OR REPLACE function getJoinWay (joinWay VARCHAR2)
return varchar is
returnVal varchar(100);
begin
SELECT 코드명_컬럼 into returnVal FROM 코드테이블 WHERE CD_ID='FPI004' AND CD = joinWay ;
return returnVal;
end;
CREATE OR REPLACE function getJoinWayFull (joinWayFull VARCHAR2)
return varchar
is
returnVal varchar(100) :='';
BEGIN
returnVal := getJoinWay(REGEXP_SUBSTR(joinWayFull, '[^,]+', 1, 1 ));
IF LENGTH(regexp_replace(joinWayFull, '[^,]+')) > 0 THEN
FOR i IN 1.. LENGTH(regexp_replace(joinWayFull, '[^,]+')) LOOP
returnVal := returnVal ||','|| getJoinWay(REGEXP_SUBSTR(joinWayFull, '[^,]+', 1, (i+1)));
END LOOP;
END IF ;
return returnVal;
end;
초성제어
CREATE OR REPLACE FUNCTION SMCHOME.FC_GANADA
(P_NAME VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
IS
V_RET VARCHAR2(10);
BEGIN
V_RET := CASE WHEN P_NAME < 'ㄱ' THEN SUBSTR(P_NAME, 1, 1)
WHEN ASCII('ㄱ') <= ASCII(P_NAME) AND
ASCII(P_NAME)<= ASCII('ㅎ') THEN P_NAME
WHEN P_NAME < '나' THEN 'ㄱ'
WHEN P_NAME < '다' THEN 'ㄴ'
WHEN P_NAME < '라' THEN 'ㄷ'
WHEN P_NAME < '마' THEN 'ㄹ'
WHEN P_NAME < '바' THEN 'ㅁ'
WHEN P_NAME < '사' THEN 'ㅂ'
WHEN P_NAME < '아' THEN 'ㅅ'
WHEN P_NAME < '자' THEN 'ㅇ'
WHEN P_NAME < '차' THEN 'ㅈ'
WHEN P_NAME < '카' THEN 'ㅊ'
WHEN P_NAME < '타' THEN 'ㅋ'
WHEN P_NAME < '파' THEN 'ㅌ'
WHEN P_NAME < '하' THEN 'ㅍ'
ELSE 'ㅎ'
END;
RETURN V_RET;
END FC_GANADA;