pl/sql-ec_area_info案例思路

--1) 分析数据, 星型模型, 构建维度分析, 区县--城市--省
select 
t1.id dis_id--区县id
,t1.shortname  dis_name --区县名
,t1.merger_name dis_merger_name --区县合并名
,t1.pinyin dis_pinyin --区县拼音
,t1.code
,t1.zip_code
,t2.id city_id --城市id
,t2.shortname city_name --城市名
,t3.id pro_id --省id
,t3.shortname pro_name --省份名
,to_char(sysdate, 'yyyymmdd') load_date
from ods.ods_ec_area_info t1
join ods.ods_ec_area_info t2 on t1.pid = t2.id
join ods.ods_ec_area_info t3 on t2.pid = t3.id
where t1.area_level = 3;

--2) 构建地区维度表, 物理表, list(宽表)
create table dim_ec_area_info_list as 
select
t1.id dis_id--区县id
,t1.shortname  dis_name --区县名
,t1.merger_name dis_merger_name --区县合并名
,t1.pinyin dis_pinyin --区县拼音
,t1.code
,t1.zip_code
,t2.id city_id --城市id
,t2.shortname city_name --城市名
,t3.id pro_id --省id
,t3.shortname pro_name --省份名
,to_char(sysdate, 'yyyymmdd') load_date
from ods.ods_ec_area_info t1
join ods.ods_ec_area_info t2 on t1.pid = t2.id
join ods.ods_ec_area_info t3 on t2.pid = t3.id
where t1.area_level = 3 and 1=2; --只创建表结构

--3) 构建存储过程
create or replace procedure sp_dim_ec_area_info_list as
       errm varchar2(1000);
begin
       --3.1 插入日志
       sp_log_ec_etl('dim_ec_area_info_list', 101, '', '');
       --3.2 清空表数据, 因为是全量刷新
       execute immediate 'truncate table dim_ec_area_info_list';
       --3.3 插入数据
       insert into dim_ec_area_info_list
       select
              t1.id dis_id--区县id
              ,t1.shortname  dis_name --区县名
              ,t1.merger_name dis_merger_name --区县合并名
              ,t1.pinyin dis_pinyin --区县拼音
              ,t1.code
              ,t1.zip_code
              ,t2.id city_id --城市id
              ,t2.shortname city_name --城市名
              ,t3.id pro_id --省id
              ,t3.shortname pro_name --省份名
              ,to_char(sysdate, 'yyyymmdd') load_date --加载日期
       from ods.ods_ec_area_info t1
       join ods.ods_ec_area_info t2 on t1.pid = t2.id
       join ods.ods_ec_area_info t3 on t2.pid = t3.id
       where t1.area_level = 3;
       commit;
       --3.4 插入数据完成, 更新日志
       sp_log_ec_etl('dim_ec_area_info_list', 101, '成功', 'dim_ec_area_info_list转换数据成功');

exception
       when others then
       sp_log_ec_etl('dim_ec_area_info_list', 101, '失败', 'dim_ec_area_info_list转换数据失败'||sqlerrm);
end;

图一案例背景: ods层有ods_ec_area_info表, 需要在dw层建立省市区的维度宽表dim_ec_area_info;

代码实现思路: 分析数据写出sql --> 创建表(表结构即可, DQL改DDL) --> 创建存储过程(往表插入数据) --> 调用日志存储过程(如下图), 写入操作记录 --> 测试

create or replace procedure sp_log_ec_etl(p_ec_tbs_name varchar2, p_num number, p_etl_success varchar2, p_etl_desc varchar2) as
       v_cnt number;
begin
       --判断日志中, 当日的插表操作记录是否存在
       select count(1) into v_cnt from LOG_EC_ETL 
       where ETL_TBS_NAME = p_ec_tbs_name and to_char(sysdate,'yyyymmdd') =LOAD_DT;
       
       --如果记录不存在, 则表示需要插入日志记录
       if v_cnt = 0 then
           insert into LOG_EC_ETL values(
           seq_log_ec_etl.nextval, p_num, p_ec_tbs_name, 'sp_'||p_ec_tbs_name,'','',sysdate, null, null, to_char(sysdate, 'yyyymmdd')
           );
       --如果存在, 则表示只需要更新日志记录
       else
           update LOG_EC_ETL
           set
               etl_success = p_etl_success
              ,etl_desc = p_etl_desc
              ,etl_end_time = sysdate
              ,etl_sec = (sysdate - etl_begin_time) * 86400
           where etl_tbs_name = p_ec_tbs_name and to_char(sysdate,'yyyymmdd') =LOAD_DT;
       end if;
       
       --提交
       commit;
end;

图二代码背景: dw层已经存在log_ec_etl日志表, 需要创建带参数的日志存储过程sp_log_ec_etl以方便其他存储过程调用;