数据源到ODS层:完成统一标准化,etl操作即清洗、加载、转化
ODS层到基础层:按照业务逻辑完成宽表的数据导入
宽表的定义:不按照三范式建表
基础层到主题层:根据主题划分来提取数据,大部分是通过where语句筛选符合要求的数据
主题层到应用层:通过主题层的数据得出结论,形成报表等实体化表格得出分析
基础层和主题层用过存储过程
create or replace procedure sp_f_ln_lnp
is
/*****************************************************************************
程序名称:sp_f_ln_lnp
功能描述:贷款宽表存储过程
输入参数:
输出参数:
返 回 值:
目 标 表:f_ln_loan_info
源 表:o_hx_xiangmu.o_ln_lnp_acct_amwkpl06
o_hx_xiangmu.o_ln_lnp_acct_amwkpl07
o_hx_xiangmu.o_ln_lnp_ctrt_cbapp
o_hx_xiangmu.o_ln_lnp_cust_cbcredcust
创 建 人:
创建日期:20220512
修改日期:
修改人员:
修改原因:
******************************************************************************/
/*存储过程变量定义*/
project_name varchar2(50);
start_date date;
end_date date;
sql_code varchar2(100);
sql_errm varchar2(500);
status varchar2(50);
/* 程序开始执行*/
begin
/*存储过程变量赋值*/
project_name :='sp_f_ln_lnp';
start_date :=sysdate;
--删除目标表数据
execute immediate 'truncate table jc_hx.f_ln_loan_info';
--插入数据
insert into f_ln_loan_info(
loanacno
, ecif_cust_no
, custname
--, mrg_sts
, currsign
, loankind
, loantype
, firstorder
, payperc
, fundsour
, loanuse
, assukind
, contdate
, sum_tran_amt
, sum_pay_amt
, ln_balance
, credamt
, assuamt
, impaamt
, mortamt
, credtotalamt
, credcapi
, cycflag
, tcapi
, thistcapi
, termfreq
, tterm
, retutype
, trothdueday
, fixrateterm
, firstduedate
, subsflag
, isenousubs
, firstpayamt
, firstpayperc
, releway
, payeeacno
, payeeacname
, payeebankname
, intecalckind
, intemeth
, intebase
, aheaddays
, basicinterate
, interate
, intefineratefloat
, capifineratefloat
, finerate
, capifinerate
, foulrate
, emberatefloat
, begindate
, enddate
, apprname
, apprtabno
, apprdate
, apprflag
, apprstate
, loanstate
)select
loanacno
, ecif_cust_no
, custname
--, mrg_sts
, currsign
, loankind
, loantype
, firstorder
, payperc
, fundsour
, loanuse
, assukind
, contdate
, sum_tran_amt --放款总金额 o_ln_lnp_acct_amwkpl06 SUM(TR_TRAN_AMT)
, sum_pay_amt --还款总金额 o_ln_lnp_acct_amwkpl07 SUM(PRIN_PAY_AMT+INT_PAY_AMT)
,(o_hx_xiangmu.o_ln_lnp_ctrt_cbapp.tcapi-fk.sum_tran_amt) ln_balance --贷款余额
, credamt
, assuamt
, impaamt
, mortamt
, credtotalamt
, credcapi
, cycflag
, tcapi
, thistcapi
, termfreq
, tterm
, retutype
, trothdueday
, fixrateterm
, firstduedate
, subsflag
, isenousubs
, firstpayamt
, firstpayperc
, releway
, payeeacno
, payeeacname
, payeebankname
, intecalckind
, intemeth
, intebase
, aheaddays
, basicinterate
, interate
, intefineratefloat
, capifineratefloat
, finerate
, capifinerate
, foulrate
, emberatefloat
, begindate
, enddate
, apprname
, apprtabno
, apprdate
, apprflag
, apprstate
, loanstate from o_hx_xiangmu.o_ln_lnp_ctrt_cbapp left join (select acustcredid,credamt,assuamt,impaamt,mortamt,credtotalamt from o_hx_xiangmu.o_ln_lnp_cust_cbcredcust) ed
on o_ln_lnp_ctrt_cbapp.payeeacno =ed.acustcredid left join (select sum(PRIN_PAY_AMT+INT_PAY_AMT) sum_pay_amt,rt_acct_num from o_hx_xiangmu.o_ln_lnp_acct_amwkpl07 group by rt_acct_num) hk
on o_ln_lnp_ctrt_cbapp.loanid=hk.rt_acct_num left join (select sum(TR_TRAN_AMT) sum_tran_amt,mi_pay_acct_1 from o_hx_xiangmu.O_LN_LNP_ACCT_AMWKPL06 group by mi_pay_acct_1) fk
on o_ln_lnp_ctrt_cbapp.payeeacno=fk.mi_pay_acct_1;
/*程序正常结束返回值*/
end_date :=sysdate;
status :='成功';
insert into hx_log values(project_name,start_date,end_date,'','',status);
commit;
/* 异常处理 */
exception
when others then
end_date := sysdate;
status := '失败';
sql_code := sqlcode;
sql_errm := sqlerrm;
write_log(project_name,start_date,end_date,sqlcode,sqlerrm,status);
end;