VIEW - inline view, stored view응용(대출 이자 납부액 구하기)

[목표]

-------------------------------------------------------------------------------------------------------------------------  
  통장번호  대출금  이자율  저번달이자지급일자  이번달이자지급일자  대출일수  이자금액
 ------------------------------------------------------------------------------------------------------------------------  

 

테이블 생성, 값 생성

	create table tbl_loan
    (gejanum        varchar2(10)    -- 통장번호
    ,loanmoney      number          -- 대출금
    ,interestrate   number(2,2)     -- 이자율    number(2,2) ==>  -0.99 ~ 0.99 
    ,paymentdate    varchar2(2)     -- 이자를 내는 날짜   '01' , '10' , '20', '25', 매월말일은 '00' 
    );
    
    insert into tbl_loan(gejanum, loanmoney, interestrate, paymentdate)
    values('10-1234-01', 5000, 0.03, '01');
    
    insert into tbl_loan(gejanum, loanmoney, interestrate, paymentdate)
    values('10-1234-02', 5000, 0.03, '10');
    
    insert into tbl_loan(gejanum, loanmoney, interestrate, paymentdate)
    values('10-1234-03', 5000, 0.03, '20');
    
    insert into tbl_loan(gejanum, loanmoney, interestrate, paymentdate)
    values('10-1234-04', 5000, 0.03, '25');
    
    insert into tbl_loan(gejanum, loanmoney, interestrate, paymentdate)
    values('10-1234-05', 5000, 0.03, '00'); -- 00 말일
    
    commit;

 

[풀이 1]

select T.account, t.loanmoney, t.interestrate, t.PRE_PAIDDAY, t.REAL_THIS_PAYDAY
     , REAL_THIS_PAYDAY - PRE_PAIDDAY 대출일수
     , (REAL_THIS_PAYDAY - PRE_PAIDDAY)*( t.loanmoney * t.interestrate ) 이자금액
from
(
    select account
         , loanmoney 
         , interestrate 
         , decode( to_char( pre_payday, 'd' ), '1', pre_payday + 1
                                             , '7', pre_payday + 2
                                                  , pre_payday ) AS PRE_PAIDDAY
         , decode( to_char( this_payday, 'd' ), '1', this_payday + 1
                                             , '7', this_payday + 2
                                                  , this_payday ) AS REAL_THIS_PAYDAY
    from
    (
        select account
             , loanmoney
             , interestrate
             
             , to_date( to_char( add_months(sysdate,-1), 'yyyy-mm-' ) || 
                        decode( paymentdate, '00', to_char( last_day(  add_months(sysdate,-1) ), 'dd') , paymentdate )
                      , 'yyyy-mm-dd') AS pre_payday
                      
            , to_date( to_char( sysdate, 'yyyy-mm-' ) || 
                        decode( paymentdate, '00', to_char( last_day(  sysdate ), 'dd') , paymentdate )
                      , 'yyyy-mm-dd') AS this_payday          
             
             
        from tbl_loan
    ) V
) T;

 

 

[풀이 2] - Stored View (저장된 뷰)로 만들기

create or replace view view_loan
as
select T.account, t.loanmoney, t.interestrate, t.PRE_PAIDDAY, t.REAL_THIS_PAYDAY
     , REAL_THIS_PAYDAY - PRE_PAIDDAY 대출일수
     , (REAL_THIS_PAYDAY - PRE_PAIDDAY)*( t.loanmoney * t.interestrate ) 이자금액
from
(
    select account
         , loanmoney 
         , interestrate 
         , decode( to_char( pre_payday, 'd' ), '1', pre_payday + 1
                                             , '7', pre_payday + 2
                                                  , pre_payday ) AS PRE_PAIDDAY
         , decode( to_char( this_payday, 'd' ), '1', this_payday + 1
                                             , '7', this_payday + 2
                                                  , this_payday ) AS REAL_THIS_PAYDAY
    from
    (
        select account
             , loanmoney
             , interestrate
             
             , to_date( to_char( add_months(sysdate,-1), 'yyyy-mm-' ) || 
                        decode( paymentdate, '00', to_char( last_day(  add_months(sysdate,-1) ), 'dd') , paymentdate )
                      , 'yyyy-mm-dd') AS pre_payday
                      
            , to_date( to_char( sysdate, 'yyyy-mm-' ) || 
                        decode( paymentdate, '00', to_char( last_day(  sysdate ), 'dd') , paymentdate )
                      , 'yyyy-mm-dd') AS this_payday          
             
             
        from tbl_loan
    ) V
) T;

 

 

--- *** 생성된 stored view(저장된 뷰) 조회하기 *** ---

select *
from user_views;

 

--- *** 생성된 stored view(저장된 뷰) 중 뷰 이름이 VIEW_LOAN 인 뷰소스 조회하기 *** ---

select text
from user_views
where view_name = 'VIEW_LOAN'; -- 디폴트 = 대문자, 데이터명은 대소문자 구분함

/*
"select T.gejanum, t.loanmoney, t.interestrate, t.PRE_PAIDDAY, t.REAL_THIS_PAYDAY
     , REAL_THIS_PAYDAY - PRE_PAIDDAY 대출일수
     , (REAL_THIS_PAYDAY - PRE_PAIDDAY)*( t.loanmoney * t.interestrate ) 이자금액
from
(
    select gejanum
         , loanmoney 
         , interestrate 
         , decode( to_char( pre_payday, 'd' ), '1', pre_payday + 1
                                             , '7', pre_payday + 2
                                                  , pre_payday ) AS PRE_PAIDDAY
         , decode( to_char( this_payday, 'd' ), '1', this_payday + 1
                                             , '7', this_payday + 2
                                                  , this_payday ) AS REAL_THIS_PAYDAY
    from
    (
        select gejanum
             , loanmoney
             , interestrate
             
             , to_date( to_char( add_months(sysdate,-1), 'yyyy-mm-' ) || 
                        decode( paymentdate, '00', to_char( last_day(  add_months(sysdate,-1) ), 'dd') , paymentdate )
                      , 'yyyy-mm-dd') AS pre_payday
                      
            , to_date( to_char( sysdate, 'yyyy-mm-' ) || 
                        decode( paymentdate, '00', to_char( last_day(  sysdate ), 'dd') , paymentdate )
                      , 'yyyy-mm-dd') AS this_payday          
             
             
        from tbl_loan
    ) V
) T"
*/

 

 

--- *** 생성된 stored view(저장된 뷰) 삭제하기 *** ---

drop view view_loan;
-- View VIEW_LOAN이(가) 삭제되었습니다.